Varchar(max) datatype and Replicate function

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 of maximum size 8000 when you dont convert the expression to specific type

Consider the following example

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

Note that the result is not 50000 but 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)

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