1

I have search a lot about the real benefits of index scan over table scan in simple words but still confused. Let's say I have,

create table a(id int, id1 int, varchar(50) name)

If I select,

select * from a where id1=1;

It says Table Scan,

If I add an index to id column and run this query again,

select * from a where id1=1;

It says Index Scan

Update: I am not understanding what benefit I will get with ** where id1 =**, because index is on id. See these images,

enter image description here

and

enter image description here

Update: This who think this is a duplicate question. Please read the other question first. I am using where id1 = but the other question is using select *, without any where statement.

Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322

1 Answers1

5

An index scan can be faster because, presumably, the index doesn't cover the entire set of columns in the table, while a table (or clustered index) scan has to read all of the data. If an index does include all of the columns in the table, then it should be roughly equivalent to a table scan, and the choice between an index scan and table (or CIX) scan will be a coin toss. The difference is that when you have fewer columns in the index, you can fit more index rows on an 8kb page, leading to fewer overall pages you have to read in order to scan all of the data in the index.

To illustrate what I mean, imagine if you have two copies of the phone book, one with last name, first name, street address, and phone number, and one with just last name, first name, and phone number. Now imagine that because the street address doesn't have to be printed, you can fit two extra columns of names and phone numbers on any page in the phone book. The end result of this is that the phone book is thinner, because you can fit the same number of phone numbers on fewer pages. Next, imagine you are charged with counting the number of phone numbers in the book. Which would you choose, the one with the street address listed (which has more pages, analogous to a table scan) or the one without the street address (which has fewer pages, analogous to most index scans)? I would choose the one with fewer pages.

Another wrinkle in this is that some indexes can be filtered, meaning that not only do they have fewer columns in most cases (and therefore can fit more rows onto a single page), but they can also have a WHERE clause that eliminates a lot of rows. In this case, as well, an index scan will be better than a table scan (but this will only work for queries that have a matching WHERE clause and the same semantics).

In the updated question, it appears you are asking about the difference between a table scan and a clustered index scan. This can be vastly different from comparing a table scan and a non-clustered index scan. For the most part, a table scan and clustered index scan are about the same - in both cases you are reading all of the data in the table, just that in the former case the table is a heap. Depending on how much churn has happened in the table, a table scan can be worse over time - due to things like forwarded records. Plenty more details in this question:

What's the difference between a Table Scan and a Clustered Index Scan?

(Though the answers there don't seem to mention anything at all about forwarded records, one of the primary reasons I do not advocate heaps in any of our customers' environments. I would always want a clustered index on every table except for very specialized scenarios.)

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • if a lot or rows were eliminated by the `WHERE` woudn't the query engine use index seek? I'm assuming the use of `INCLUDE` is beyond the scope of the question. – Jodrell Dec 06 '13 at 14:57
  • 1
    @Jodrell no, because the query may still have to read the whole index. And even sometimes when you see a seek it is actually a range scan underneath - it uses the seek to find the start / end points and then performs a scan within that range. – Aaron Bertrand Dec 06 '13 at 14:59
  • @AaronBertrand, what do you think if my clustered index is on id and i am using where id1 = , Will I get any benefit from Index Scan? See question as an example. – Imran Qadir Baksh - Baloch Dec 06 '13 at 15:03
  • @AaronBertrand surely it depends on the `WHERE` and what the statistics suggest the best way to service the query will be. Anyway, the minutiae are not relevant to the answer. – Jodrell Dec 06 '13 at 15:06
  • @Jodrell my point is if the WHERE clause in the query matches the WHERE clause of the index, the query is returning all of the rows in the index. I'm not sure why you think that will end up with a seek, regardless of statistics say... – Aaron Bertrand Dec 06 '13 at 15:07
  • @user960567 Not sure, I still get a table scan in my tests given the data in the question. If SQL Server chooses an index scan in your case, then yes, it is probably because there is some benefit (such as reading fewer pages). But if you are using `SELECT *` ([read this](https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list)), the benefit often gets washed away because you have to perform lookups to get the rest of the columns that aren't covered by the index. – Aaron Bertrand Dec 06 '13 at 15:08
  • 2
    @user960567 the question hasn't really changed. There is not a huge difference between a *clustered* index scan and a table scan - you're still reading the entire table to satisfy the query. The difference is largely semantic - a table scan is what it is called when no clustered index exists. [You can learn more about the differences here](http://stackoverflow.com/questions/18764/), but if your question is actually about clustered index scan vs. table scan, rather than index scan vs. table scan, you should see the dupe or ask a different question. You shouldn't be changing it once accepted. – Aaron Bertrand Dec 06 '13 at 15:21