1
REPLICATE ( string_expression ,integer_expression )  

As documentation says, it

returns the same type as string_expression.

I see that return type is always VARCHAR.

declare @vb varbinary(10) = 0x01
declare @res sql_variant = REPLICATE(@vb, 2)

SELECT sql_variant_property(@res, 'BaseType')  

varchar

Is the documentation wrong?

I raised this as a documentation issue.

0

3 Answers 3

2

This appears to be mainly a documentation issue. I had originally thought that this was a bug within the REPLICATE function, but I've done a bit of testing across most other string functions and have found that this behavior is common among most of them. To summarize, this behavior is:

  1. If there is one "string" parameter and VARBINARY is passed in, it is assumed to be VARCHAR in the code page associated with the database's default collation.
  2. If there are multiple "string" parameters:
    1. If all string params are passed in as VARBINARY, they are assumed to be VARCHAR
    2. If one or more params are passed in as VARCHAR and none as NVARCHAR, the VARBINARY is assumed to be VARCHAR
    3. If any string params are passed in as NVARCHAR, the VARBINARY is assumed to be NVARCHAR

As far as I can tell, the only string function that will return VARBINARY given VARBINARY input is SUBSTRING.

So the problem is really that the documentation isn't specific enough. The statement:

string_expression can be either character or binary data.

is not incorrect. It just doesn't state that binary data will be implicitly converted to VARCHAR. If this were to be stated, then getting VARCHAR output for VARBINARY input makes sense, and thus the statement:

Returns the same type as string_expression.

is also not incorrect.

This behavior, of course, would ideally be noted across all of the functions that it pertains to, and not just this one.

I have already added this info to the documentation issue on GitHub that was opened by the O.P..

1
  • The documentation for + 'string' concatenation is no better. Consider @vb + @vb returns varbinary.
    – Paul White
    Dec 19, 2019 at 8:27
2

I suspect that REPLICATE is expecting a string not a binary type, and an implicit conversion is taking place from VARBINARY to VARCHAR. If you update your example like so you get the expected type:

declare @vb nvarchar(10) = '#'
declare @res sql_variant = REPLICATE(@vb, 2)
SELECT sql_variant_property(@res, 'BaseType')

Even though it accepts a binary input, the spec does specifically state that parameter is a "string expression" which is why I suspect type coercion is happening before the function is actually run. By "expected type" I am referring to it not being fixed as VARCHAR irrespective of input type which might be implied when looking in your result on its own. So I think the documentation is correct, but perhaps not perfectly clear: the function returns the same [N][VAR]CHAR(<len>) type that is receives, and in your test it is receiving a VARCHAR() due to implicit conversion from VARBINARY to VARCHAR.

0
1

By performing a little sleight-of-hand with SQL I can get SSMS to show an execution plan. This:

select REPLICATE(@vb, 2) where 1 = 1;

Has a single CONSTANT SCAN showing the implicit conversion, as David surmised:

enter image description here

1
  • Yep, it makes this conversion visible. Good way to check what's going on BTW, thank you.
    – Alsin
    Dec 19, 2019 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.