12

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
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Napco
  • 205
  • 1
  • 3
  • 5
  • 2
    Duplicate of [What is the reason not to use select \*?](https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select) – TylerH Feb 06 '20 at 14:57

10 Answers10

40

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.

devlord
  • 4,054
  • 4
  • 37
  • 55
  • 1
    The only time you should be tempted to use "select *" is within an exists(), but don't! Use "select 1" instead. – Even Mien Sep 25 '08 at 01:11
  • 4
    @Even Mien: It depends on the implementation. Some RDBMS know how to optimize `SELECT *` inside an `EXISTS()` predicate, so it can even be more efficient than `SELECT 1`. The only way to know for usre is to test with your brand and version. – Bill Karwin Sep 28 '09 at 21:36
11

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.

Gthompson83
  • 1,099
  • 1
  • 8
  • 18
4

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.

CJM
  • 11,908
  • 20
  • 77
  • 115
3

Select * is a poor programming 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.

Plus it is quick to drag the column names over from the object browser, so that is just pure laziness not efficiency in coding.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1

It depends. Inheritance of views can be a handy thing and easy to maintain (SQL Anywhere): 

create view v_fruit as select F.id, S.strain from F key join S; 
create view v_apples as select v_fruit.*, C.colour from v_fruit key join C;
Brian Mains
  • 50,520
  • 35
  • 148
  • 257
Simon
  • 11
  • 1
  • This is a very good example of when SELECT view.* FROM view is actually a good practice. And the reason is because we can have tons of fruits, it'd be a nightmare to change every time our parent class changes, so I do agree with this approach, however I don't know if this would be a good practice if there is some reporting layer on top of those fruits, of course assuming that we will always need all columns from v_fruit – dim_user Jun 07 '18 at 15:17
  • Also, if for some reason a column name gets added to v_fruit but the same name already existed in any of the v_apples or v_pears, etc. we will get an error during query time, I know this is a design issue but it is definitely a potential risk, any thoughts on how to overcome this? – dim_user Jun 07 '18 at 15:33
0
select 
column1
,column2
,column3
.
.
.
from Your-View

this one is more optimizer than Using the 

select *
from Your View
Botz3000
  • 39,020
  • 8
  • 103
  • 127
Alireza Masali
  • 668
  • 1
  • 10
  • 19
0

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.

Adam Bellaire
  • 108,003
  • 19
  • 148
  • 163
0

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.

Mike McAllister
  • 1,479
  • 2
  • 12
  • 15
0

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.

Ken Ray
  • 2,500
  • 3
  • 21
  • 28
0

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

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

Amy B
  • 108,202
  • 21
  • 135
  • 185