none
difference between select * and select column name

    Question

  • can anyone explain the difference between

    select * from tblname

    and select col1,col2,col3 ........<all columns> from tblName

    which one is faster and why


    Regards, Amitesh Srivastva
    Thursday, December 30, 2010 1:14 PM

Answers

All replies

  • Hello,

    One point is a SELECT * (nearly) never could use a covering index, see SQL Server Optimization => Covering Index.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, December 30, 2010 1:38 PM
  • I need to know the reason also.

     

    But since I learned, I don't use SELECT * at all. That's my practice. Knowing the reason behind it will definitely help to understand.

    The reason I use SELECT COLUMN_NAMES is when using Stored Procedure, adding columns to the table will not screw your application.

    select * will give additional column (which you've just added to the table) and application will get additional column and may raise error.

     

    Thursday, December 30, 2010 3:02 PM
  • From a perf perspective, then are the same (if you list all columns). However, from a stability perspective, listing column names is safer (adding columns to your table doesn't break applications). I never use SELECT * in production code.
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, December 30, 2010 4:33 PM
    Moderator
  • The main reasons are:

    * Performance might suffer

    * Table changes can lead to unexpected behavior and errors

    * You might not be getting what you expect

    You can read all about it at http://www.xs4all.nl/~gertjans/sql/dont-use-select-star-in-production-code.html


    Gert-Jan
    • Proposed as answer by Naomi NModerator Thursday, December 30, 2010 6:56 PM
    • Marked as answer by KJian_ Wednesday, January 05, 2011 7:49 AM
    Thursday, December 30, 2010 6:54 PM