5

Possible Duplicate:
Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc.

Is it bad practice to use Select * ?

I was going through some old code and saw some 'SELECT *' statements. My previous coworker had told me Select * was bad practice, but I couldn't really see the reason why (unless of course I only needed to return a few fields). But for full 'detail retrieves' (Get by Id type queries) Select * seems right.

Community
  • 1
  • 1
mint
  • 3,341
  • 11
  • 38
  • 55
  • If you need the full detail, then use `select *` -- especially if you need future detail columns that you don't know the names of. – Lou Franco Aug 02 '10 at 14:22
  • 2
    No @ Lou Franco, that is a poor practice even then. You do not know what will be added in the future. You may have columns that added that are for adminstrative purposes that you do not want users to see. It is always a poor practice to use select * . And defining the columns is usually better for performance too as the database doesn't have to look them up and if you have a join at least one column is duplicated meaning you are wasting bandwidth returning it. – HLGEM Aug 02 '10 at 16:46

7 Answers7

5

It's bad practice.

If your schema changes down the road, the calling application may get more fields than it knows what to do with.

Also, you are getting more info than you need, which affects performance.

Also also, it implies you don't know what the columns are.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • I almost saw that as an advantage. I have to add a field to my table and now I have to go into the code to change the sql query for the new field, versus with Select * no change would have to be made. – mint Aug 02 '10 at 14:18
  • 1
    @snow - Really though you should be using stored procs for calling that stuff instead of hard coded SQL, in which case you change the stored proc and all your calls are updated. – JNK Aug 02 '10 at 14:34
  • @snow, it's a huge disadvantage, you may expose information to the user that you don't want them to see. Further it could break this if some yo yo decides to rearrange the columns inthe table (yes there are idiots who do this) and now you are inserting the data from the lastname into the first name column and vice versa. Selct * is just bad. – HLGEM Aug 02 '10 at 16:43
5

Using SELECT * is bad practice for two reasons:

  • It can return extra columns that you don't need, wasting bandwidth
  • It can break your code if someone adds a column
SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
2

Yes, Select * is a bad practice. For one, it is not clear to other developers which columns you really are using. Are you actually using all of them? What happens when you add columns are you using those too? That makes it much more difficult to refactor column names should that need arise. Second, there are some instances where some database systems will remember which columns existed at the time you created an object. For example, if you create a stored procedure with Select *, it will bake in the columns that exist in the table at the time it is compiled. If the table changes, it make not reflect those changes in the stored procedure. There really isn't any reason to use Select * beyond laziness.

Thomas
  • 63,911
  • 12
  • 95
  • 141
  • I had never heard of the 'baking' of columns using select * in stored procedures... noted though. – mint Aug 02 '10 at 14:21
1

Yes, it is deemed bad practice.

It is better to specify an explicit column list, especially if the table contains many columns and you only really need some of them.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
1

If any schema changes occur (extra columns are added), these will be caught by your application. This might be undesirable, say, if you bind a grid dynamically to a DataTable. Also it incurs more overhead on network communications.

Even if you are selecting all columns as of today, define the columns by name - its readable and explicit. Any additional columns will then not cause any problems with your code.

Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
1

When you use SELECT *, you choose to trade immediate productivity (writing a query faster) for potential maintenance productivity (should your underlying query change and thus break dependent code/queries). The "bad-ness" of the practice is a risk management activity.

kbrimington
  • 25,142
  • 5
  • 62
  • 74
0

Even if you need to select all columns it is still better to specify them rather then use 'select *'

Giorgi
  • 30,270
  • 13
  • 89
  • 125