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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!