Use varchar(max) instead of text in SQL Server
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
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
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
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!
- Cheat Sheet for SQL Server - April 20, 2009
- DateFormat for SQL Server - December 5, 2008
- Dear SQL Server Enterprise Manager Developer - July 31, 2008
- Try Catch for SQLServer T-SQL - April 7, 2008
- Use Char instead of Varchar to Store UUID's - February 16, 2007
- ColdFusion defaults avoid flawed Random Number Generator
- Apache Security Patches on CentOS / RHEL
- FuseGuard 2.4 Released
- New HackMyCF Features
- Blocking .svn and .git Directories on Apache or IIS
- CFDocs site now Open Source
- Getting Size of Heap and Non Heap Memory in CFML
- Firefox Aurora now Supports Content Security Policy 1.0