Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

index is not working while using ROW_NUMBER Expand / Collapse
Author
Message
Posted Sunday, February 24, 2013 10:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
thanks for this valuable advice.
Post #1423423
Posted Sunday, February 24, 2013 9:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
If I use any other column which is in NCI (& not PK), then this is a seek.
NCI is same in both situations. Difference is only is Row_Number order by column. Please check the attached two execution plans. One is Row_Number with PK & second one is Row_Number with any other column.

one shows SCAN & another one shows SEEK. Difference is only in Row_Number order by column.


  Post Attachments 
RowNum_with_PK.png (1 view, 36.39 KB)
RowNum_with_NCI_Column.png (0 views, 39.02 KB)
Post #1423468
Posted Monday, February 25, 2013 1:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Pictures of the plan are pretty useless.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1423495
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse