[quoted text, click to view]

It's because you're using SELECT *. You're telling the query to return
every field for the matching rows. Let's look at a different variation
of your query:

SELECT status_id, someothercolumn
FROM table
WHERE status_id = 161

If you look at the execution plan for this query, you'll see that it
(probably) does an index seek using your nonclustered index, followed by
a bookmark lookup. It uses the index to locate records matching the
status_id filter, but because the index doesn't include the
"someothercolumn" column, it has to go to the table (using a bookmark
lookup) to get that value.

Now, back to your example:

SELECT *
FROM table
WHERE status_id = 161

You've just asked it to return EVERY column for every row matching your
filter condition. Because the index doesn't include EVERY column, a
large bookmark lookup will have to be done to get all of those values.
The query engine has determined that it is "cheaper" to just scan the
table and grab everything in one pass, rather than use the index, then
make a seperate trip to the table to get the other values.

With a clustered index, the index *IS* the table, so there's no need to
make a return trip to do a bookmark lookup.

Does that help?


--
Tracy McKibben
MCDBA