In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.
Today we are going to take a look at varchar(max). Instead of using the text and ntext data types, you should get into the habit of using varchar(max) and nvarchar(max). Varchar(max) and nvarchar(max) were introduced in SQL Server 2005
The ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.
nvarchar [ ( n | max ) ]
Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying
If you have ever worked with text or ntext then you probably came to love the READTEXT, UPDATETEXT, TEXTPTR, WRITETEXT and TEXTVALID functions…….NOT!!
When using varchar(max) you can also use functions that don’t work on text like LEN and LEFT
Let’ s write some code to see what I mean. The following block of code will create one table with one row and two columns. One column is the text data type, the other column is varchar(max). Both columns will have 100000 charaters
CREATE TABLE TestMax(SomeVar varchar(max), SomeText text) GO DECLARE @v varchar(max) SELECT @v = REPLICATE(CONVERT(varchar(max),'a'),100000) INSERT TestMax VALUES(@v,@v)
Now to do a quick LEN for the varchar(max) column, there is no problem
SELECT LEN(SomeVar) FROM TestMax
If you do the same for the SomeText text column
SELECT LEN(SomeText) FROM TestMax
You get the following error
_Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of len function._
Changing LEN to DATALENGTH will work for the text data type
SELECT DATALENGTH(SomeText) from TestMax
Another function that doesn’t work with text but does work with varchar(max) is LEFT
SELECT LEFT(SomeVar,1) FROM TestMax
If you try the LEFT function with the text datatype, you get an error
SELECT LEFT(SomeText,1) FROM TestMax
Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of left function.
The SUBSTRING function does work, so you can use this instead
SELECT SUBSTRING(SomeText,1,1) FROM TestMax
Let’s add 1 character to the column, look how easy it is for varchar(max)
UPDATE TestMax SET SomeVar = SomeVar + 'Z'
SELECT REVERSE(SomeVar) FROM TestMax
Now, let’s try to do that with the text data type
UPDATE TestMax SET SomeText = SomeText + 'Z'
_Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the add operator._
Yep, kinda like that…you need to use the UPDATETEXT, TEXTPTR functions, these are a pain in the neck to use
Make a habit of changing your code to use varchar(max) and nvarchar(max) instead of text and ntext, not only is it easier to use but text and ntext have been deprecated
COme back tomorrow for another post in these series