Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Reason to not use SELECT *

    Or when to use it...

    http://weblogs.sqlteam.com/brettk/ar...4/22/1272.aspx

    Any comment appreciated...I'll add to the list as well

    Thanks
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Here 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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see Why "select star" is bad (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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks Rudy...

    Nice pix....

    http://searchdatabase.techtarget.com...285649,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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Absolutely. SELECT * is just sloppy coding, and for that matter so is referencing columns without including the table name.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2004
    Posts
    75
    Ok I am kind of lost... what should I use instead of the SELECT statement?

    breeze76

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2004
    Posts
    75
    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

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    absolutely

    but that my own opinion (hence, the MOO)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2004
    Posts
    75
    OK.. say I want to get info from Three(3) tables in one database, how would I do that?

    breeze76

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •