-2

I read somewhere that never use * to return all columns in a table–it’s lazy. In this case, if I need all 5 columns and there won't be any changes to the table later, are there any performances difference in:

SELECT * 
from table

vs. 

SELECT id,col1,col2,col3,col4 
from table
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
stackoverflower
  • 919
  • 1
  • 7
  • 5
  • 2
    This might help [Why is SELECT * considered harmful?](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) – Sam May 10 '13 at 19:10
  • 2
    There really isn't a performance difference. It's mainly a caveat because in team environments, you'll never know when someone may add 20 columns (one of which is a 2M blob). That would definitely cause a performance issue. – Erik Nedwidek May 10 '13 at 19:11
  • If you use “*” the database engine have to look for the field for you. – a52 May 10 '13 at 19:11
  • If performance is your concern, you should try and time those queries. Check [this](http://stackoverflow.com/questions/595762/calculate-execution-time-of-a-sql-query) out – Kimko May 10 '13 at 19:11
  • @a52: and? It will have to do that for `select col1, col2 ...` just as well. –  May 10 '13 at 19:12
  • 3
    This statement is not true: "*there won't be any changes to the table later*" - experience shows that there **will** be a change at some point. –  May 10 '13 at 19:12
  • 1
    If it is an ad hoc query you are not going to save in any way shape or form * can be fine. If not type out the solumn names. There are tons and tons of articles on this. If you type in `select *` bad into google you will probably get a million results. – Zane May 10 '13 at 19:13
  • 7
    @a52 please stop spreading this terrible myth. SQL Server still has to validate that col1, col2 exist, validate their data types, etc. in either case. – Aaron Bertrand May 10 '13 at 19:15

2 Answers2

3

I don't think there is a noticeable performance difference, but in production code I would stick with an explicit column list:

  • It's clearer
  • There's no way you can absolutely know there will never be a column that is added, removed, or changed
  • It's easier to update the query later to do something like alias a column
  • If you end up having to add a JOIN to that query, things could get interesting if you keep the SELECT *
Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
0

There shouldn't be any difference. It's just bad practice - if you modify table later on it may break some more complex queries (like queries across multiple tables) if they use *.

wroniasty
  • 7,884
  • 2
  • 32
  • 24
  • 1
    There is a difference in some platforms. – Kermit May 10 '13 at 19:11
  • @FreshPrinceOfSO for example? – wroniasty May 10 '13 at 19:12
  • 2
    [Bullet point 4](http://stackoverflow.com/a/3180435/679449) and [this article](http://bi-bigdata.com/2012/09/02/select-vs-select-in-sql-server-query/) – Kermit May 10 '13 at 19:14
  • @wroniasty - I once saw a SQL Server view that was along the lines of `SELECT A.*, B.X, C.Y, D.Z`, and when table A had a column added to the end of it, the view kept the captions intact, returned the same number of columns as before, but the last column of A was returned where B.X was, B.X got moved to C.Y's column, and D.Z simply didn't get returned. I immediately updated the view to call out the specific columns, and no more problems. – LittleBobbyTables - Au Revoir May 10 '13 at 19:19
  • Read @AaronBertrands comment in the main question you are wrong. – Zane May 10 '13 at 19:22
  • @LittleBobbyTables your example is scary, but - I stand corrected – wroniasty May 10 '13 at 19:26
  • @Zane doing `SELECT *` and `SELECT [all columns in table]` is the same thing - the same number of lookups in the meta data – wroniasty May 10 '13 at 19:27