Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

So, 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.

This brings me to my question: 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).

Thanks in advance.

share|improve this question

migrated from stackoverflow.com Dec 28 '11 at 12:25

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 6 down vote accepted


Following gbn's suggestion to look at local variables I have now done this.

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 < Scalar Operator([Expr1007])

Testing Code

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

    [name] ASC


SET @name = 'rpc'

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

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

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

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

Execution Plans


share|improve this answer
I'd like to see this with a local variable rather than a constant. The optimiser converts the constant to the correct datatype... –  gbn Jul 2 '10 at 5:13
and nvarchar(max) too. Sorry, I don't have SQL just now installed. –  gbn Jul 2 '10 at 5:26
@gbn - I've just tried that (see edit). It seems that can actually improve the situation compared to using a string literal. –  Martin Smith Jul 2 '10 at 10:40
Thank you... I'm impressed by the first set of numbers... –  gbn Jul 2 '10 at 13:18

In general the CAST will kill performance because it invalidates any use of index seeks as Martin Smith's last example shows. CASTing to nvarchar(max) or to a different length means a different data type: the fact it's all nvarchar is irrelevant.

On top of that, the datatype of the right hand side of the compare matters too. If it's a local variable or parameter of a different length then one side will be implicitly CAST to the widest of the 2 datatypes (see datatype precedence).

Basically, if you have a general CAST to nvarchar(max) it will bollix things up. I would consider fixing the use of ntext before I added CAST all over.


Martin Smith's first query invalidates most of this answer. Perhaps behaviour has improved SQL Server 2005 to 2008.. i need to investigate more

Edit 2 (Oct 2011):

The conversion may not show in the query plan. See Paul White's blog article

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.