SQL Server 2008 performance when using BETWEEN

Question

Is there any performance difference when using BETWEEN Date1 AND Date2 and Date >= Date1 and Date <= Date2 ?

Answer 1

They are similar in every detail except performance

BETWEEN is a shorthand for the longer syntax BETWEEN is easy to read and maintain

One thing to be careful of, is if you are using this against a DATETIME, the match for the end date will be the beginning of the day:

<= 20/10/2009

is not the same as:

<= 20/10/2009 23:59:59

---

But There is a difference in performance .

SQL> create table t (n number, m number not null)
  2  partition by list (n)
  3  (
  4    partition t_0 values (0),
  5    partition t_1 values (1)
  6  );

Table created.

SQL> create index i_t_m on t (m) local;

Index created.

The plan with between:

SQL> select /*+ index(t i_t_m) */ m
  2    from t
  3    where n between 0 and 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 555829789

------------------------------------------------------------
| Id  | Operation                          | Name  | Rows  |
------------------------------------------------------------
|   0 | SELECT STATEMENT                   |       |     1 |
|   1 |  PARTITION LIST ALL                |       |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T     |     1 |
|   3 |    INDEX FULL SCAN                 | I_T_M |     1 |
------------------------------------------------------------

And the other plan:

SQL> select /*+ index(t i_t_m) */ m
  2    from t
  3    where n >= 0 and n <= 1;

Execution Plan
--------------------------------------------
Plan hash value: 1131641999

--------------------------------------------
| Id  | Operation          | Name  | Rows  |
--------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |
|   1 |  PARTITION LIST ALL|       |     1 |
|   2 |   INDEX FULL SCAN  | I_T_M |     1 |

That is exactly how we discovered it. After switching to between, the query magically started to perform slower.

Answer 2

I think there is no difference because it only makes the language "sweeter" for humans to use:D in other words "Syntactic sugar" there is no performance benefit :)