Is selecting all columns from a SQL table expensive?

Question

Is it expensive to select all columns from a SQL table, compared to specifying which columns to retrieve?

SELECT * FROM table

vs

SELECT col1, col2, col3 FROM table

Might be useful to know that some of the tables I'm querying have over 100 columns.

yes, imagine having to read all columns in a file versus only a few; also in terms of memory — Scorpion, May 03 '13 at 08:37
Refer to this question : - http://stackoverflow.com/questions/3180375/select-vs-select-column — Vivek Sadh, May 03 '13 at 08:40
Always specify the columns you want. `EXISTS` or `NOT EXISTS` are exceptions. However, even there you could use `EXISTS(SELECT 1...` instead `EXISTS(SELECT *...` since the former is clearer. — Tim Schmelter, May 03 '13 at 08:41
Thanks for the link to the other thread, I can't belive I missed it when searching. — Andreas, May 03 '13 at 09:00

score 4 · Answer 1 · answered May 03 '13 at 08:38

It may be. It depends on what indexes are defined on the table.

If there's a non-clustered index on col1,col2,col3 then that index may be used to satisfy the query (since it's narrower than the table itself, be it a heap or a clustered index), which should result in lower I/O costs.

It's also, generally, preferred so that you can determine which queries are using particular columns in a table.

If the table has no indexes, or only a single clustered index, or there are no indexes that cover your particular query, then every page of the heap/clustered index is going to have to be accessed anyway. Even then, if you have any off-row data (e.g. a largish varchar(max)) then, if you don't include that column in the SELECT then you can avoid that I/O cost.

+1. I'd also add that there are network transfer costs which grow with the size the data set (your application will get the whole dataset sooner if it contains less columns/bytes), and there may be internal costs associated with storing intermediate results (e.g. sorting) which may or may not be connected to how large the data set is, depending on the database in question. And of course a large data set will probably take more time to process on the application side (either directly or via an ORM). — Allon Guralnek, May 03 '13 at 08:45

score 1 · Answer 2 · edited May 23 '17 at 10:25

1

performance in this case, depends upon the proper use of indexes in your query.

If your DB is normalized properly and you have made use of indexes in where clause then for sure performance is going to be better.

Eg.

select * from tableName where id=232

Here index is used.

You can refer following link:

Performance issue in using SELECT *?

What is the reason not to use select *?

edited May 23 '17 at 10:25

Community

1
1

answered May 03 '13 at 08:40

Freelancer

9,008
7
42
81

@Andreas Always welcome. If it is useful, then you can accept this as a answer. – Freelancer May 03 '13 at 09:15

score 1 · Answer 3 · answered May 03 '13 at 08:52

Lets take it apart into the main issues:

The actual database/application: How you type your query MIGHT change how the SQL application actually optimizes your query, where it gets the data from, etc. Then again, it might not. Its hard to generalize here and depends on the database application and setup.

Programmer resources: Using * instead of typing things out is easier and quicker for you. Yay! And if the "implication" behind the command is literally "get everything", maybe its a nice bit of programmer communication to use * instead of listing all the columns out by hand. Being hit in the face with a list of hundreds of column names as a programmer reading code afterwards is an unpleasant experience. On the other hand, listing things by hand can act as a bit of a signal that there's some reason you're asking for those columns specifically. Its not a strong signal, but its still a signal.

Other resources/IO/memory, etc: Now, if you don't actually need all 100 columns and you're querying them because you're lazy, then we get into further grey area. What's the database being loaded from? Where are the query results going? How fast are the read/write speeds on those things? Do you really want to do that with all the columns? How much memory or resources are going to be used in actioning the query? Will it be using an index? Is it indexed? Do you even need to care about optimization at this stage?

So the long and short of it is, its a grey area...

score 0 · Answer 4 · answered May 03 '13 at 08:41

Generally you should only select the columns that you need for the query.

Sometimes selecting all the columns for a query which is used later in a stored procedure won't make any difference due to how the execution plan optimises the whole stored procedure. Also indexes on columns will have an effect.

Is selecting all columns from a SQL table expensive?

4 Answers4