A Query Method Faster Than BETWEEN

This is an invaluable trick that can be sometimes be used instead of the BETWEEN operator. A common place this can be used is in an historical table that contains EffectiveFrom & EffectiveTo dates. In these kinds of tables, many historical versions of a record can be stored. Each record in this scenario will contain a Start & End Date which signifies the time span when a record is active. Because two records cannot be active at the same time, we can be sure that the Start & End dates will never overlap.

While this describes one particular scenario for this example, there are many other situations this method can be used.

Let’s look at the typical query we would use to find the record which is active for an employee at a specific point in time.

[cc lang=”sql”]
SELECT *
FROM [HumanResources].[EmployeeDepartmentHistory]
WHERE EmployeeID = 274
AND ‘2001-04-28’ BETWEEN StartDate AND EndDate
[/cc]

This query will return to us the single record that is active as of ‘2001-04-28’. However if you think about the constraints our table contains, we actually only need to query one column (the StartDate) in order to isolate this record. This is because the StartDate will not overlap any other StartDate & EndDate for this employee.

Here’s an example:

[cc lang=”sql”]
SELECT TOP 1 *
FROM [HumanResources].[EmployeeDepartmentHistory]
WHERE EmployeeID = 274
AND StartDate >= ‘2001-04-28’
ORDER BY StartDate
[/cc]

Because only one record can be active for an employee at a single point in time, we can be sure that if we take the minimum start date that is less than or equal to the as of date, this will also return the active record. Since this query only needs a one column in order to isolate the record, it can be much quicker than our BETWEEN version.

If we wanted to be sure this date range fell within our as of date, would could also use this fail safe:
[cc lang=”sql”]
SELECT *
FROM (
SELECT TOP 1 *
FROM [HumanResources].[EmployeeDepartmentHistory]
WHERE EmployeeID = 274
AND StartDate >= ‘2001-04-28’
ORDER BY StartDate
) t
WHERE ‘2001-04-28’ <= EndDate [/cc] While this trick may show marginal improvements in some tables, it can be many many times faster in other scenarios. It all depends on how many versions of a single record exist, and the size of the table. I recently employed this method that took a query down from 30 ms, to under half of one millisecond.

5 comments
Richard 01 Aug 2016 at 8:45 am

I think by doing top 1 with a >= you’re not being semantic enough – If a junior engineer saw this it would be confusing, whereas using the between and would make much more sense. The saving from 30ms to 0.5 ms doesn’t really justify the obscurification for me.

Val 03 Apr 2012 at 12:39 am

Hi everybody,

good article Mr Dieter.

In the 2nd and the 3rd query shouldn’t you write
StartDate >= ‘2001-04-28’ instead of StartDate <= '2001-04-28' ?

Cheers !

V.

Derek Dieter 03 Apr 2012 at 8:40 pm

Yes, you’re correct. Thank you Val

Jim 28 Oct 2013 at 9:38 pm

I disagree. If you get the first StartDate >= ‘2001-04-28’, you will not get the row in effect on 4/28/01 unless that was the actual start date. And if you select the first StartDate <= '2001-04-28', you will only get the correct row if there is only 1 row that meets the criteria. If 1 row is 2001-01-01 thru 2001-01-30, and the next is 2001-01-31 thru 2002-01-01…..

Cheers!

Derek Dieter 10 Dec 2013 at 4:44 am

Not sure I follow the first part.. It’s a >= not an =.

As for the second part, I mention that in the post.

Featured Articles

 Site Author

  • Thanks for visiting!