SQL Server 'In' Statement Item Order for Performance
performance, sql, sql-server, sql-server-2005, t-sql
Solution
If you have an index on `SomeNumberField`, then this statement will be either just split into two range scans on the index, or processed as a `TABLE SCAN / CLUSTERED INDEX SCAN` with a filter.
The latter is more probable, provided that the majority of your rows have `SomeNumberField = 999999`
The range scans will always be performed in the index order, regardless of the constants order in the `IN` predicate.
Filter comparison time is negligible compared to the time required to fetch the data pages.
Problem
Given the SQL Statement: ``` SELECT * FROM MY_TABLE WHERE SomeNumberField in (0,99999) ``` If I can guarantee that the majority of rows in `MY_TABLE` have `SomeNumberField` set to 99999, and can project that this will remain the case indefinately, is it better to write the above query like this: ``` SELECT * FROM MY_TABLE WHERE SomeNumberField in (99999,0) ```