UncleBoris (11/29/2012)

---

thanks, I see the conversion but what I do not get at this point is if a conversion is made then why wouldn't a SEEK happen like if there was no N'?

As mentioned if I convert the table field to BIGINT and run the query with the N' clause a SEEK is performed, but of course it does not if the table field type is VARCHAR.


Assuming this is the case and a SCAN must be performed then the overhead from a lot of transactions is very high in extra reads and cpu time.

uncle_boris (12/4/2012)

---

Hello, I have read your replies and looked at the link so hopefully I have the cause of the SEEK / SCAN action clear???

When passing in a NVarchar to be compared to the NC Index of VarChar type one value has to be converted.
Based on the link you sent the Varchar is lower in order so this value must be converted. Because this value is in the index ALL values must be converted to compare, therefore a SCAN must be performed.

CONVERT_IMPLICIT(nvarchar(19),[clientname].[dbo].[CARD].[CARD_NUMBER],0)=N'123456789'

When a Varchar is compared to a Varchar no conversion is necessary therefore a SEEK in done.

If I convert the field to a BIGINT datatype a conversion is needed between a NVarchar and a BIGINT. But this time the BIGINT is higher up the list so the NVARCHAR get converted to BIGINT, therefore it only happens once so a SEEK can be performed.

Seek Keys[1]: Prefix: [clientname].[dbo].[CARD].CARD_NUMBER = Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0))

thanks

SELECT *
  FROM MyTable
 WHERE SomeCol = CAST('2012-12-11' AS DATETIME)