August 31, 2011 at 1:15 pm
Keep in mind that an index will be highly efficient for your query if it can "cover" all columns you actually need to have (i.e. if the index definition contains all of those columns).
Every time you put a SELECT *, you force a non-clustered index to do what is called a bookmark lookup: find the clustered index key from the non-clustered index, and then a clustered index lookup to get the other columns. This is expensive, and tends to make the non-clustered index a lot less attractive as an option. In some cases, even though the non-clustered might cover the elements in your WHERE clause, the "bookmark lookup" makes using it so expensive that the query engine will just choose to skip it.
only pull the data you actually need at that time: you will tend to get much better shot at covering your query with an index.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 31, 2011 at 1:15 pm
tfifield (8/31/2011)
If you really need all columns returned, then by all means use SELECT *.
Even if every single one really is needed, they should still be explicitly named. Tomorrow some other developer comes along and adds another column of type varbinary(max) and suddenly, because you used select *, your query's returning several GB more data that you don't need and assorted things unexpectedly break.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2011 at 2:23 pm
GilaMonster (8/31/2011)
tfifield (8/31/2011)
If you really need all columns returned, then by all means use SELECT *.Even if every single one really is needed, they should still be explicitly named. Tomorrow some other developer comes along and adds another column of type varbinary(max) and suddenly, because you used select *, your query's returning several GB more data that you don't need and assorted things unexpectedly break.
Good point Gail. You never know what people are going to do down the line.
Todd Fifield
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply