How to I force a better execution plan when the database is forcing a join?

Question

I am optimizing a query on SQL Server 2005. I have a simple query against mytable that has about 2 million rows:

SELECT id, num
FROM mytable
WHERE t_id = 587

The id field is the Primary Key (clustered index) and there exists a non-clustered index on the t_id field.

The query plan for the above query is including both a Clustered Index Seek and an Index Seek, then it's executing a Nested Loop (Inner Join) to combine the results. The STATISTICS IO is showing 3325 page reads.

If I change the query to just the following, the server is only executing 6 Page Reads and only a single Index Seek with no join:

SELECT id
FROM mytable
WHERE t_id = 587

I have tried adding an index on the num column, and an index on both num and tid. Neither index was selected by the server.

I'm looking to reduce the number of page reads but still retrieve the id and num columns.

Answer 1

The following index should be optimal:

CREATE INDEX idx ON MyTable (t_id)
INCLUDE (num)

I cannot remember if INCLUDEd columns were valid syntax in 2005, you may have to use:

CREATE INDEX idx ON MyTable (t_id, num)

The [id] column will be included in the index as it is the clustered key.