Search code examples
sqlsql-servert-sqlsql-server-2016

Why is index used in Update statement but not SELECT statement

I have a table with about 5 million records. I have a bitwise column called process_flag that has a non-clustered index on it. When I do the following SELECT query it takes about 6 seconds. As I would expect it doesn't use the index due to the complexity of the where clause:

SELECT * FROM mytable WHERE process_flag & 4 = 4

However, if I perform the UPDATE statement below it takes milliseconds and does use the index.

UPDATE mytable SET process_flag = process_flag &~ 4 WHERE process_flag & 4 = 4

Can anyone explain why the index would be used on the UPDATE statement but not on the SELECT statement?

Solution

  • Why the SELECT doesn't use the index

    The problem is that SELECT * requires obtaining all of the other columns that aren't covered by the index on the flag column. So, if more than a few rows are expected, and depending on the estimated size of the other columns, it is likely going to be cheaper for SQL Server to scan the clustered index instead of using the index and then performing a separate lookup for each individual row.

    Why the UPDATE does use the index

    To find the rows for the update, the other columns aren't relevant. SQL Server can efficiently scan (or potentially seek!) the narrow index to find the rows, then apply updates to the clustered index (and all non-clustered indexes) after those rows have been identified.

    What you can do about it

    1. As with any optimization, the first thing is to ask yourself if SELECT * is necessary.

    2. If SELECT * (or at least more than the columns currently in the index) is necessary, then maybe you can widen the index via INCLUDE.

    3. In general, it can be useful to provide SQL Server this additional (should be redundant) predicate, since it can allow a seek in some cases (I've written about this, long ago, here). This is unlikely to help if not in combination with 1. and/or 2..

       WHERE flag_column >= 4 AND flag_column & 4 = 4

    In the following example, you'll notice that SELECT * uses a clustered index scan, while just selecting a subset of columns uses a scan of the non-clustered index. When I add 3., that becomes a seek.

    The updates both use the non-clustered index, as described, with again a seek resulting from adding the redundant where clause.