difference between varchar(500) vs varchar(max) in sql server -

i want know pros , cons while using varchar(500) vs varchar(max) in terms of performance, memory , else consider?

• will both use same amount of storage space?

is answer differ in case of sql server 2000/2005/2008?

in sql server 2000 , sql server 7, row cannot exceed 8000 bytes in size. means varbinary column can store 8000 bytes (assuming column in table), varchar column can store 8000 characters , nvarchar column can store 4000 characters (2 bytes per unicode character). limitation stems 8 kb internal page size sql server uses save data disk.

to store more data in single column, needed use text, ntext, or image data types (blobs) stored in collection of 8 kb data pages separate data pages store other data in same table. these data pages arranged in b-tree structure. blobs hard work , manipulate. cannot used variables in procedure or function , cannot used inside string functions such replace, charindex or substring. in cases, have use readtext, writetext, , updatetext commands manipulate blobs.

to solve problem, microsoft introduced varchar(max), nvarchar(max), , varbinary(max) data types in sql server 2005. these data types can hold same amount of data blobs can hold (2 gb) , stored in same type of data pages used other data types. when data in max data type exceeds 8 kb, over-flow page used. sql server 2005 automatically assigns over-flow indicator page , knows how manipulate data rows same way manipulates other data types. can declare variables of max data types inside stored procedure or function , pass them variables. can use them inside string functions.

microsoft recommend using max data types instead of blobs in sql server 2005. in fact, blobs being deprecated in future releases of sql server.

credit: http://www.teratrax.com/articles/varchar_max.html

---

in sql server 2005 , sql server 2008, maximum storage size varchar(max) 2^31-1 bytes (2,147,483,647 bytes or 2gb - 1 bytes). storage size actual length of data entered + 2 bytes. data entered can 0 characters in length. since each character in varchar data type uses 1 byte, maximum length varchar(max) data type 2,147,483,645.

full interesting read you: http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx

reference: http://msdn.microsoft.com/en-us/library/ms143432.aspx