Home Forums SQL Server 2008 SQL Server 2008 Administration Whydoes 'N before the WHERE value causes an Index SCAN RE: Whydoes 'N before the WHERE value causes an Index SCAN

  • November 29, 2012 at 11:25 pm

    #1564154

    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.

    Converting the VARCHAR column to BIGINT changes where the implicit conversion occurs. Instead of converting the value of the column in each row, the constant value is implicitly converted. Again, you can see this by looking at the properties of the seek operator in your actual execution plan.