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

I'm specifically referring to how stor开发者_StackOverflow中文版ed 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!

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.

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.