October 16, 2009 at 7:30 am
We had a nice dicussion today that I thought you guys would be interested in. Somebody made the statement that the ability to select 'all columns' from an object (e.g. SELECT * FROM OBJECT) should be removed from T-SQL to force programmers and DBA's to always use column names.
I can think up of some obvious advantages:
- The programmer is forced to think about which columns he will use, and not arbitrarily select 20 columns, only to use 3 or 4 (I've seen this happen in every SQL environment I have seen to date), in addition this could mean obvious performance improvements.
- It would fix the issue where certain queries would break if extra columns were added to the table.
Some obvious disadvantages would be:
- Users would be unable to quickly check what's there in each field by using a 'SELECT TOP 100 * FROM OBJECT' query.
- Users would be unable to use dynamic OBJECTS (e.g. views or SP's that return different column names each time you query it).
In terms of both disadvantages, I'd say that point 1 is unvalid because you should never 'quickly do A or B' inside SQL server. And for point 2 I'd say that that's bad design to start with (I can see very little real life applications for dynamic columns that could not be handled on a row level).
Because we didn't get anywhere near a concensus in this discussion I was wondering what people here think about the statement? I was hoping it would spark a nice bit of discussion. 🙂
October 16, 2009 at 7:42 am
I dont mind having a select * being done from SSMS but not from application. Getting unwanted data that you are not going to use in your app by doing a Select * is not wise. More IO, Network and load on the application server.
-Roy
October 16, 2009 at 7:58 am
I'm a fan of SELECT * after EXISTS...:-) though I must admit that it is frustrating that the syntax is invalid when the object is schema-bound.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 17, 2009 at 6:34 pm
I believe a little education and threats of high velocity pork chops would be the easiest and best thing to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2009 at 12:49 pm
From a purely DBA point of view where you are going to be querying system tables, DMVs etc it would be very annoying when troubleshooting under pressure not to be able to use select * and have to remember column names and type a load of them out.
And what if you want to create a copy of a table (all columns) using select * into...from?
Not things to do in an application but there are other times T_SQL is used. 🙂
So there is a place for select *.
---------------------------------------------------------------------
October 19, 2009 at 2:03 am
bas de zwart (10/16/2009)
Some obvious disadvantages would be:- Users would be unable to quickly check what's there in each field by using a 'SELECT TOP 100 * FROM OBJECT' query.
- Users would be unable to use dynamic OBJECTS (e.g. views or SP's that return different column names each time you query it).
In terms of both disadvantages, I'd say that point 1 is unvalid because you should never 'quickly do A or B' inside SQL server.
Why not? The first thing I do when writing a query against a table I'm not familiar with is
SELECT TOP(10) * FROM <TableName>
That way I can see what the data values look like and what the column names are. Same thing when querying DMVs, unless it's one of the small number that I work with very often.
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
October 19, 2009 at 7:14 am
george sibbald-364359 (10/18/2009)
From a purely DBA point of view where you are going to be querying system tables, DMVs etc it would be very annoying when troubleshooting under pressure not to be able to use select * and have to remember column names and type a load of them out.And what if you want to create a copy of a table (all columns) using select * into...from?
Not things to do in an application but there are other times T_SQL is used. 🙂So there is a place for select *.
[shameless plug and no I am not affiliated with redgate in any way]
Then you should be using SQLPrompt. Literally four keystrokes and your object name. And if you need to see columns, just mouseover the object name in the code and you get both columns/datatypes and a script to recreate the object.
Time to go bug my boss about how that requisition request is going...:-D
[/shameless plug and no I am not affiliated with redgate in any way]
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 19, 2009 at 7:39 am
yep, totally shameless. 😀
How much do I have to get my boss to spend to save me typing sp_help or expand object explorer?
(p.s I don't spend all day cutting code and the people who do work for an outsource company, let them pay for it.)
---------------------------------------------------------------------
October 19, 2009 at 7:45 am
What happened to "right click in SSMS > script to 'select'"??
Works like a charm, I still haven't seen any technical reasons for this option to be here besides being a lazy DBA. 🙂
I have seen several technical reasons to disable this feature though..
October 19, 2009 at 8:37 am
bas de zwart (10/19/2009)
What happened to "right click in SSMS > script to 'select'"??Works like a charm, I still haven't seen any technical reasons for this option to be here besides being a lazy DBA. 🙂
I have seen several technical reasons to disable this feature though..
I can type "select top 100 * from dbo.MyTable" faster than you can mouse though that and then add in the top command. Guaranteed. (I type over 100 words per minute.)
I also use that to explore a table or other data source while I work my way through the logic of a query.
A query might start as:
select *
from dbo.MyTable;
Then:
select *
from dbo.MyTable
left outer join dbo.MyOtherTable
on MyTable.Col2 = MyOtherTable.Col1;
Then:
select *,
(select ColA
from dbo.MyThirdTable
where ID = MyOtherTable.Col3) as MyOtherTableCol3
from dbo.MyTable
left outer join dbo.MyOtherTable
on MyTable.Col2 = MyOtherTable.Col1
Then, finally:
select MyTable.Col1,
coalesce(
(select ColA
from dbo.MyThirdTable
where ID = MyOtherTable.Col3),
MyOtherTable.Col2,
MyTable.Col4,
getdate()) as DateCol
from dbo.MyTable
left outer join dbo.MyOtherTable
on MyTable.Col2 = MyOtherTable.Col1
Each iteration adds more to the solution. In each one, I can see exactly what rows I'm returning and why. I can see if the row number changes when I add a join. If it's an inner join, I can expect less or more rows, and see if it worked as expected. And so on. But in each case, being able to see all columns helps me make sure the query is going the right direction on things like join criteria and Where clauses.
Finally, when I've got all the tables, CTEs, etc., built, I limit the number of columns down to exactly what I need. Then I add in any aggregate functions, a Group By clause for those, and any Having clause, if any of those are needed.
It's an efficient way to build queries, and I find it makes for higher quality code faster. It's especially crucial if I'm not already comfortably familiar with the tables/views/etc. that I'm querying.
Edit: Fixed a typo and clarified one sentence.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 19, 2009 at 7:28 pm
I do it the same way... and every step is tested that way as Gail and Gus. The side benefit is that every step is tested.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2009 at 2:53 am
I think it's a matter of educating developers in proper database design and manipulation. Yes, removing the option for SELECT * would force them to do that one thing properly, but it wouldn't force them to design a decent database or properly index things, which I imagine would have a far greater impact on performance.
October 20, 2009 at 3:05 am
I agree, I often build it up that way..
I just think the whole idea, is well, stupid, sure it isn't a best practice to do so, but if we go down that road what else are we going to take out in the name of best practices..
CEWII
October 20, 2009 at 3:32 am
Thanks everyone for your insights. I have to be honest and have been playing a bit of the devils' advocate. I often use the (top x *) statement too, especially when working for new clients in new environments or when working with linked servers when it's harder to find the column names.
I find this kind of discussion interesting as it is a method for me in trying to find the exact point where a mandatory process is replaced by best practices. Often there are grey areas of which this is just one of many.
October 20, 2009 at 3:39 am
There are lots of ways to accomplish things, but usually only a few good ways. Using it in code is a bad practice but not having it would be worse.. You have to know the schema of every object? This makes it largely self-discovering..
CEWII
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply