Whenever we talk about inclusive date comparison & filtering in T-SQL, the only two options that come to mind are the BETWEEN clause and the greater than (>) and less than (<) operators paired with the equal to operator. Today, let us attempt to find out if there is a difference between the two.
Let me mention that our study today will be restricted to dates only. DATETIME values need extra “precautions” on the part of the query developer to cover the time portion in the comparison. However, most legacy systems (and as we would see from the examples in the AdventureWorks2008R2 database), use separate storage for date and time values and therefore, the need to filter on the date value is a very realistic user scenario.
Without spending much time, let’s get right along to the simple test. Let’s assume that once a year, the Adventure Works Bicycle Company prepares a list of all employees who were hired in the organization 10 years ago, (because we are in 2011, this would be in 2001) to send out an organization-wide recognition of service notification. Quite simply, the query would be something like:
SELECT * FROM HumanResources.Employee WHERE HireDate BETWEEN '2001-01-01' AND '2001-12-31' GO
We get 2 records in return:
Now, let us study the actual execution plan to find out what goes on under the covers.
As it turns out, BETWEEN is internally implemented by pairing a set of comparison operators with the equal to operator!
We can safely conclude that BETWEEN is just a facade for the comparison operators. The only difference is in the fact that comparison operators, by themselves offer exclusive comparison (i.e. the comparison excludes the value being compared to). For an inclusive comparison, they need to be paired with the equal to (=) operator – something which the BETWEEN does out-of-the-box.
So is BETWEEN really required?
I studied the Books-On-Line pages for BETWEEN and the Comparison operators, and could not find any difference other than the one mentioned above (about BETWEEN being inclusive). So, the big question now becomes whether BETWEEN is really required? Wouldn’t our code be much better if we avoid the need for Microsoft SQL Server to transform the between into comparison operators?
Well, in my humble opinion, that’s where the issue is. We, as developers and ultimately, humans, tend to make mistakes. The compilers warn us of syntactical issues with our code, but there is nothing that would warn us about a logical bug. A single miss in placing an equal to operator (or placing of an operator in excess), might result in code that would be logically incorrect. Using BETWEEN when we need inclusive comparison just helps in avoiding such errors.
So, BETWEEN is required – it’s not mandatory, but recommended. And that’s why it’s a T-SQL coding best practice.
Interested in more such surprises?
If you are interested in more such surprises, please do check out my post on the differences between CAST & CONVERT here: http://beyondrelational.com/blogs/nakul/archive/2011/07/18/cast-v-s-convert-is-there-a-difference-as-far-as-sql-server-is-concerned-which-is-better.aspx.
Until we meet next time,
Be courteous. Drive responsibly.