May
17
Written by:
Jeff Smith
5/17/2010
What’s the first thing we learn when it comes to databases and/or SQL? Odds are it was how to write a SQL statement to retrieve data from one or more tables.
Image courtesy of Bart Simpson Chalkboard Generator
The merits and detriments of using the SELECT * command have been, and will continue to be, debated all over the Internet. Here are a few enlightening and entertaining takes:
Yes, these are all SQL Server folks; please don’t hold it against them. To be honest though, this topic concerns ALL database professionals.
I don’t want to re-open this can of warms as others have waxed philosophical much more eloquently than I. However, most schools of thought come down to the following Pros & Cons:
PROS
- Easy
- Gets me all columns w/o hard-coding column names
CONS
- Easy
- You ask for everything, but only need a subset
- Expensive
- If the column order, column names, or column definitions change, your query (and Application!) is bound to break
Toad Can Help!
You are going to write SELECT * queries no matter what I or anyone else tells you. However, if you want, you can get help. Let’s investigate some methods in Toad:
Code Insight
If you want all of the columns for one or more tables, Toad can get you these very quickly, and with no typing. You can optionally use F4 DESC popups and the Object Palette to drag-and-drop column names into your queries.
Schema Browser
While in the Browser, you can quickly ask Toad to build your SELECT statement for you.
Mouse-click, Voila!
By the way, Toad does drink it’s own Kool-Aid! When you click on the Data Tab for a table or view in the Schema Browser, it explicitly lists the columns to be displayed.
A Parting Gift for our SQL Server Friends
Toad for SQL Server (as well as Toad for Data Analysts) will support you regardless of your take on the SELECT * debate.
The Editor has the same IntelliSense features that Toad for Oracle offers – and oh by the way, we support this feature while connected to SQL 2000 and 2005 in addition to 2008. Amazing that some IDE’s only offer this feature if you are using SQL Server 2008…but that’s another blog!
If you decide to use our Query Builder, you have your choice in going for all columns in an object:
One last thing, don’t chuckle at the Query Builder. When surveying about 800 customers a few years ago, we found this to be the most popular feature in Toad when it comes to productivity. Apparently not everyone enjoys manually typing out JOINS as much as you do.
And of course, to come back full circle, when getting started with SQL or databases, someone will want help in building their queries. And if they stumble upon the Query Builder, thankfully we do list the ‘Add All Columns’ selection first!
P.S. Twitter Is Not Just About Telling People What You Had for Lunch
I came across this SELECT * debate there just the other day and learned quite a bit from my fellow database professionals. I generally follow more SQL Server folks as that’s where I need the most help…if you’re curious but too afraid to try it out, I heartily recommend this tutorial.