x

How big a varchar before going MAX

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)?

more ▼

asked Oct 15, 2009 at 12:35 AM in Default

craigvn gravatar image

craigvn
23 1 2 2

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

It should be 8000

more ▼

answered Oct 15, 2009 at 04:06 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 17, 2009 at 10:03 PM

Mike Walsh gravatar image

Mike Walsh
11

(comments are locked)
10|1200 characters needed characters left

or 4000 for unicode

more ▼

answered Oct 15, 2009 at 04:43 AM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 15, 2009 at 10:57 PM

Bart Czernicki gravatar image

Bart Czernicki
11

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 16, 2009 at 04:56 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

asked: Oct 15, 2009 at 12:35 AM

Seen: 2699 times

Last Updated: Oct 15, 2009 at 12:35 AM