Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Using * in SELECT Statement

    Hello,
    Is there any major difference (performance wise or any other) between writing all the column names of a table / view in select statement than just writing SELECT * FROM TABLENAME?


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you run some tests and see?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do not use SELECT * in production code. Think about it this way. If you have a couple of dozen fields in one table and you are interested in only the values in 2 integers fields of lets say a few hundred thousand records, do think it is going to require more or less bandwidth to pull just the 2 fields over the network or the whole thing for your result set?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DBA_Rahul
    Is there any major difference (performance wise or any other) between writing all the column names of a table / view in select statement than just writing SELECT * FROM TABLENAME?
    performance: no difference, assuming you actually write every column

    when you change the table/view by adding or deleting a column, then if you used "select star" you won't have to change the query

    however, this is usually a poor strategy for other reasons, such as the fact that you usually have to change the application to accommodate the change in the table

    plus, if your query involves more than one table/view, you definitely want to avoid using "select star"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    never use select *, except when you should

    http://weblogs.sqlteam.com/brettk/ar...4/22/1272.aspx
    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
  •