SQL select * vs. selecting specific columns

Question

I was wondering which is best practice. Lest say I have a table with 10+ columns and I want to select data from it.

I've heard that 'select *' is better since selecting specific columns makes the database search for these columns before selecting while selecting all just grabs everything. On the other hand, what if the table has a lot of columns in it?

Is that true?

Thanks

"I've heard that 'select *' is better " - where did you hear that? It's wrong. Also, probably been asked many times before... — Mitch Wheat, Dec 17 '13 at 07:32
one of the programmers in our company. could you explaing why it's wrong? thanks — developer82, Dec 17 '13 at 07:33
I would refer to this past post. http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful — Kiwi, Dec 17 '13 at 07:35
Go through this http://blog.sqlauthority.com/category/main/pinal-dave/page/7/ — Suraj Singh, Dec 17 '13 at 07:50

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

5

It is best practice to explicitly name the columns you want to select.

As Mitch just said the performance isn't different. I even heard that looking up the actual columns names when using * is slower.

But the advantage is that when your table changes then your select does not change when you name your columns.

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 17 '13 at 07:32

juergen d

201,996
37
293
362

score 4 · Accepted Answer · edited May 23 '17 at 11:58

4

I think these two questions here and here have satisfactory answers. * is not better, actually it is slower is one reason that select * is not good. In addition to this, according to OMG Ponies, select * is anti-pattern. See the questions in the links for detail.

edited May 23 '17 at 11:58

Community

1
1

answered Dec 17 '13 at 07:35

Barış Akkurt

2,255
3
22
37

score 2 · Answer 3 · answered Dec 17 '13 at 07:36

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

It's also require less changes, 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.

score 1 · Answer 4 · answered Dec 17 '13 at 07:33

1

Definetly not. Try making a SELECT * from a table which has millions of rows and tens of columns.

The performance with SELECT * will be worse.

answered Dec 17 '13 at 07:33

Semih Yagcioglu

4,011
1
26
43

score 0 · Answer 5 · answered Dec 17 '13 at 07:33

It depends on what you're about to do with the result. Selecting unnecessary data is not a good practice either. You wouldn't create a bunch of variables with values you would never use. So selecting many columns you don't need is not a good idea either.

score 0 · Answer 6 · answered Dec 17 '13 at 07:38

It depends.

Selecting all columns can make query slower because of need of reading all columns from disk -- if there are a lot of string columns (which are not in index) then it can have huge impact on query (IO) performance. And from my practise -- you rely need all columns.

From the other hand -- for small database with a few user and good enough hardware it's much easier to select just all columns -- especially if schema changes often.

However -- I would always recommended to explicitly select columns to make sure it doesn't hurt performance.

SQL select * vs. selecting specific columns

6 Answers6

It is best practice to explicitly name the columns you want to select.