SQL Server Performance Forum – Threads Archive

Range Selection

The table has about 4 million rows There is a clustered index on ipFROM and ipTO.
Which would be faster: ——–
Select * from tblIPData Where @hash between ipFROM And ipTO
——–
or
——–
Select * from tblIPData Where ipFROM <= @hash And @hash <= ipTO
——– They are both fairly quick, but I’m sure you guys know the inner workings of how a query like this is executed. Is there a difference?

The "BETWEEN" operator should be reserved for non-inclusive searches, i.e. if you needed
Select * from tblIPData Where ipFROM < @hash And @hash < ipTO
without the "=", then you cannot use the between clause. Otherwise, I would say it is better to use it in terms of sheer simplicity. You probably won’t see a performance difference since the queries are identical. You should be able to see the similarities in the execution plan.

Set the execution plan and see the results Madhivanan Failing to plan is Planning to fail

Tahsin, You contradict yourself. The BETWEEN operator is for inclusive searches only. You need the > and < operators for non-inclusive searches. Note that NOT BETWEEN is non-inclusive too.

It doesn’t matter, the execution plan will be the same because the first query is internally represented as the second one.
Having ipTo as a second column of the clustered index doesn’t help performance of the query we are talking about. It is enough to have clustered index on just ipFrom.

Adriaan, you are correct. I meant to say "inclusive". I think my logic was screwed up as I was typing [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Tahsin The "BETWEEN" operator should be reserved for <s>non</s>-inclusive searches, i.e. if you needed Select * from tblIPData Where ipFROM < @hash And @hash < ipTO without the "=", then you cannot use the between clause. <hr height="1" noshade id="quote"></blockquote id="quote">

]]>