Lately I’ve been reading some contradicting information about SQL indexes.
This time it was that DateTime fields in a database was prime candidates for usage as index keys, and that DateTime wasn’t prime candidates.
The reasoning behind the first statement looks to be that if you have a date and/or time stamp that you use in your data, you often filter on said data, thus making it advisable to use as index.
The reasoning behind the contrary statement that DateTime data is not advisable to use as index keys is that because a DateTime is stored with time down to milliseconds, and it is rare that you actually need that type of precesion in your queries, you will induce index scans more often by having the DateTime in your index.
Color me confused and call me Barney. So I decided to try and gain an understanding of these two contradicting statements to see whether they both were correct. Not that I think I can prove anything, but hey, I need to satisfy my own curiosity a bit.
So I decided to make a table:
And filled it with testdata.
DECLARE @i INT
SET @i = 0
DECLARE @d DateTime
DECLARE @d2 DateTime
WHILE @i < 10000 BEGIN
SET @d = GETDATE()
SET @d = DATEADD(dd, @i % 4, @d)
SET @d2 = CONVERT(VARCHAR(16), @d, 112)
INSERT INTO DateTimeTest
VALUES
('abcdefg' + STR(@i) + 'h' + STR(@i % 4), @d, @d2)
END
You’ll notice that I have two fields with DateTime (one and two), and this is because I wanted to clean one of the dates for its time representation to see if that made any difference. (Basically, it didn’t :))
I then made 2 indexes (unique, non-clustered). IX1 was made containing DateTimeOne and id, and IX2 was made containing DateTimeTwo and id (remember, DateTimeTwo contains the same date as DateTimeOne, but the time representation is set to 00:00:000)
I then ran a series of queries to see what result I would get in the execution plan.
For example:
DECLARE @d DateTime
SET @d = '2008-01-29'
SELECT id
FROM DateTimeTest
WHERE DateTimeOne >= @d
SELECT id
FROM DateTimeTest
WHERE DateTimeTwo >= @d
This provides the same result for both queries, both seeking the index. I tried the same when selecting the DateTimeOne and DateTimeTwo values out in their respective queries and got the same result. 2 times Index seeks, and both queries with the same numbers in the execution plan.
So this had me starting to believe that the first case from the starting conundrum is true. However...what if you only needed rows from a specific day or similar information?
So I ran some queries along this type:
DECLARE @d DateTime
SET @d = DATEADD(dd,2, GETDATE())
SELECT id
FROM DateTimeTest
WHERE DATEPART(dd, DateTimeOne) < DATEPART(dd, @d)
SELECT id
FROM DateTimeTest
WHERE DATEPART(dd, DateTimeTwo) < DATEPART(dd, @d)
And lo and behold – I got my elusive index scans. Naturally I would say, because now I could not use the indexes as they were build, but had to compute on them, making them suboptimal.
So it looks that there are truths in both statements, and it depends on how you use your data, but that is basically true for any index. To make the optimal indexes, one should always analyze the data.
Now that I was in the middle of all this testing, I started to wonder that since the internal representation of a datetime field is 2x4 bytes of integer, then – could that be used to speed up the queries.
So I made a third date field, but in this I placed the value of DateTimeOne cast as an integer.
I then ran the queries:
DECLARE @d DateTime
SET @d = DATEADD(dd,2, GETDATE())
SELECT id
FROM DateTimeTest
WHERE DateTimeOne < @d
SELECT id
FROM DateTimeTest
WHERE DateTimeOneInt < CAST(@d AS integer)
To my surprise, this performed equally well for both queries, both showing the same numbers in the execution plan. It wasn’t until I had to select the DateTimeOneInt as a DateTime and thus had to convert in my SELECT statement that a small penalty was incurred. I also found that I did infact not really need to cast @d to an integer. I could just use it in the WHERE statement directly.