Coming Events
SSWUG.org provides premier opportunities for you to learn techniques helping you improve your craft. One great way to keep aware of these opportunities is to go to www.sswug.org and scroll down to Upcoming Events. If you don’t see one your interested in, click on More Events to see even more opportunities.
SELECT * Feedback
Today we have some comments about the use of the SQL Syntax, SELECT *. Before we dig into them, here is another tip you may want to consider.
SELECT COUNT(*) FROM SomeTable is a common practice for doing an aggregate query simply wanting a record count. You may even have other columns listed and include a GROUP BY clause. One tip an Oracle DBA friend of mine shared with me years ago was that it can be beneficial to simply use a scalar value in place of *.
Instead you would use SELECT COUNT(1) FROM SomeTable.
The theory behind this is that when you use SELECT * against a table, the query analyzer must resolve * into a list of columns based on the current schema. So, using SELECT COUNT(1) instead of SELECT COUNT(*) the query engine doesn’t have any overhead resolving * when it is not even needed.
Frankly, I don’t know if there is any performance gain in this scenario anymore…but I still use it out of habit. You never know what you may have as you move to other SQL engines.
Here’s some reader feedback…
Tom Writes:
I have read your column for quite a while and enjoy it. It helps pinpoint areas of interest. As I was reading today’s newsletter condemning the SELECT * notation, I want to point out a good reason to use it. I agree whole heartedly that it is way over used causes extra and usually unnecessary performance hits to the server, but there is an instance it is very useful.
If an application needs to output a table or query results in another format such as HTML, XML, Excel, etc. Execute SELECT * against the table directly and you can ensure you are getting everything. I understand you take the order as presented from the server, but it ensures everything is there. I would hate to keep updating my code just to ensure that all the columns of a table or view are being included.
Editor:
I guess I wasn’t clear in my last editorial, or I’m being stubborn; probably both. Your example is a good example of why you would not want to count on using SELECT *. If you want to be sure you get everything from a view when you use SELECT *, then each time you modify an underlying table you have to be sure to update ALL of your views in cache or re-create them in order to be sure that the new columns will appear in your output.
This isn’t a process I would want to have to manage in a production environment. When code is released to production, I want to deploy that software, and it should do all the stuff it is supposed to do to work as expected.
One size does not fit all…I can see cases where you might want to use your technique…but those are going to be small shops where people are developing in the production databases without risks to a larger body, etc. In an enterprise environment with a rigorous release process, this probably isn’t a good plan.
Martin Writes:
You might already have mentioned it, but if, for performance reasons, you study the profiler output to have an idea what crazy statements SQL Server is asked to execute, and if you have the exact columns asked for, you can make useful conclusions about what indexes to create. If you see only "SELECT *" you can’t.
And in that same respect, the Index Tuning Wizard will be able to give much better advice if it knows what columns are truly needed.
Thanks for the timely comments. It’s always nice to have more than just my voice and perspective. You can send comments you may wish to share to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Spool Operators: Index spool and Window spool
In this article I will talk about the Non Clustered Index Spool. Then I will talk about Window Spools which are used by a number of window functions being introduced in Denali. In the end, I will talk about the common scenarios SQL Server query optimizer employs spool operators and how can we avoid them by simply keeping in mind a few things.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)
Featured Script
Rowcounts
returns rowcounts for all tables in the current database… (read more)