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.

Answer 1

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

Answer 2

I don't think it's a serious performance hit, but the recommended good practice is to explicitly list all needed columns.

Check this wiki page http://wiki.lessthandot.com/index.php/Don't_Use_(select_*),_but_List_Columns

---

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

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 3

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

Answer 4

Thanks for the confirmation guys.   I am aware of the implications but they do not apply in the particular case I am working with.

Answer 5

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)

Answer 6

Also you can use Top N for data sampling.