Compare performance difference of T-SQL Between and '<' '>' operator?

Question

I've tried searching through search engines,MSDN,etc. but can't anything. Sorry if this has been asked before. Is there any performance difference between using the T-SQL "Between" keyword or using comparison operators?

Answer 1

You can check this easily enough by checking the query plans in both situations. There is no difference of which I am aware. There is a logical difference though between BETWEEN and "<" and ">"... BETWEEN is inclusive. It's equivalent to "<=" and "=>".

Answer 2

The two operators that are being compared to one another here are fundamentally different and hence why the execution plans that are generated are also likely to be different (although not guaranteed).

The determining factor is the data distribution (selectivity) of the column that the comparison operators are applied to. This along with Statistics will dictate whether or not an index is or is not used etc.

Hope this makes sense.

Answer 3

actually. I just try verifying between with some of my data. BETWEEN is equivalent to ">=" and "<". For example: Between '05/01/2010' and '05/30/2010': You will only get data between 5/1/2010 00:00:00 to 5/29/2010 23:59:59. Try query your table with "Order by [TimeField] desc" and you will see the result.