SELECT * - pros /cons

Question

I always hear from SQL specialists that it is not efficient to use the '*' sign in SELECT statement and it is better to list all the field names instead.

But I don't find it efficient for me personally when it comes to adding new fields to a table and then updating all the stored procedures accordingly.

So what are the pros and cons in using '*' ?
Thanks.

Answer 1

In general, the use of SELECT * is not a good idea.

Pros:

• When you add/remove columns, you don't have to make changes where you did use SELECT *
• It is shorter to write
• Also see the answers to: Can select * usage ever be justified?

Cons:

• You are returning more data than you need. Say you add a VARBINARY column that contains 200k per row. You only need this data in one place for a single record - using SELECT * you can end up returning 2MB per 10 rows that you don't need
• Explicit about what data is used
• Specifying columns means you get an error when a column is removed
• The query processor has to do some more work - figuring out what columns exist on the table (thanks @vinodadhikary)
• You can find where a column is used more easily
• You get all columns in joins if you use SELECT *
• You can't use ordinal referencing (though using ordinal references for columns is bad practice in itself)
• Also see the answers to: What is the reason not to use select *?