locked
Scan RRS feed

  • Question

  •  

    I write a work about database, and I want to write the diffrence between Table scan and Index scan. When is the index scan effectient? Use the database a Table scan when there is an index on the table or  always a index scan when the index is nonselective? Can be a Table Scan effecient than a index scan?

    Monday, October 8, 2007 8:31 AM

Answers

  • The basic answer is - it depends.  If you have a clustered index, then if you are doing an index scan on the clustered index, it is the same thing as scanning the table.  Beyond that, the mechanics are really not that different.  A table scan starts at the first page in the table and walks the table, one sequential page at a time until it finds the answer to your query or hits the last row of the table.  An index scan starts at the first page in the index and walks the index, one sequential page at a time until it finds the answer to your query or hits the bottom of the index.  One specific difference is that with a table scan, when it is done, you have your answer.  With an index scan, if the data in the index cannot completely satisfy your query, then it has to hop to the table and pull the remaining rows out of the table.

     

    I'd suggest picking up Inside SQL Server 2005: The Storage Engine.  There are several hundred pages in that book dealing with the storage architectures, indexes, and the differences between the various types of search algorithms employed by the optimizer.  (You'll also find some additional information to correlate to in the other three volumes of the Inside SQL Server 2005 series.)

     

    Monday, October 8, 2007 10:49 AM