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?