Beware of data truncation – Replicate function with Varchar(max) datatype

As you know, Replicate function is used to repeat a character expression for a specified number of times. But by default the result is converted to varchar with maximum size of 8000 when you don’t convert the expression to specific type

Consider the following example

declare @v varchar(max)
set @v=replicate('a',50000)
select len(@v),datalength(@v)

What do you think the result would be? 50000 Right? But the result is 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype.

To get a correct result, you need to convert the expression to the type of varchar(max) as shown below

declare @v varchar(max)
set @v=replicate(convert(varchar(max),'a'),50000)
select len(@v),datalength(@v)

Now the result is 50000 as expected.

So you need to be aware of this implicit convertion when using Replicate function to assingn value to the column of varchar(max) datatype

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s