Querying Dates in SQL Server for Performance

I tested six ways of filtering a datetime column and one of them silently returned a few thousand fewer rows than the others. The reads were fine and the query was fast, which is what made it worth writing down.

Recently I explored how different query patterns for filtering dates affect performance. For my tests, I used SET STATISTICS IO ON and SET STATISTICS TIME ON.

Setup

I tasked myself with retrieving user activity from the StackOverflow database Users table, specifically for the year 2017, since the database I have only has data up through 2018. The table has millions of rows, so I thought this would be a perfect example. A quick note: there is a non-clustered index covering this query, which I’ve scripted below for you to check. LastAccessDate is a datetime column.

USE [StackOverflow]
GO

SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [IX_LastAccessDate_DisplayName_Reputation] ON [dbo].[Users]
(
	[LastAccessDate] ASC,
	[DisplayName] ASC,
	[Reputation] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Here are the six approaches I tested, along with my results.

1. For this query, I used BETWEEN with precise timestamps. This is probably the most common method I see in the wild.

2. Next, I used >= and < again with precise timestamps. This method is also relatively common.

I’d argue there’s nothing unusual so far. Both queries are running fast enough for my needs, and I got the data I expected. Lets keep going.

3. Next, I used BETWEEN again, but without the precise timestamps. I wanted to see if using less precise date format would make my query non-sargable.

We see no significant degradation to the performance of the query in terms of speed or reads, but if you look closely, I actually got a few thousand fewer rows than my other two tests. We’ll talk more about that later.

4. After that, I decided to use the same idea for >= and <. To which there was nothing significant to report.

5. Next, I chose to use BETWEEN again, but convert the datetime value to a VARCHAR to manipulate the date format.

6. Lastly, doing this but with >= and < and converting the datetime value to a VARCHAR, as I did in step 5.

About those missing rows

Query 3 is the one worth going back to. BETWEEN '2017-01-01' AND '2017-12-31' on a datetime column is inclusive at both ends, but the upper bound is 2017-12-31 00:00:00.000, so everything that happened during the last day of the year is outside the range. A few thousand rows on this table, and they went missing without any indication that the query had done something different.

That’s the failure I care about more than the reads. A slow query announces itself. This one comes back fast, with a plausible number of rows, and nothing tells you the answer is wrong. >= '2017-01-01' AND < '2018-01-01' gets all of it, and it keeps working when the column is datetime2 with more precision, where a BETWEEN ... AND '2017-12-31 23:59:59.997' style fix quietly stops covering the last few fractions of a second.

Final Thoughts

Logical reads go up sharply when using CONVERT or similar formatting functions, because they render queries non-sargable and lead to table scans instead of index seeks. Wrapping the column in a function means the index on that column can’t be used to seek, since the index stores the column’s values and not the converted ones.

I keep using the word sargable, so here’s where it comes from. Microsoft’s index design guide has it as “a Search ARGumentable predicate that can use an index to speed up the execution of the query,” and it recommends indexing “the columns that are frequently used in predicates and join expressions in queries. These are your SARGable columns.”

I went looking for a page that states the function-wrapping rule outright and couldn’t find one, which surprised me for something so widely repeated. So the reads in the screenshots above are my own measurement rather than a documented rule I can point you at.

The performance difference between BETWEEN and >=/< on the sargable versions was not significant, which was not what I expected going in. What made me switch isn’t speed, it’s the boundary. Needless to say, I’ll be avoiding CONVERT on my datetime columns, and BETWEEN on anything with a time component.

Since I’m writing this on December 31st, 2024, I want to wish everyone a happy New Year! Thank you for reading!