varchar(max) and varbinary(max) Questions and Answers

Download PDF

The following applies to SQL Server 2008.

How big of an allocation does max represent in the case of varchar(max) and varbinary(max)?

Up to 2 ^ 31 – 1 bytes or just about 2gb.

 

Can I specify a varchar(9000) or varbinary(12000)?

No. If you are going to specify an actual number instead of (max) then your limit is 8000. The size can be 1 to 8000, or max, there is nothing between 8000 and max.

 

Is a varchar(max) or varbinary(max) column stored in the data row?

If the size of the varchar or varbinary item is less than 8000 then it is stored in the data row.
If it is greater than 8000 then the item is moved out of the data row into a special storage location for large objects.

 

Should varchar(max) be used to replace the TEXT column type?

Yes. varchar(max) can handle larger strings than the text type can. varchar(max) is easier to update than text with standard SQL statements.

Posted in TSQL Tagged with: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.