best way to index for retrieving data using ordered by

Question

I am looking to do an index seek, keep in mind i am a newbie in indexing, is it possible to achieve an index seek if I were to run the folling using adventure works.

 select BusinessEntityID, FirstName, MiddleName, LastName, ModifiedDate
 from dbo.person
 order by ModifiedDate

I have created the following non-clustered index:

create index IX_ModifiedOn  on [dbo].[person] (ModifiedDate Asc)

I don't seem to be getting an index seek looking at the query execution plan. I just want to know the besy way to created an index for ordering on ModifiedDate?

Also there is a Clustered Index on the BusinessEntityID as its the primary key

Thank you

SQL Server will use the index if appropriate. If the data is too small, the optimizer may decide that actually doing a sort is faster than using the index. — Gordon Linoff
– Gordon Linoff, Commented Mar 4, 2017 at 16:44
Hi Gordan, thank you i am actually trying this on a table at work with 27million rows. I am just using adventure to simulate the same principal. But is possible yo achieve a index seek this way on the modifiedDate? — abs786123
– abs786123, Commented Mar 4, 2017 at 16:47
you are selecting 27 million rows in one query with no where or join? — SqlZim
– SqlZim, Commented Mar 4, 2017 at 16:48
Thats correct but at a work enviroment but using adventure work to simulate the example. Its for loading a complete table but in order of CreatedOn at work for creating a staging table in a Data Ware Housing scenario for the base table — abs786123
– abs786123, Commented Mar 4, 2017 at 16:50

SqlZim · Accepted Answer · 2017-03-04 16:52:59Z

1

Since you are selecting the whole table without any filter or joins, you will not get an index seek unless the index also covers your query. Otherwise the engine would have to go back to the table to get the columns you are selecting anyway. This would be a covering index for your example.

create index IX_ModifiedOn  on [dbo].[person] (ModifiedDate Asc)
  include (BusinessEntityID, FirstName, MiddleName, LastName);

answered Mar 4, 2017 at 16:52

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

abs786123 Over a year ago

Cheers for that, I still get a cost of 90% on the sort. Is there anyway to reduce that? I just want to select the entire table but by reducing the cost of ordering

SqlZim Over a year ago

@abs786123 Then your example might not be accurately describing your actual situation. This does not produce a sort operation on Adventureworks, here is the execution plan after creating that index: brentozar.com/pastetheplan/?id=H10-0jY9x

Collectives™ on Stack Overflow

best way to index for retrieving data using ordered by

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related