I'm confused by using Clustered and non-clustered Index in my db

Question

I hope you could share your time to help me on this.

Currently, I'm using 3 tables to compare performance in getting data. These 3 tables have the same columns (LocInvID, ActivityDate,ItemID,StoreID,CustomerID), same data (around 13 million records):

Table LocInv1: Using Clustered Index at LocInvID (it's primary key too). Using Partition Table for ActivityDate. And 3 columns (ItemID, StoreID, CustomerID) are Non-Clustered Index.
Table LocInv2: Using Clustered Index at LocInvID (it's primary key too). Not using Partition Table.
Table LocInv3: Using Clustered Index at LocInvID (it's primary key too). And 3 columns (ItemID, StoreID, CustomerID) are Non-Clustered Index. Not using Partition Table.

CREATE NONCLUSTERED INDEX [IX_LocInv3] ON [LocInv3] ( [ItemID] ASC ,[StoreID] ASC, [CustomerID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

And when I run this query string (in 3 tables)

select ActivityDate,ItemID,StoreID,CustomerID from LocInv1 WITH (INDEX(IX_LocInv))  where ItemID=43 
select ActivityDate,ItemID,StoreID,CustomerID from LocInv2 where ItemID=43 
select ActivityDate,ItemID,StoreID,CustomerID from LocInv3 where ItemID=43

the result is quite weird:

Table LocInv1 got slowest. Is it possible? my query string is incorrect?
Table LocInv3 uses Non-Clustered Index, but at Actual Execution Plan, it is Clustered Index Scan. I don't understand, I query with ItemID, why it is Clustered Index Scan?
Table LocInv2 uses only Clustered Index for LocInvID, but it got fastest result. Is it correct?

Please advise.

Thanks.

I query with ItemID, why it is Clustered Index Scan? that's because the query optimizer made a decision that scanning the whole clustered index was faster / less expensive than doing an Index Seek and lookup. Try creating a non-clustered index on ItemID that includes the ActivityDate, StoreID, CustomerID columns - in that case, this covering nonclustered index will most likely be used — marc_s

Higarian Higarian · Accepted Answer · 2012-08-21T12:53:38

the query optimizer choses the fast way he can find, not only depending on the indexes but in the data they contain too.

A search by a clustered index is usually faster, but in some cases it is faster to do the way around, you can test it removing and putting the index again.

And not to mention, depending on the operations that the table sufered (inserts, updates, deletes) and late index insertions will afect search too.

these changes will change how the indexs are stored, depending on the size of the thing you might have indexes with several pages.

if you can post one insert script of the data inside these table i can look better. if you only did the query analyser test (ctrl+l) it should not be 100% acurate

I'm confused by using Clustered and non-clustered Index in my db

1 Answers