How to properly create an index

Question

I have the following very large table in SQL Server 2005:

create table Blah 
(
  FirstName varchar(30),
  Rank int,
  Position int,
  ...
)

I will run the following query on it:

declare @PassedInFirstName varchar(30)
set @PassedInFirstName = 'SomeName'

select TOP 1 Position
from Blah
where FirstName = @PassedInFirstName
order by Rank DESC

I am setting up the following index on it:

CREATE INDEX IX_Blah ON Blah (FirstName, Rank)

Given that I order it by Rank DESC, should I change the index to order Rank in a descending way:

CREATE INDEX IX_Blah ON Blah (FirstName ASC, Rank DESC)

Or it does not matter?

Thanks.

Answer 1

Adding Rank as a descending value in your index is only a minor change. Sql Server can probably reverse the sorting used, or in this case, easily iterate to the last item in the list.

Is Position your primary key? An index is built of indexed column, the primary key, and optionally included columns. If Position is not your primary key then you're currently looking up your primary key in your index, then going through the result with a primary index seek to find the Position value. Try adding the Position value as an included column and you should be able to execute your query based on only one index, no other indexes will be used.

CREATE INDEX IX_Blah ON Blah (FirstName, Rank DESC) INCLUDE (Position)

Dont forget to check out your query plans, they can tell you if you lack any indexes (assuming Sql Server 2008), what indexes are used, etc.

Answer 2

If should benefit if the WHERE returns many rows.

I've seen results where logical IO was reduced by 50% by using DESC in the INDEX to match an ORDER BY

Also, change the query to covering:

SQL 2005 +:

CREATE INDEX IX_Blah ON Blah (FirstName, Rank DESC) INCLUDE (Position)

SQL 2000, SQL 7:

CREATE INDEX IX_Blah ON Blah (FirstName, Rank DESC, Position)

Answer 3

I messed up authentication on my previous post, this is my registered user.

An index is based on those columns you choose, and the primary key. You're basicly storing a hashmap, where the key (FirstName, Rank) resolves to your Id (assuming your primary key is Id). This Id is then used to read the Position value.

My proposition would be to include the Position value into the index as an included column. This would allow you to read the data from the index, avoiding the second lookup.