BETWEEN vs >= and <=

I love it when I get queries that are actually easy to answer.

Today, one of my developer friends asked me if it was better to use BETWEEN or to use >= and <= when filtering for a range of dates.

From a logic perspective, I like the idea that a single predicate expresses your intent rather than needing two predicates to do the same. For example, consider the following two queries:

I’d argue that the first one expresses the intent slightly more clearly than the second query. The intent is to find orders in a particular range of dates. Having that as a single predicate expresses that intent slightly more clearly than having to assemble the intent from multiple predicates. At least I think so.

But the bigger question is about performance. It’s easy to see that they are identical. If you enter the following query against the AdventureWorks database:

Then request an estimated execution plan (Ctrl-L), you’ll see this:

The missing index warning isn’t relevant to this discussion and if you hover over the Clustered Index Scan, you’ll see this:

Note under the Predicate heading that SQL Server has converted the original BETWEEN predicate into a pair of >= and <= predicates anyway. You’ll find it does the same for LIKE predicates as well. LIKE ’A%’ becomes >= ’A’ AND < ’B’.

So performance is identical. It’s more of a style issue, and I think that BETWEEN is (only very) slightly more expressive so I’d prefer it.

UPDATE: Aaron Bertrand posted a pertinent comment on this. I would only lean to using BETWEEN if I’m strictly working with dates or other types of discrete values (ints, etc.), not with datetime values that actually contain times. If that was the case, I’d definitely lean towards the separate predicates.

2016-03-02