This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
If you make a varchar(8000) or smaller it is stored in the table dataspace. It will thus be compressed with datacompression. A varchar(MAX) is stored outside the datapage. So getting this value takes a little bit longer.
In short: varchar(MAX) is, without additional settings, sored in the same page as the record if it fits. If it doesn't fit the content will move ans only a reference remains. This will cost performance.
By the way: get rid of text/ntext. They will not be available soon and are already giving problems in T-SQL.