Search code examples
sqlsql-servert-sqlsql-server-2000

Using BETWEEN clause

Whenever you write a query where you need to filter out rows on a range of values - then should I use the BETWEEN clause or <= and >= ?

Which one is better in performance?

Solution

  • Neither. They create exactly the same execution plan.

    The times where I use them depends not on performance, but on the data.


    If the data are Discrete Values, then I use BETWEEN...

    • x BETWEEN 0 AND 9


    But if the data are Continuous Values, then that doesn't work so well...

    • x BETWEEN 0.000 AND 9.999999999999999999


    Instead, I use >= AND <...

    • x >= 0 AND x < 10


    Interestingly, however, the >= AND < technique actually works for both Continuous and Discrete data types. So, in general, I rarely use BETWEEN at all.