varchar(max)

[rcloutie]

Hi there,

Users always want more...
I have a column of type varchar(1000) and someone asked me to expand this limit. I can put it to 2000 but I know for sure that, one of these days, another person will ask me to expand it again...

Considering that actually there's no search clause in all my SQL queries, is there any pain (or performance issue) if I change data type to varchar(max)?

Thanks a lot for information.

Rej Cloutier

 

[SQLDenis]

>>Considering that actually there's no search clause in all my SQL queries, is there any pain (or performance issue) if I change data type to varchar(max)?

be aware that varchar max is a overflow datatype, it has the benefits of the text datatype without the negatives

What this means is that if the data doesn't fit in the row it will be stored elsewhere and a pointer will be created on the row which will point to the data

Also be aware of this gotcha

http://blogs.conchango.com/stevewright/archive/2006/01/20/2686.aspx

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions