Performance hit using CAST in T-SQL

We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField).

If myField is of type NVARCHAR, we can do a comparison of said field against a string like so: myField = 'foo'.

However, this does not work for fields of type NTEXT. Thus, we have to do the comparison with a cast: CAST(myField as NVARCHAR(MAX)) = 'foo'. This will in fact work if myField is of type NVARCHAR or NTEXT.

What is the performance hit of doing the aforementioned cast on a field that is already of type NVARCHAR? My hope is that SQL Server is smart enough to dynamically recognize that myField is already of type NVARCHAR (effectively turning the CAST into a no-op).

If the cast of the column is to exactly the same datatype and length and the seek predicate is a literal it does indeed seem to disregard it or treat it as a no-op and does an index seek on equality.

Seek Keys[1]: Prefix: [tempdb].[dbo].[#test].name = Scalar Operator(N'rpc')


If the cast of the column is to the same datatype but greater length and the seek predicate is a string literal it causes an index scan. This is obviously to be avoided.

If the cast of the column is to the same datatype and the same or greater length and the seek predicate is a local variable it adds a compute scalar operator to the execution plan. This calls GetRangeThroughConvert and outputs a range.

This range is used to do an index seek and seems pretty efficient

`Seek Keys[1]:
Start: [tempdb].[dbo].[#test].name > Scalar Operator([Expr1006]),
End: [tempdb].[dbo].[#test].name

Testing Code

SELECT *
 INTO #test
  FROM [master].[dbo].[spt_values]

CREATE NONCLUSTERED INDEX [ixname] ON #test
(
    [name] ASC
)

DECLARE @name NVARCHAR(MAX)

SET @name = 'rpc'

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))= @name --Cast the same and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))=@name --Cast to longer and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))='rpc' --Cast the same and literal

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))='rpc' --Cast to longer and literal

StackExchange Database Administrators Q#9832, answer score: 12