SQL Server BETWEEN not so efficient

Question

I remember hearing or reading somewhere that

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

more effective than

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

If the date column is a DATETIME type and has the same index. Is it correct?

Answer 1

No, that is not right.

Both syntaxes are exactly the same.

BETWEEN is just syntactic sugar, short for >= … AND <= …

The same is true for all major systems ( Oracle , MySQL , PostgreSQL ), and not just for SQL Server .

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

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

Please note that the last condition is strict.

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

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

You need a strict condition, since DATETIME 's, unlike integers, is not ordered , that is, you cannot say "the last possible datetime value in 2009 " (which, of course, does not depend on the implementation).