I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table.

When I run the following:

WHERE   IR_ID IN ( 100, 101, 102 )

I get the following execution plan:

enter image description here

It states Table Scan (HEAP). Not quite sure what it means in the context of a partitioned table. I also don't see that it uses any kind of index. And yet, it must, because the query comes back fairly fast (e.g. the table has 6 million rows).

So, my questions are:

  1. What does Table Scan (HEAP) mean for a partitioned table?
  2. Does it indeed use an index, perhaps behind the scenes?
  3. Is there anything I need to do to improve efficiency?
  • As IR_ID is the partitioning column it can just look at the three partitions of interest. Do any of the partitions contain more than one IR_ID value? Commented Jul 10, 2012 at 22:17
  • @MartinSmith No, IR_ID is unique in partition. Commented Jul 10, 2012 at 22:29
  • No point in having the NCI on IR_ID then AFAIK. It can already tell which partitions contain a particular value from the partitioning metadata and within each partition all rows in the index will have the same value. Commented Jul 10, 2012 at 22:32

1 Answer 1


The query did do partition elimination.

If you look at the tooltip, the Actual Partition Count says 3, which means it touched 3 partitions. (The Seek Predicate section also confirm this.)

The reason why it appears as a table scan is because it's a table scan with partition elimination. I'm not sure how else to explain that.

No indexes were used for this. Table partitioning is implemented deeper down in the database internal structures than an index, so partition elimination is very efficient by nature (given a suitable predicate, which you provided).

That said, the predicate you've given is suitable only for partition elimination, and (very likely) not much else because it's not very selective. If all the rows from those partitions are required, this is already close to an optimal solution -- creating a useful clustered index is usually recommended.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.