SELECT * - pros /cons
select, sql
Solution
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 *?
Problem
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.