Orsdxy

I have a field which is a varchar(20)

When this query is executed, it is fast (Uses index seek):

SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'

But this one is slow (uses index scan).

SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'

I am guessing that if I change the field to an nvarchar, then it would use the Index Seek.

Because nvarchar has higher datatype precedence than varchar so it needs to perform an implicit cast of the column to nvarchar and this prevents an index seek.

Under some collations it is able to still use a seek and just push the cast into a residual predicate against the rows matched by the seek (rather than needing to do this for every row in the entire table via a scan) but presumably you aren't using such a collation.

The effect of collation on this is illustrated below. When using the SQL collation you get a scan, for the Windows collation it calls the internal function GetRangeThroughConvert and is able to convert it into a seek.

create table  [dbo].[phone]

(

phone1 varchar(500) collate sql_latin1_general_cp1_ci_as constraint uq1 unique,

phone2 varchar(500) collate latin1_general_cs_as  constraint uq2 unique,

)



SELECT phone1 FROM [dbo].[phone] WHERE phone1 = N'2164474477'

SELECT phone2 FROM [dbo].[phone] WHERE phone2 = N'2164474477'

In the second case the compute scalar emits the following values

Expr1003 = 62

Expr1004 = '2164474477'

Expr1005 = '2164474478'

the seek predicate is on phone2 > Expr1004 and phone2 < Expr1005 so on the face of it would exclude '2164474477' but the flag 62 means that this does match.

Other answers already explain what happens. We've seen that NVARCHAR has higher precedence than VARCHAR. I want to take a moment and explain why the database must cast every row in the column as an NVARCHAR, rather than casting the single supplied value as VARCHAR, even though the second option is cleary much faster, both intuitively and empirically.

Casting from NVARCHAR to VARCHAR is a narrowing conversion. That is, NVARCHAR has potentially more information than a similar VARCHAR value. It's not possible to represent every NVARCHAR input with a VARCHAR output, so casting from the former to the latter potentially loses some information. But opposite cast is a widening conversion. Casting from a VARCHAR value to an NVARCHAR value never loses information; it's safe.

Now you and I know that your narrowing conversion is also safe for this particular data. But the Sql Server query optimizer doesn't know that. It only sees the data type information when building the execution plan. Sql Server wants to cache it's execution plans and potentially re-use them, to save the work of building the same plans again in the future, and therefore the actual value doesn't matter as much as getting the generic conversion right.

When presented with two mismatched types for a comparison, Sql Server will always choose the safe conversion. It's same old "correctness trumps performance" mantra. Or, to paraphrase Benjamin Franklin, "He who would trade essential correctness for a little performance deserve neither correctness nor performance."

Here's the real kicker: now that we're making this cast, we have to do it for every row in the table. This is true even for rows that would not otherwise match the comparison filter. Morever, the cast values from the columns are no longer the same as the values stored in an index, such that any index on the column is now worthless for this query.

I think you're very lucky to be getting an index scan for this query, rather than a full table scan, and it's likely because there is a covering index that meets the needs of the query (the optimizer can choose to cast all the records int he index as easily as all the records in the table).

But you can fix things for this query by forcing the cast you actually want:

SELECT * FROM [dbo].[phone] WHERE phone = cast(N'2164474477' as varchar(20))

Of course, it's even better to fix it by using a VARCHAR value in the first place:

SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'

If this query was created from a .Net program, it's probably related to the AddWithValue() function. I've written about this issue in the past and how to handle it correctly.

 SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'

is interpreted as

 SELECT * from [dbo].[phone] WHERE CAST(phone as NVARCHAR) = N'2164474477'

which prevents index usage