Posted by: Denny Cherry
Index Performance, Index Scan, Index Seek, Indexing, T/SQL
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.
You can see this yourself by creating a table, with a date field, then stuff some records into that table.
CREATE TABLE sample_table (id INT IDENTITY(1,1) PRIMARY KEY, dt DATETIME) go CREATE INDEX ix_sample_table ON dbo.sample_table (dt) go INSERT INTO sample_table (dt) SELECT '1/1/2000 15:00:00' UNION SELECT '1/2/2000 12:00:00' UNION SELECT '1/3/2000 08:00:00' go
You can now query the dt column and use the convert function. When you view the execution plan you’ll see that an index scan is being used.
SELECT id, dt FROM sample_table WHERE CONVERT(varchar(10), dt, 101) = '01/01/2000'
But it you use the BETWEEN operator instead of the = operator you’ll see the engine using an index seek instead of the index scan.
SELECT id, dt FROM sample_table WHERE dt BETWEEN '01/01/2000' AND '01/02/2000'
Now the same thing applies when using other functions to the datetime column in the table. For example if you were to have a system where people need to search based on the timezone where the data is stored in UTC time, but searching needs to happen based on the users time zone.
The easiest way to do this would be to put a dateadd function around the dt field. However even when using the BETWEEN operator you still use an index scan, instead of the seek. In this case I’m searching against the pacific timezone.
SELECT id, dt FROM sample_table WHERE dateadd(mi, -420, dt) BETWEEN '01/01/2000' AND '01/02/2000'
Now, if you switch the -420 to 420, and put the DATEADD function around the dates in the BETWEEN operator the index seek will be preserved. The query looks a little strange, but that’s ok. It’s the end result that we are looking for here.
SELECT id, dt FROM sample_table WHERE dt BETWEEN dateadd(mi, 420, '01/01/2000') AND dateadd(mi, 420, '01/02/2000')
Now if you have the UtcOffset that you want to use stored in another table such as an Account table. We’ll recreate the sample_table to go with this.
CREATE TABLE sample_table (id INT IDENTITY(1,1) PRIMARY KEY, dt DATETIME, AccountId int) CREATE TABLE Account (AccountId INT PRIMARY KEY, UtcOffset INT) go CREATE INDEX ix_sample_table ON dbo.sample_table (dt) INCLUDE (AccountId) go INSERT INTO sample_table (dt, AccountId) SELECT '1/1/2000 15:00:00', 1 UNION SELECT '1/2/2000 12:00:00', 2 UNION SELECT '1/3/2000 08:00:00', 1 INSERT INTO Account SELECT 1, -420 UNION SELECT 2, -300
In order to make this work, you have to take the UtcOffset value *-1 so that the date is adjusted in the correct direction to make the query work.
SELECT id, dt FROM sample_table JOIN Account on sample_table.AccountId = Account.AccountId WHERE dt BETWEEN dateadd(mi, Account.UtcOffset*-1, '01/01/2000') AND dateadd(mi, Account.UtcOffset*-1, '01/02/2000')
(Ignore the Clustered Index Scan on the Account table, correcting that isn’t the goal of this post.
Hopefully you find this technique useful.