Friday, January 29, 2010 3:29 AMI 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:32 AMModeratorThis 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:
- Marked As Answer by beneuto Friday, January 29, 2010 3:59 AM
Friday, January 29, 2010 3:35 AMModeratorI 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
Friday, January 29, 2010 3:42 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 4:01 AMThanks 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 5:57 PMModeratorYou've seen all the usual arguments. It's not a performance hit...
Just don't add several new VARCHAR(MAX) columns full of megabytes of data to your table!
--Brad (My Blog)
Friday, January 29, 2010 7:29 PMAlso you can use Top N for data sampling.