I've always been told that the inclusive comparison is better than the exclusive, but it's never been explained why.
Is it purely from a performance perspective or is there another reason why >= 1 is better than > 0 ?
Why does SQL Server prefer >= 1 over > 0?
Viewed 123
1 Answers
This is a complete myth.
Let me explain why:
When you seek an index with an inequality, you get a range seek. In other words, the server seeks to the start point, and moves forward to the end point (if any).
We assume that there are no rows between
0and1, say0.5, otherwise the resultset is not the same.
So when you use WHERE IndexColumn >= 1, the server seeks down the B-Tree to the first row with 1. And when you use WHERE IndexColumn > 0, the server seeks to after the end point of all 0 rows.
If the next row after 0 is a 1 then they are seeking to the same place, ergo the performance will be exactly the same.
Statistics-wise, they will also get similar estimates, because the calculation ends up being almost exactly the same.
If there is no index then you are doing a full scan, in which case you are dependent on the CPU taking the same time for > as for >=, which I believe is true for all modern processors.
As a rule of thumb, treat all rules-of-thumb you hear about SQL with a big pinch of salt. SQL performance is far more dependent on things like good indexing and sarge-ability.
Related