SQL Server Performance

Why a table scan even when an index is present?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by EMoscosoCam, Nov 30, 2010.

  1. EMoscosoCam Member

    Hello
    I have a table with 13 columns, where column13 is of type bit and has an index (actually, the sole index in this table). I have a simple SELECT statement explicitly showing the other 12 columns, WHERE column13 = 0. When looking at the query plan, one can see that a Table Scan is performed instead of a Index Seek. Any ideas why?
    Thanks a lot.
  2. Luis Martin Moderator

    How many rows that table have?
  3. Adriaan New Member

    Simply put, you're asking the server to return data from a number of columns. If all columns were covered by indexes then the data could be read from the indexes. Since they are not, the table must be read - which is what a table scan is.
    Seems a bit odd if the only index on your table (remember that a primary key is implemented as an index) is on a bit column.
  4. EMoscosoCam Member

    Thanks.
    The table has at moment only 400 rows. It is an auxiliary table that I have created as bridge when integrating 2 systems. I have just created a Primary Key and the Table Scan has been replaced for a Clustered Index Scan.
    I would have presumed that having an index for the column being mentioned in the WHERE clause would have been just enough.
  5. Adriaan New Member

    If you were only returning values from columns covered by the index, then yes. But you're returning values from other columns too.
  6. FrankKalis Moderator

    Also, an index alone on a bit column might not be terribly useful since there can only every be 2 or 3 possible values for such a column and in many cases it is simply faster to scan the table than to use the index and from there look up the rest of data from the table.
  7. Luis Martin Moderator

    [quote user="FrankKalis"]
    Also, an index alone on a bit column might not be terribly useful since there can only every be 2 or 3 possible values for such a column and in many cases it is simply faster to scan the table than to use the index and from there look up the rest of data from the table.
    [/quote]
    And with 400 rows I'll bet for scan.
  8. EMoscosoCam Member

    Thanks, I appreciate the replies. I placed an Index on the bit column being solely mentioned in the WHERE clause because I believed that in that way the Query Engine would separate faster those rows with value 0 from value 1. Obviously I was mistaken.
    I am glad of learning more by using this forums.
    Thanks a lot.

Share This Page