May 17, 2011 at 7:54 am
Why SELECT * is not a best practise?
Will there be any difference in performance in executing a query with SELECT * (all) columns and a query with SELECT col1, col2, col3, ... only required / specific columns (or all column names included instead of *)?
(or)
While selecting rows from a table with more columns (say 150 columns), will there be any difference in performance in executing an SQL query selecting all columns and a query with only few required columns?
May 17, 2011 at 8:02 am
There can definitely be a performance difference. If you select * from a table with 150 columns and you only need 3 columns you asking sql to retrieve and return 147 columns of data that is not needed by the calling program.
It is also seen as lazy in the industry because of the potential performance issues. Lets say you have a table with 5 columns of small text data and you run select * on this table to get your desired output. Then down the road a new column gets added that contains a very large attachment in every row. That same select * will be crippled because of the huge amount of data that is now being retrieved instead of the original 5 columns of small text data.
There are so many reasons to select ONLY the columns you need it is hard to put into a single post. Of course it is fine for development but before completing development use only the data you need. If it is a table with a lot of columns you drag the columns folder from the Object Explorer to a query window and will drop all the column names for you nice and simple. Then you can just remove the ones you don't need, or other columns from another table etc.
I am sure there will be plenty of others jump in with their thoughts too but it industry wide considered bad practice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 17, 2011 at 8:40 am
If your application is expecting 150 columns and you add a column to the table it will probably break when it is suddenly starts getting 151 columns.
May 17, 2011 at 10:14 pm
Suriya.pn (5/17/2011)
Will there be any difference in performance in executing a query with SELECT * (all) columns and a query with SELECT col1, col2, col3, ... only required / specific columns
Yes, for following reasons:
1) COVERING index
2) Network load
May 18, 2011 at 6:37 am
Very simply put. You ask the server to do WAY more work than needed and it hurts performance with a very long list of reasons.
Worst of then all, you'll never be able to tune the server by adding indexes and without changing the code (well a little bit, but not as much as you'd want to).
May 18, 2011 at 6:52 am
Suriya.pn (5/17/2011)
Why SELECT * is not a best practise?
Just building up on previous postings, as a rule of thumbs: never retrive all columns, never retrieve all rows.(*)
(*) As always happens there are exceptions to the rule but any deviation to the never/never concept has to be properly supported and documented.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 18, 2011 at 6:56 am
Spam!... Reported, please do not reply.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 18, 2011 at 6:57 am
PaulB-TheOneAndOnly (5/18/2011)
Spam!... Reported, please do not reply.
You can reply... just don't quote him and give him more of what he wants!
May 18, 2011 at 6:59 am
PaulB-TheOneAndOnly (5/18/2011)
Suriya.pn (5/17/2011)
Why SELECT * is not a best practise?Just building up on previous postings, as a rule of thumbs: never retrive all columns, never retrieve all rows.(*)
(*) As always happens there are exceptions to the rule but any deviation to the never/never concept has to be properly supported and documented.
I've never seen any good use of select * except when needed to have a quick look at what the table contains and what the data means... in DEV environement.
A case *could* be made for audit triggers (fails if the audited table changes but not the history). But I can't think of anything else.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply