SQL Server with Mr. Denny:

Index Seek

Sep 17 2009   11:00AM GMT

Dates can easily be the hardest datatype to work with.



Posted by: mrdenny
T/SQL, Index Performance, Index Scan, Index Seek, Indexing

The datetime data type can be one of the hardest to work with when it comes to index optimization.  Most queries that use a datetime data type for filtering (part of the where clause) only want to match the date portion of the value.  Most people handle this via a convert function around the date column.  This causes the index that you create to become useless as the convert function causes the index to be scanned not seeked.

Continued »

Nov 24 2008   1:00PM GMT

Back To Basics: What’s the difference between a Scan and a Seek?



Posted by: mrdenny
SQL Server, Back To Basics, Index Performance, Index Scan, Index Seek, Table Scan

There are a few basic operations which SQL will perform when looking for the data that you need.  Here they are listed in the order of worst to best.

  • Table Scan
  • Clustered Index Scan
  • Index Scan
  • Clustered Index Seek
  • Index Seek

The basic rule to follow is Scans are bad, Seeks are good.

Continued »