none
Select * performance

    Question

  • I am being told that the use of "SELECT *" instead of explicitly listing ALL columns is a serious performance issue with SQL Server (2008).   Is this true?   We are using every column anyway and the only overhead I can see is a lookup to metadata which might be worth what - a few milliseconds?   The query would run once a day.
    Friday, January 29, 2010 3:29 AM

Answers

All replies

  • This is incorrect. Using SELECT * will not cause serious performance issues (SQL Server will take minimal time for column resolution). But it is a bad practice. Look at #8 in the following article for examples of why:
    http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/
    Plamen Ratchev
    • Marked as answer by beneuto Friday, January 29, 2010 3:59 AM
    Friday, January 29, 2010 3:32 AM
  • I don't think it's a serious performance hit, but the recommended good practice is to explicitly list all needed columns.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, January 29, 2010 3:35 AM
  • As indicated above, it's not necessary a performance issue in this case, but it's a bad habit. If you are retriving two out of 8 columns, and you use * for that, that will cause a performance hit. Run the following query on AdventureWorks and then change the column names with *, you will notice both have the same execution plan and same cost.

    SELECT  [SalesOrderID]
          ,[SalesOrderDetailID]
          ,[CarrierTrackingNumber]
          ,[OrderQty]
          ,[ProductID]
          ,[SpecialOfferID]
          ,[UnitPrice]
          ,[UnitPriceDiscount]
          ,[LineTotal]
          ,[rowguid]
          ,[ModifiedDate]
      FROM [AdventureWorks].[Sales].[SalesOrderDetail]

    Abdallah El-Chal, PMP, ITIL, MCTS
    Friday, January 29, 2010 3:42 AM
  • Thanks for the confirmation guys.   I am aware of the implications but they do not apply in the particular case I am working with.
    Friday, January 29, 2010 4:01 AM
  • You've seen all the usual arguments.  It's not a performance hit...

    but...

    Just don't add several new VARCHAR(MAX) columns full of megabytes of data to your table!

    --Brad (My Blog)
    Friday, January 29, 2010 5:57 PM
  • Also you can use Top N for data sampling.
    Friday, January 29, 2010 7:29 PM