SQL Server "B" Statement for Order Position for Performance

Question

Given the SQL statement:

SELECT * FROM MY_TABLE WHERE SomeNumberField in (0,99999) 

If I can guarantee that most of the rows in MY_TABLE have SomeNumberField equal to 99999 and can project that this will remain undefined in this case, is it better to write the above query as follows:

 SELECT * FROM MY_TABLE WHERE SomeNumberField in (99999,0) 

Answer 1

If you have an index in SomeNumberField , then this operator will either be simply split into two range scans by index, or processed as a TABLE SCAN / CLUSTERED INDEX SCAN with a filter.

The latter is more likely if most of your lines have SomeNumberField = 999999

Range scanning will always be performed in index order, regardless of the order of constants in the IN predicate.

Filter comparison time is negligible compared to the time required to obtain data pages.

Answer 2

Such optimization can be performed automatically by the SQL optimizer if you collect some statistics that are used by the optimizer. You can use the tools provided by the database provider for this task. For SQL Server, see the following article on MSDN: http://msdn.microsoft.com/en-us/library/cc966419.aspx