SQL Server BETWEEN not as efficient

Question

I recall hearing or reading somewhere that

SELECT * from TABLE where date >= '2009-01-01' AND date <= '2009-12-31'

is more efficient than

SELECT * from TABLE where date BETWEEN '2009-01-01' AND '2009-12-31'

Where date column is a DATETIME type and has the same index. Is this correct?

Answer 1

Not, it's not correct.

Both syntaxes are absolutely same.

BETWEEN is just a syntax sugar, a shorthand for >= … AND <= …

Same is true for all major systems (Oracle, MySQL, PostgreSQL), not only for SQL Server.

However, if you want to check for the date to be in the current year, you should use this syntax:

date >= '2009-01-01' AND date < '2010-01-01'

Note that the last condition is strict.

This is semantically different from BETWEEN and it is a preferred way to query for the current year (rather than YEAR(date) = 2009 which is not sargable).

You cannot rewrite this condition as a BETWEEN since the last inequality is strict and BETWEEN condition includes the range boundaries.

You need the strict condition since DATETIME's, unlike integers, are not well-ordered, that is you cannot tell the "last possible datetime value in 2009" (which is not implementation-specific, of course).