Query with Execution Plan

Question

Post reply

Query with Execution Plan

koustav_1982

SSCommitted

Points: 1788
More actions
December 21, 2010 at 4:58 am

#232555

Hi
I am executing the following 2 querries on Adventureworks database:
1.SELECT *
FROM [Production].[ProductInventory]
ORDER BY [ProductID]
2.SELECT *
FROM [Production].[ProductInventory]
ORDER BY [LocationID]
The primary key for the table is a composite one defined on ProductID and LocationID.This also implies that the Clustered Index exists on these 2 columns,.
When i see the execution plan for these 2 queries, i notice
For the first case Index satisfies the query and no separate sort operation is executed. which is justifiable as the clustered index is at work.
For the second case an additional effort to sort the result is executed.WHY SO? LocationID is still a part of the clustered index , so why is the data being sorted again? What is the difference between LocationID and ProductID column which is inducing this Sort?
Using defualt schema as supplied with AdventureWorks.LocationID is smallint and ProductID is int

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

tertiusdp SSCommitted Points: 1569 More actions · Answer 1

The clustered indes spans both columns as you said. So the table will look like this :

SELECT top 5 [ProductID] , [LocationID] FROM [Production].[ProductInventory]

ProductID LocationID

11

16

150

21

26

Everything is first sorted by the productid column and then by the location column. So if you want to sort by location, SQL will need to apply a sort over the location column to remove the sort over the productid column.

Another example - a dictionary that is sorted by surname and then first name. If you wanted to get the phone book sorted by first name, you would need to re-sort it as the phone book is firstly sorted by surname.

koustav_1982 SSCommitted Points: 1788 More actions · Answer 2

Thanks for the reply. So what i understand is , if i alter the table such that the composite primary key is defined as "LocationID,ProductID" instead of "ProductID,LocationID" as it is now, the default index sorting will happen on LocationID and an extra sort is required for ProductID to order, correct?

tertiusdp SSCommitted Points: 1569 More actions · Answer 3

Yes,

That's correct.

Just remember that you will still need to supply the order by clause as otherwise you cannot guarantee that the results will be ordered. To change the key just to eliminate the extra sort operation is maybe a bit extreme, but if you only want to test a concept then all is good!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

-

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

spaghettidba

SSC Guru

Points: 105732

December 21, 2010 at 2:40 pm

#1265291

???

-- Gianluca Sartori

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

!!

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills