Index Seek

Dates can easily be the hardest datatype to work with.

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 »

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

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 »