sql server 2005 annot exceed 8000 bytes in size.variable binary column can store 8000 bytes assuming in a table.And were as var char can store 2 bytes per Unicode character. The actual storage size of the var char(max) is length of data entered + 2.VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.
Not quite right. First (if we're going to deal with exact byte counts), the VARCHAR(MAX) datatype holds 2^31-1 bytes. A couple of entires into a scientific calculator says the actual answer is 2,147,483,647. You might have just phat phingered that last digit.
I'm also not sure what you mean by "sql server 2005 annot [sic] exceed 8000 bytes in size" because 2005 was the first version to come out with the MAX datatype capabilities. That includes VARCHAR, NVARCHAR, and VARBINARY. CHAR, NCHAR, and BINARY can only hold 8000 bytes each with the understanding that that will only be 4000 characters in NCHAR.
There's something else (and I may have missed it above)... The use of any "blob" datatype (which includes any datatype defined as MAX, XML, IMAGE, ect) will prevent online rebuilds of indexes if the "blob" column(s) are contained within the index in 2005 and 2008. That means that you won't be able to rebuild the Clustered Index of a table if the table contains a "blob" column. Of course, any Non-Clustered index with an "include" on a "blob" column will suffer the same fate. IIRC, they've fixed this "little" problem in 2012.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs