varchar(max) vs varchar(2000) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

varchar(max) vs varchar(2000)

We are currently redesigning our database and the topic of using varchar(max) has popped up. In my reading to me varchar(max) is like a lob, clob, etc. We are discussing whether it is better to use varchar(2000) for a comment field or to use varchar(max) for the comment field. For the developer they are arguing that we should use varchar(max). However, as the DBA I am concern of using varchar(max) in regards in how it will affect maintenance, backups, restores, performance, replication. Does anyone have an opinion on what is best? Has anyone started using varchar(max) and have had issues with it?
If the BLOB or CLOB is less than 256 KB , it can be stored as text value or large object. But if it in over than it becomes diffcult task. This theory behind it but I would wait for Gurus who have handled such data to respond
Cheers
Sat
My suggestion is, always use a fixed size when 1. Its not more than 8000
2. You don’t see a need to readjust the size in the near future. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
I don’t see any reason to use Varchar(max) when you don’t exceed the limit of 2000… Check the following… http://sqljunkies.com/WebLog/simons/archive/2006/02/28/Why_use_anything_but_varchar_max.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
See our FAQhttp://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>