Sort works on one index and doesn't work on the other?

Question

Sort works on one index and doesn't work on the other?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
March 21, 2013 at 10:37 am

#272383

I have a query which does a order by at the end.
where WFID ='Something'
Order by Rkey asc, NFD desc
And my index is:
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFid] ASC,
[SId] ASC,
[NFTId] ASC,
[AId] ASC,
[RKey] asc,
[NFD] desc
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
With above index i still see a sort operator, even though the two columns are added to the index key columns.
With below index i don't see sort operator
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFId] ASC,
[RKey] asc,
[NFD] desc
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Any thoughts on why 2nd one works and 1st one doesn't?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sqldba_newbie (3/21/2013)
I have a query which does a order by at the end.
where WFID ='Something'
Order by Rkey asc, NFD desc
And my index is:
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFid] ASC,
[SId] ASC,
[NFTId] ASC,
[AId] ASC,
[RKey] asc,
[NFD] desc
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
With above index i still see a sort operator, even though the two columns are added to the index key columns.
With below index i don't see sort operator
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFId] ASC,
[RKey] asc,
[NFD] desc
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Any thoughts on why 2nd one works and 1st one doesn't?

Yes, the columns between WFId and RKey. With those columns, the values in RKey and NFD are not sorted as they are in the second index.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Lynn Pettis (3/21/2013)
sqldba_newbie (3/21/2013)
I have a query which does a order by at the end.
where WFID ='Something'
Order by Rkey asc, NFD desc
And my index is:
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFid] ASC,
[SId] ASC,
[NFTId] ASC,
[AId] ASC,
[RKey] asc,
[NFD] desc
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
With above index i still see a sort operator, even though the two columns are added to the index key columns.
With below index i don't see sort operator
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[WrkFS]
(
[WFId] ASC,
[RKey] asc,
[NFD] desc
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Any thoughts on why 2nd one works and 1st one doesn't?
Yes, the columns between WFId and RKey. With those columns, the values in RKey and NFD are not sorted as they are in the second index.

hmmm...so in that case i need specific indices ONLY for those two columns on which sort is done?

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

DEpends whether the sort's expensive enough to justify a duplicate index.

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