Should I use varchar(8000) or varchar(max)

Question

Should I use varchar(8000) or varchar(max)

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

this is what varchar(max) was designed for.

varchar(max) will be stored out of row (if i'm correct on that) therefore not counted towards your 8060 bytes and operates effectively the same as a standard varchar, but with the benefits (and none of the disadvantages) of text/ntext

use varchar(max)

MVDBA

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

Have a look here:

http://sqlblog.com/blogs/kalen_delaney/archive/2007/10/09/geek-city-detecting-overflowing-columns.aspx

for some discussion on the storage/usage of overflow columns (which is what you're talking about).

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 3

Definitely go for the varchar(max). The only down sides are disk space, but that's easily monitored.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

71 camaro SSC Eights! Points: 911 More actions · Answer 4

Thanks all for the reply.

So we were just discussiing where does the out of row data get stored.

Is it memory or disk ?? It sounds like disk.

I am approaching this as everything is done under the covers so things like select , updates and even dbase restores do not change because of the data type.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

All data is stored on disk, and switching to varchar(max) will not affect any select or update or backup/restore statements.

If you're really interested enough to read about how it is implemented you'll find that SQL does some creative page shuffling to make it work, but it is completely transparent to you.