SQL query – Select * from view or Select col1, col2, … colN from view

sqlsql-server

We are using SQL Server 2005, but this question can be for any RDBMS.

Which of the following is more efficient, when selecting all columns from a view?

Select * from view 

or

Select col1, col2, ..., colN from view

Best Solution

NEVER, EVER USE "SELECT *"!!!!

This is the cardinal rule of query design!

There are multiple reasons for this. One of which is, that if your table only has three fields on it and you use all three fields in the code that calls the query, there's a great possibility that you will be adding more fields to that table as the application grows, and if your select * query was only meant to return those 3 fields for the calling code, then you're pulling much more data from the database than you need.

Another reason is performance. In query design, don't think about reusability as much as this mantra:

TAKE ALL YOU CAN EAT, BUT EAT ALL YOU TAKE.