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

Question

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

71 camaro

SSC Eights!

Points: 911
More actions
October 11, 2007 at 9:13 am

#182533

Any input on the below would be helpful:
The maximum row length for a table in SQL Server 2005 is 8060. If I needed to create a column as varchar(8000), along with other columns on a table, which would be the better approach? Creating a separate table with the varchar(8000) column, or creating the column defined with the datatype varchar(max), and keeping all of the columns in a single table?
Are there issues in inserting or selecting data when a column is defined as varchar(max). Are there any performance considerations when having a varchar(max) column?

Viewing 6 posts - 1 through 5 (of 5 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: 398909 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.