Is it really bad to use * in SQL?

Question

Possible Duplicate:
SQL: Using Select *

Hi everyone!

I wonder if it is really a bad idea to use the * symbol in stored procedure in SQL server? Is really better to write

SELECT
 NAME,
 AGE,
 OTHER_STUFFS

FROM
 TABLE

than

SELECT * FROM TABLE

For sure is only 3 columns in the table.. For performances is it better to enumerate every column? Tanks for help..

Answer 1

Using SELECT * instead of selecting all columns doesn't make a difference at all. In fact, SELECT * could be a little bit faster, because less bytes have to be sent to the server to execute the command.

Answer 2

I would say that it's only bad when you do something along the lines of

insert into Table1
select * from Table2

Because what if someone adds a column to Table2 or something like that.

It doesn't matter how you specify the necessary columns in a select statement, it won't affect performance.

Answer 3

If you really are listing every column in the table, then the performance should be equivalent.

The trouble is when you later add more columns to that table, then they are getting selected by the stored procedure and not used unless you go back and update all your procs after each table change (I don't).

Answer 4

While you might be using all the columns now it's possible (likely even) that the table will have columns added to it in the future. When this happens you will be selecting extra data and depending what you are doing with it this could cause problems.

Stolen from duplicate post http://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc:

You may be willing to dismiss this as a minor cost, but realize that columns that you don't need still must be:

1. Read from database
2. Sent across the network
3. Marshalled into your process
4. (for ADO-type technologies) Saved in a data-table in-memory
5. Ignored and discarded / garbage-collected