Where Clause and indexes

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  June 5, 2008 at 10:29 am

  #186522

  HI Guys,

  I saw someone post something about changing a where clause so that the Index on a date field is using a Seek instead of scan:

  HEre is a sample

  --USES A SEEK

  SELECT *

  FROM #Test

  WHERE

  somedate >= dateadd(yy,@year - 1900, 0)

  and somedate < dateadd(yy,(@year - 1900) + 1, 0)

  --USES A SCAN

  SELECT *

  FROM #Test

  WHERE

  YEAR(somedate) = @Year

  Could some please give me a breif explination of why the query plan changes here or an article I could ready about this?

  Thanks

  Chris

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 5, 2008 at 10:49 am

  #824964

  I recognize the code, and it was I.

  Here is a link to an article that may help: http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/bewareofsearchargumentsargdatatypes/2485/

  😎

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  June 6, 2008 at 2:19 am

  #825323

  indeed it was you 🙂

  thanks

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life