Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

I've heard that SELECT * is generally bad practice to use when writing SQL commands because it is more efficient to SELECT columns you specifically need.

If I need to SELECT every column in a table, should I use

SELECT * FROM TABLE

or

SELECT column1, colum2, column3, etc. FROM TABLE

Does the efficiency really matter in this case? I'd think SELECT * would be more optimal internally if you really need all of the data, but I'm saying this with no real understanding of database.

I'm curious to know what the best practice is in this case.

UPDATE: I probably should specify that the only situation where I would really want to do a SELECT * is when I'm selecting data from one table where I know all columns will always need to be retrieved, even when new columns are added.

Given the responses I've seen however, this still seems like a bad idea and SELECT * should never be used for a lot more technical reasons that I ever though about.

Solution 1:

One reason that selecting specific columns is better is that it raises the probability that SQL Server can access the data from indexes rather than querying the table data.

Here's a post I wrote about it: The real reason select queries are bad index coverage

It's also less fragile to change, since any code that consumes the data will be getting the same data structure regardless of changes you make to the table schema in the future.

Solution 2:

Given your specification that you are selecting all columns, there is little difference at this time. Realize, however, that database schemas do change. If you use SELECT * you are going to get any new columns added to the table, even though in all likelihood, your code is not prepared to use or present that new data. This means that you are exposing your system to unexpected performance and functionality changes.

You may be willing to dismiss this as a minor cost, but realize that columns that you don't need still must be:

1. Read from database
2. Sent across the network
3. Marshalled into your process
4. (for ADO-type technologies) Saved in a data-table in-memory
5. Ignored and discarded / garbage-collected

Item #1 has many hidden costs including eliminating some potential covering index, causing data-page loads (and server cache thrashing), incurring row / page / table locks that might be otherwise avoided.

Balance this against the potential savings of specifying the columns versus an * and the only potential savings are:

1. Programmer doesn't need to revisit the SQL to add columns
2. The network-transport of the SQL is smaller / faster
3. SQL Server query parse / validation time
4. SQL Server query plan cache

For item 1, the reality is that you're going to add / change code to use any new column you might add anyway, so it is a wash.

For item 2, the difference is rarely enough to push you into a different packet-size or number of network packets. If you get to the point where SQL statement transmission time is the predominant issue, you probably need to reduce the rate of statements first.

For item 3, there is NO savings as the expansion of the * has to happen anyway, which means consulting the table(s) schema anyway. Realistically, listing the columns will incur the same cost because they have to be validated against the schema. In other words this is a complete wash.

For item 4, when you specify specific columns, your query plan cache could get larger but only if you are dealing with different sets of columns (which is not what you've specified). In this case, you do want different cache entries because you want different plans as needed.

So, this all comes down, because of the way you specified the question, to the issue resiliency in the face of eventual schema modifications. If you're burning this schema into ROM (it happens), then an * is perfectly acceptable.

However, my general guideline is that you should only select the columns you need, which means that sometimes it will look like you are asking for all of them, but DBAs and schema evolution mean that some new columns might appear that could greatly affect the query.

My advice is that you should ALWAYS SELECT specific columns. Remember that you get good at what you do over and over, so just get in the habit of doing it right.

If you are wondering why a schema might change without code changing, think in terms of audit logging, effective/expiration dates and other similar things that get added by DBAs for systemically for compliance issues. Another source of underhanded changes is denormalizations for performance elsewhere in the system or user-defined fields.

Solution 3:

You should only select the columns that you need. Even if you need all columns it's still better to list column names so that the sql server does not have to query system table for columns.

Also, your application might break if someone adds columns to the table. Your program will get columns it didn't expect too and it might not know how to process them.

Apart from this if the table has a binary column then the query will be much more slower and use more network resources.

Solution 4:

There are four big reasons that select * is a bad thing:

1. The most significant practical reason is that it forces the user to magically know the order in which columns will be returned. It's better to be explicit, which also protects you against the table changing, which segues nicely into...

2. If a column name you're using changes, it's better to catch it early (at the point of the SQL call) rather than when you're trying to use the column that no longer exists (or has had its name changed, etc.)

3. Listing the column names makes your code far more self-documented, and so probably more readable.

4. If you're transferring over a network (or even if you aren't), columns you don't need are just waste.