It has been a long time since we have played a puzzle over this blog. This Monday, let us play a quick puzzle.
SQL Server have REPLICATE function which will replicate the string passed as many as times as the second parameter. For example execute following string.
SELECT 'Ha'+REPLICATE('ha',20)
The script above will return following result:
You can notice that it has returned a string ha about 20 times after first Ha.
Now let us run following script.
DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;
The script above will return following result:
Quiz 1:
Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?
Here is the second part of the Quiz.
Quiz 2:
What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.
DB Optimizer
One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.
Please leave a comment with your answer. I will publish the answer to this puzzle on coming Friday’s blog post and will mention the names of the all the valid users.
Reference: Pinal Dave (https://blog.sqlauthority.com)
154 Comments. Leave new
You have to cast the ‘C’ as a varchar(max) as well, or else it treats the whole string as a varchar(8000).
When ‘C’ is introduced inside REPLICATE without previous declaration it is implicitly declared as VARCHAR(1). When that ‘C’ being VARCHAR(1) is concatenated to another ‘C’ which is also VARCHAR(1), the result will be ‘CC’ – VARCHAR(2). Adding another ‘C’ to it will make ‘CCC’ VARCHAR(3) and so on. That is basically what REPLICATE will do until reaching VARCHAR(8000). Adding another VARCHAR(1) to VARCHAR(8000) will not increase VARCHAR to MAX or over its 8000 limit so the all further additions will be lost and the result remains VARCHAR(8000).
Storing that result back to declared VARCHAR(MAX) variable will convert it to MAX, and additional characters can be added, like this:
DECLARE @FourthString VARCHAR(MAX)
SET @FourthString = REPLICATE(‘C’,8000)
SET @FourthString = @FourthString + REPLICATE(‘C’,3000)
SELECT LEN(@FourthString) LenFourthString;
Or – even better way – declaring ‘C’ as VARCHAR(MAX) right in the beginning will make it possible to replicate it more than 8000 times.
DECLARE @FifthString VARCHAR(MAX)
SET @FifthString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
SELECT LEN(@FifthString) LenFifthString;
Best Regards
-Nenad Zivkovic
DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = CONVERT(VARCHAR(MAX),REPLICATE(‘C’,8000)) + CONVERT(VARCHAR(MAX),REPLICATE(‘C’,3000))
SELECT LEN(@ThirdString) LenThirdString;
DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CAST(‘a’ AS varchar(max)),11000)
SELECT LEN(@ThirdString) LenThirdString
Would do the trick probably… as the replication will be treated as non-max datatype otherwise.
SET @ThirdString = cast(REPLICATE(‘C’,8000) as varchar(max)) + cast(REPLICATE(‘C’,3000) as varchar(max))
varchar(max) can only store 8000 char in it at a time. So as the type of srting is varchar(max) so it is only storing 8000 chars and skipping the rest.
SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)) ,11000)
set @firststring = REPLICATE (cast(‘A’ as varchar(max)),11000)
By doing above changes in the replicate function i can achieve the output as 11000.
1)
In SQL Server,page size 8 kb. Replication function allocates 8kb for whatever character expression that we have to replicate(first argument).for undeclared length of character expression in replication function,it can be replicate through 8000 character not more than this means 8kb.
2)solution is to give a max length/defined length for character expression for page allocation.
Query would be :
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
SELECT LEN(@ThirdString) LenThirdString;
Replicate function truncate after 8000 character. so that’s why its showing 8000.
SET @ThirdString = REPLICATE(cast (‘C’ as varchar(max)),11000)
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.
declare @third varchar(max)
select @third= REPLICATE(cast(‘c’ as varchar(max)),11000)
select len(@third)
this will solve the issue by explicitly casting string as varchar (max).
my previuos assumption about varchar(max) storing 8000 char only was wrong.
I am still confused about difference between varchar(8000) and varchar(max)
Hello:
REPLICATE is causing that behaviour. As the MSDN states: “If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.”
The solution would be to write the script for @ThirdString as follows:
DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CAST(‘C’ AS nvarchar(max)),11000)
SELECT LEN(@ThirdString) LenThirdString;
SET @ThirdString = REPLICATE(cast(‘C’ as nvarchar(max)) ,11000)
I gave you the answer on LinkedIn
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE(‘A’,4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE(‘B’,8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE((CAST(‘C’ AS varchar(max))),11000)
SELECT LEN(@ThirdString) LenThirdString;
DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(cast(‘C’ as varchar(max)),11000)
SELECT LEN(@ThirdString) LenThirdString;
The ‘C’ causes the REPLICATE function to return a value of data type varchar(8000). If more than 8000
characters are required explicit conversion of the string to be replicated to varchar(MAX) is required.
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.
That happens because SQL Server uses the data type of the first parameter as the data type of the result, which in this case will be VARCHAR(8000). So you need to cast the first parameter to VARCHAR(MAX) like as follows:
SET @ThirdString = REPLICATE(CAST(‘C’ AS VARCHAR(MAX)),11000)
DECLARE @ThirdString VARCHAR(max)
SET @ThirdString = REPLICATE(CAST(‘c’ AS varchar(max)), 11000)
SELECT @ThirdString, LEN(@ThirdString);
source:http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b8e4bd8d-b20d-4586-a359-66781ff8aa98