Comments by msmits - CodeProject

msmits 2-Aug-11 8:26am View

Deleted

This article is right but doesn't explain why... There are a few reasons, the main one being that you want your queries to be predictable:
- If a column is added, (poorly written) client code that uses column indexes may fail.
- If a column is added with a lot of data (say big documents) this may create a big performance impact.
- SQL may use different indexes (or no index at all) if it needs all columns since it may decide that e.g. an index seek + bookmark lookup is more work then doing a table scan.

Exceptions (since they return a predictable number of columns and are optimized properly by SQL):
- Using * in subqueries (such as SELECT A FROM (SELECT * FROM T)).
- Using * from a subquery (such as SELECT * FROM (SELECT A FROM T)).

Cheers,
Michel

msmits 2-Aug-11 2:06am View

Deleted

Hey Wizardzz,
Your consultant was right. See my other comment/alternative.

msmits 2-Aug-11 2:02am View

Deleted

That is not true.
The optimizer takes into account the cost of using the index and then finding the record (if the index is not covering). This estimation greatly depends on the distribution of the data (statistics).
Just because you have an index with all fields in the WHERE clause does not mean it will be used. In fact, using an index and then retrieving the other columns may degrade performance.

msmits 10-Jan-11 8:08am View

Deleted

Reason for my vote of 1
Not a good advice

Comments by msmits (Top 4 by date)