Wednesday, October 8, 2008

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

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

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.

1 comment:

  1. i would like to ask , can i specify a varchar with size greater than 8000 bytes?