Use varchar(max) instead of text in SQL Server

databases

As you may know on SQL Server a varchar column can hold up to 8000 characters (each row can hold up to 8K, so it depends on the size of other columns), when you need to store more it is common to use the text or ntext datatypes. The problem with the text datatype however is that you can't call most string functions on the column, they also require more IO due to how they are stored internally.

In SQL Server 2005 Microsoft added support for varchar(max) and nvarchar(max), this new datatype can be used anywhere a regular length limited varchar can, but lets you store up to 2GB of data. Behind the scenes the varchar(max) stores up to the as much of the text as it can in the row (up to 8K), and then creates additional pages for any additional text. So in a lot of cases the text content will fit in the row, requiring much less disk IO.

Microsoft is said to be deprecating the text and ntext in future releases.

I also found an interesting blog entry which finds that when you alter a column from ntext/text to nvarchar(max)/varchar(max) the text content will still be stored in the external page, you should run UPDATE tableName SET columnName=columnName which will cause SQL server to store text more efficiently.

Thanks Jason Q, for the tip on this!



Related Entries

6 people found this page useful, what do you think?

Trackbacks

Trackback Address: 734/C363BDFAAF4EF795792C255DAA5B8D3E

Comments

On 12/10/2009 at 10:36:16 AM EST Ed wrote:
1
Good point, the new 'max' datatypes offer much useful functionality that seems to have gone below the radar.

One potential minor gotcha with this can occur within string comparisons: make sure that your datatypes are CAST() to identical types if performing equality tests in string comparisons.

If you convert your data column to a 'max' column, you'll also need to CAST your comparison text to the same 'max' datatype in order to compare strings with the '=' operator, or SQLServer returns 'The data types nvarchar and ntext are incompatible in the equal to operator'.

Example for case-sensitive equality comparison: WHERE CAST(myMaxColumn AS nvarchar(MAX)) COLLATE SQL_Latin1_General_CP1_CS_AS = CAST(<cfqueryparam cfsqltype="cf_sql_varchar" value="#myTextToCompare#"> AS nvarchar(MAX))

This might be obvious to some but it tripped us up for a while!

On 12/10/2009 at 10:39:44 AM EST Pete Freitag wrote:
2
That's a great tip Ed, thanks for sharing it.

On 10/01/2010 at 5:59:24 PM EDT Jason Warren wrote:
3
You also need to reindex to release the original space. You will gain considerable db storage space.

On 08/09/2011 at 5:07:36 AM EDT Surya wrote:
4
Thanks a lot, its helped me a lot.

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?