How big a varchar before going MAX

It should be 8000

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.

or 4000 for unicode

That actually depends on the other columns in your table. Remember if you set the max column to varchar(8000) you don't leave yourself much room for other columns. This is why the varchar max datatype exists to eliminate this problem.

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.