SQL Server 2008 seems to be picking the PK Index for every query, even if a better one seems to exist

It sounds like a similar situation to what's asked here, but I'm not sure his details are the same as mine.

Basically I have a relational table, we'll call it User:

User
-----------
int Id
varchar<100> Name
int AddressId
varchar<max> Description

and it has the following indices: PK_User_Id - Obviously the primary key. IX_User_AddressId - which includes only 开发者_JAVA技巧the AddressId.

When I run the following query:

select Id, Name, AddressId, Description from User where AddressId > 200

The execution plan shows that a scan was done, and PK_User_Id was used.

If I run this query:

select AddressId from User where AddressId > 200

The execution plan shows that a scan was done and IX_User_AddressId was used.

if I include all of the columns in the IX_User_AddressId index, then my original query will use the proper index, but that still seems wrong that I'd have to do that.

So my SQL noob question is this: What in the world do I have to do to get my queries to use the fastest index? Be very specific because I must be retarded as I can't figure this out.

You query looks like it has tipped, since your index does not cover all the fields you wanted, I would say it tipped (check out Kimberly Tripp - Tipping Point) and has used the Primary Key index which I would take a pretty good guess as being your clustered index.

When your IX_User_AddressId index contains only the AddressId, SQL must perform bookmark lookups on the base table to retrieve your other columns (Id, Name, Description). If the table is small enough, SQL may decide it is more efficient to scan the entire table rather than using an alternate index in combination with bookmark lookups. When you add those other columns to your index, you create what is called a covering index, meaning that all of the columns necessary to satisfy your query are available in the index itself. This is a good thing as it will eliminate the bookmark lookups.