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


Character column length vs Data length

When you use character datatypes (char,varchar,nchar,etc),always make sure that column has enough length to have data. Otherwise sometimes you would get errors and sometimes you won’t. 
Consider the following example

Declare @t table(id int, test_col varchar(2))
Insert into @t select 1,'test'
Select id,test_col from @t

Now, You get the following error

Msg 8152, Level 16, State 14, Line 4
String or binary data would be truncated.
The statement has been terminated.

Because the column length of test_col is not enough to have the value ‘test’
But only when you insert data to a table, you get this error.
In other cases you dont

Declare @v varchar(2)
set @v='test'
select @v

Now there is no error but the value is truncated and
only first two characters are assigned to the variable.
The same case is applied when you use a stored procedure with input parameter create

procedure test(@test_param varchar(2))
Select @test_param
EXEC test1 'test'
drop procedure test

Now see what happens when you don’t specify the length

Declare @v varcharset @v='test'
select @v

By default the length is 1

Consider another example

       'This has more than thirty characters' as varchar
          ) as data

The result is

This has more than thirty char

When you cast a literal to character type without specifying the length,
by default it is 30.

Depends on the usage, if the column does not have enough length
1 Error is generated
2 Data is truncated
3 Default length is assumed
So you need to specify enough column length when you use character datatypes