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

Output

——–

100000

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

Output

——–

100000

Another function that doesn’t work with text but does work with varchar(max) is LEFT

SELECT LEFT(SomeVar,1) FROM TestMax

Output

——–

a

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

Output

——–

a

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

Output

——————-

Zaaaaaaaaaaaaaaaaaa…..

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