SQL Query Question - Select * from view or Select col1,col2.....from view

Question

We are using SQL2005 but this question can be for any rdbms.

Which is more efficient, when selecting all columns from a view

Select * from view or Select col1,col2.....from view

Answer 1

If you're really selecting all columns, it shouldn't make any noticeable difference whether you ask for * or if you are explicit. The SQL server will parse the request the same way in pretty much the same amount of time.

Answer 2

It is best practice to select each column by name. In the future your DB schema might change to add columns that you would then not need for a particular query. I would recommend selecting each column by name.

Answer 3

Always do select col1, col2 etc from view. There's no efficieny difference between the two methods that I know of, but using "select *" can be dangerous. If you modify your view definition adding new columns, you can break a program using "select *", whereas selecting a predefined set of columns (even all of them, named), will still work.

Answer 4

I guess it all depends on what the query optimizer does.

If I want to get every record in the row, I will generally use the "SELECT *..." option, since I then don't have to worry should I change the underlying table structure. As well, for someone maintaining the code, seeing "SELECT *" tells them that this query is intended to return every column, whereas listing the columns individually does not convey the same intention.

Answer 5

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.

Answer 6

Select * is a poor prgramming practice. It is as likely to cause things to break as it is to save things from breaking. If you are only querying one table or view, then the efficiency gain may not be there (although it is possible if you are not intending to actually use every field). If you have an inner join, then you have at least two fields returning the same data (the join fields) and thus you are wasting network resources to send redundant data back to the application. You won't notice this at first, but as the result sets get larger and larger, you will soon have a network pipeline that is full and doesn't need to be. I can think of no instance where select * gains you anything. If a new column is added and you don't need to go to the code to do something with it, then the column shouldn't be returned by your query by definition. If someone drops and recreates the table with the columns in a different order, then all your queries will have information displaying wrong or will be giving bad results, such as putting the price into the part number field in a new record.

usit is quick, but it is almost as simple to drag the column names over from the object browser, so that is just pure laziness not efficiency in coding.

Answer 7

For performance - look at the query plan (should be no difference).

For maintainability. - always supply a fieldlist (that goes for INSERT INTO too).

Answer 8

Just to clarify a point that several people have already made, the reason Select * is inefficient is because there has to be an initial call to the DB to find out exactly what fields are available, and then a second call where the query is made using explicit columns.

Feel free to use Select * when you are debugging, running casual queries or are in the early stages of developing a query, but as soon as you know your required columns, state them explicitly.