I have a field that is currently a varchar(250). I need to make this bigger, probably something like varchar(1000). How big do you normally allow a field to be before going for varchar(MAX)?
asked Oct 15, 2009 at 12:35 AM in Default
It should be 8000
answered Oct 15, 2009 at 04:06 AM
Since I don't have enough reputation to add a note to Bart's I'll add that he is partially right.
In SQL Server 2005/2008 you could have multiple VARCHAR/NVARCHAR columns that each exceed 8000 characters. In SQL 2000 this was limited and you would have a problem if the row size exceeded 8,060 bytes. In 2005, Row Overflow data was introduced and if you go over with certain datatypes (varchar,nvarchar,varbinary) that data would be stored in a row overflow page.
So you -could- have multiple varchar columns of 8000 in a table in SQL 2005/2008. Also the other comments are correct, you can only define a varchar of up to 8000.
Varchar(max) is great because you can still work with larger data (up to 2GB, I believe) and query it without having to use the special query syntax that came out for Text data. Also it is supported moving forward while Text is not.
That being said, I wouldn't instantly go to the VARCHAR(Max) if you don't foresee the need to use it. So in your case I would stick with a defined VARCHAR amount since it doesn't sound like you will get anywhere near the 8000.
answered Oct 17, 2009 at 10:03 PM
or 4000 for unicode
answered Oct 15, 2009 at 04:43 AM
If it's big text, and you're not sure how big, I'd just set it to varchar(max). From Books Online (http://msdn.microsoft.com/en-us/library/ms175055.aspx), it doesn't appear that there is a reason not to use it. If you have text-in-row, then varchar(2) and varchar(max) should be the same.
For good design, I'd set a number for smaller items, but for free form text/comments, it seems like (max) is a good choice.
answered Oct 16, 2009 at 04:56 PM
Steve Jones - Editor ♦♦