Is it still correct than an index on a date/datetime column is not optimized for YEAR(col), MONTH(col) functions? Bill Karwin gave a pretty definitive answer here, but since this was from ten years ago I wanted to check. I would have thought that since the date column is described as
A three-byte integer packed as YYYY×16×32 + MM×32 + DD
The problem is not the representation of the date. The problem is the optimization of the query. When you use
MONTH() on a date column, the column is a function of an argument.
That means that the compiler would have a lot of information about the function in order to optimize it using an index scan or index lookup. Such as:
Of course, these things are possible. The challenge is actually building them into a function definition and into the optimizer. No one who builds SQL optimizers would want to add special cases for a specific function (well, almost no one; SQL Server does allow some use of
cast() to take advantage of an index).
So, the answer to your question is that Bill's answer is still valid.