BETWEEN versus ">=" AND "<=" in SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have to search a vary large table for records that satisfy date criteria.
The date columns are not indexed.
I need to do the following
SELECT ...
FROM ...
WHERE (Date1 >= ParmDate1 AND Date1 <= ParmDate2) OR
(Date2 >= ParmDate1 AND Date2 <= ParmDate2)

I guess my questions are is it any more efficient to use BETWEEN syntax and
would it make a vast difference to have the data columns indexed?
 
Well,
SomeField BETWEEN x and Y
is probably converted by the query engine into
SomeField >= X and SomeField <= Y
So, there is no real difference in performance between the two. It is a bit
easier to write the query with Between .. and ...

Indexing the fields you are using can make a huge difference in performance.
However, it is a tradeoff. Every index takes space and every index has to
be updated when the contents of fields in the index are updated, added, or
inserted.
 
I guess my questions are is it any more efficient to use BETWEEN syntax
Click to expand...

Not to my knowledge. BETWEEN is logically identical to

[fieldname] >= [first argument] AND [fieldname] <= [second argument]
and would it make a vast difference to have the data columns indexed?
Click to expand...

Yes, it would. A *dramatic* difference in a big table.

John W. Vinson[MVP]
 
Back
Top