Reason to not use SELECT *

Or when to use it...
http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx
Any comment appreciated...I'll add to the list as well
ThanksHere is one of my favorite examples...

create table test1
(col1 int)
go
create view testview1
as
select *
from test1
go
select *
from testview1
go
alter table test1 add col2 varchar(10)
go
select *
from testview1

Some folks depend too much on what they think SQL Server should do, rather than on what it does do.|||see Why "select star" is bad (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid576581_tax285649,00.html) (site registration may be required, but it's free)

* can't use GROUP BY unless you itemize every column in the table(s)

* can't write SELECT * MINUS col3 (select all columns except col3)

* can't take advantage of a covering index|||Thanks Rudy...

Nice pix...

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid576581_tax285649,00.html

but

That said, if you do actually need all columns, then SELECT * is fine, provided that you never change the table. Or the query.

Almost lends credibility to it's use...better it was left out...MOO

I like the group by argument and the select * minus-col3...mind if I add'em to the list?|||How about the argument that SELECT * makes debugging a bitch for the next poor sod who comes along and has to figure out what your code is doing? Or you, six months later, for that matter.

I always take the time to neatly enumerate the fields I am returning, and I am sure it has saved me time debugging in the long run.|||True...it's like it's self documenting..

That's why when I have a join...I use the labels on every column, even if the column names are unique...

don't have to guess next time...

Thanks|||Absolutely. SELECT * is just sloppy coding, and for that matter so is referencing columns without including the table name.|||Originally posted by blindman
Absolutely. SELECT * is just sloppy coding, and for that matter so is referencing columns without including the table name.

Nope labels are enough for me...

can't stanf the fully qualified names...hard to read...but then I have glasses

Drinking Glasses that is...|||Originally posted by Brett Kaiser
Nice pix...
yah, i keep meaning to get that replaced, i was hardly gray then

what do you think of the mushot on my home page http://rudy.ca/
mind if I add'em to the list? please do :cool:|||Ok I am kind of lost... what should I use instead of the SELECT statement?

breeze76|||No...you need the select

It's just an extremley bad and pervasive coding practice that people use

SELECT *

Except when doing analysis, or trying to figure things out, you should always code like

SELECT Col1, Col2, Col3, ect

Even when we offer advice and samples, a majority of the time we use col lists...I think just to hope that the advice take will be cut and paste and hopefully alter the way the developer works...

MOO|||Ok so, if I want to use:

SELECT user_name, password, email FROM USERS

I should do that instead of:

SELECT * FROM USERS

Correct?

breeze76|||absolutely

but that my own opinion (hence, the MOO)|||OK.. say I want to get info from Three(3) tables in one database, how would I do that?

breeze76|||USE Northwind
GO

SELECT o.OrderId, od.Quantity, od.UnitPrice, o.CustomerId, c.CompanyName
FROM Orders o
JOIN [Order Details] od
ON o.OrderId = od.OrderId
JOIN Customers c
ON o.CustomerID = c.CustomerId
WHERE c.CompanyName = 'Around the Horn'|||I kind of do not understand your example.. so here is a relational of my database...

Thanks again

breeze76|||I think I have a problem here.. I use access for my database... so I guess all this is moot then, eh?

breeze76|||sometimes when i feel naughty, i use select * when i create a dynamic table query.

select A.*
from (select statement) as A|||Originally posted by breeze76
I think I have a problem here.. I use access for my database... so I guess all this is moot then, eh?
no, of course not

whether to use

SELECT *

or the mucho splendido better version

SELECT col1, col2, ...

depends not on your database, because as far as that part of the syntax is concerned, all databases are the same

but you should take your relationship diagram and start a new thread in the access forum if you still have a question about something|||OK thanks.. but I understand what you are saying now... just declare your stuff each and not a global thing..

Thanks

breeze76|||Absolutely. SELECT * is just sloppy coding, and for that matter so is referencing columns without including the table name.

I really do believe this, for my developers have used indexing in the VB application. Upon each form-load and save; they used SELECT *.
It was disclosed when i added ROWGUID for my merge replication, my client/server application banged. :mad: All inserts failed, upon form-loads user were viewing ROWGUID in each last field.
That day i took immediate short-leave, for i would have either killed myself or blow the heads of those developers off. Now i expect any thing from my develpers here. I tried very much that they must get warning-letters ;)
I had to drop the column from each table and the developers changed each page and put column-names in the queries.|||Thanks for the real world example...

As for Access...

The example I gave should actually work with the sample Northwind database that Access is shipped with...just get rid of the USE Northwind and the GO's...

OPEN a new Query, (Well open Northind first), then switch to the SQL Window, and paste it...

it should run...you can then see what it transform it to in QBE...

I hate what Access does to stuff though...

And hey, doesn't anyone think the site looks really nice...just took a little while...(thank god it's not banking ot trades...)|||I kind of do not understand your example.. so here is a relational of my database...

Thanks again

breeze76

So...you can't apply what I gave you to your example?|||Well access is a little picky..

but it generated this

SELECT o.OrderID, od.UnitPrice, od.Quantity, o.CustomerID, c.CompanyName
FROM Customers AS c
INNER JOIN (Orders AS o
INNER JOIN [Order Details] AS od
ON o.OrderID = od.OrderID)
ON c.CustomerID = o.CustomerID;