How is CAST(VARBINARY AS NVARCHAR) implemented in SQL Server?

Question

I'm specifically referring to how stored procedure input parameters are handled. Does it just change how SQL Server interprets the data, or does it require SQL Server to make a copy of the data?

Thanks!

Answer 1

Each NVARCHAR character occupies 2 bytes, so SQL Server will pad NVARCHAR size to the nearest larger even integer:

WITH    q AS
        (
        SELECT  CAST('qqq' AS VARBINARY) vb
        )
SELECT  DATALENGTH(CAST(vb AS NVARCHAR(20)))
FROM    q

---
  4

What do you mean by "copy of the data"? This depends on the execution plan. SQL Server can make a copy of a whole table (say, in an Eager Spool) even without type casting.

Answer 2

If you assign @variableA to @variableB there has to occur a copy, irelevant of the CAST.

If you use an @variable in a query things are a lot murkier and whether a copy occurs or not depends on the context of the CAST.