when is varchar(max) not a varchar(max)

I was looking into a question raised at the lauanch about the best way to pass large sets of values to sql. Currently the person is restricted by the 8000 character limit for a varchar in SQL 2000. A number of options where mentioned including bulk inserting, xml, a user defined type and a varchar(max).

Well in looking into the last, varchar(max) I came across some interesting behaviour.

If you declare a variable as varchar(max) then it does not force an expression that is used to set the value to use varchar(max) let me show an example

declare @t nvarchar(max)
set @t = replicate('simon,',2000) + 'some more text'
select len(@t)

This highlights that the expression replicate('simon,',4000) is evaluated to be of type varchar(8000) when the text 'som more text' is added the variable is already full and so is not added, thus the length of @t is 8000.

This seems to be because of the 'simon,' literal that does not have an explicit type, and so the implied type is a varchar that by default has a maximum length of 8000. If the literal is explicitly converted to varchar(max) then we have a different result.

declare @t nvarchar(max)
set @t = replicate(cast('simon,' as nvarchar(max)),2000) + 'some more text'
select len(@t)

This results in a string of length 12014, as expected.

From what I can deduce the data type is determined as the largest data type in the expression, unfortunately the engine does not figure out that the combination of two variables may require the use of the (max) data type and so truncation occurs.

Therefore to avoid this make sure that all components of an expression evaluate to the correct datatype, i.e. literals are explicitly converted to the required data type. You could be conservative as in the above example however to avoid confusion it may be better to convert all literals, i.e.

declare @t nvarchar(max)
set @t = replicate(cast('simon,' as nvarchar(max)),2000) + cast('some more text'as nvarchar(max))
select len(@t)

Update: Corrected barckets in last code snippet

-
Published 18 November 2005 15:19 by simonsabin
Filed under: ,

Comments

11 September 2006 20:41 by Louis Davidson

# re: when is varchar(max) not a varchar(max)

Was looking around for Varchar(max) info and found your post.  Good stuff. I wanted to point out a resource that answers the what datatype from an operation question.  I came across it by a forum post and used it for this blog (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1374.entry)

In BOL, search for "Precision, Scale, and Length".  It covers how datatypes are chosen in an operation.
15 September 2006 10:39 by SimonS SQL Server Stuff

# Support for varchar(max) with string functions

You may have read my previous post about string literals and their data types (When is varchar(max) no...
15 September 2006 10:39 by SimonS' SQL Server Stuff

# Support for varchar(max) with string functions

You may have read my previous post about string literals and their data types (When is varchar(max) no...
11 July 2009 16:38 by SimonS Blog on SQL Server Stuff

# String concatenation with max types stops plan caching

Erland Sommarskog ( http://www.sommarskog.se/ ) was recently investigating delimited strings and was