-2

Possible Duplicate:
select * vs select column

Suppose there is a table with 20 columns. Is there any speed difference between a select * and a select that includes all 20 columns explicitly?

If there is no difference, what would you advise? Should I use the lazy select * or should I create a query string with each column?

(If it makes any difference: I use SQL Server.)

Community
  • 1
  • 1
Sascha
  • 1,210
  • 1
  • 17
  • 33
  • Are you running such a large operation that the few microseconds you'd save would be worth the effort of changing your code? – Marc B Sep 26 '12 at 05:39
  • Have you tried both ways? Benchmark it and let us know. – Tass Sep 26 '12 at 05:41
  • I'm sure it's one of the 10-most-frequently-asked-questions on stackoverflow. – dkretz Sep 26 '12 at 05:49
  • My question is no a duplicate of "select * vs select column", because that question is about the difference of selecting a subset of all columns. – Sascha Sep 26 '12 at 07:38

2 Answers2

1

Not so much about speed, but maintainability...

If your application requests columns specifically, and the table structure changes (column removed or renamed) then the statement will break when it runs, indicating exactly where the issue lies.

select * will still work after a structure change, but may cause a more subtle issue later on in the application that will be more difficult to trace.

It is additional work up front, but better for maintainability to explicitly list columns.

Assuming the where clause is paramaterised the SQL is only parsed once, and should be in the statement cache after that, execution speed won't be much different.

shonky linux user
  • 6,131
  • 4
  • 46
  • 73
0

As a good practice its better to include column names in the select statement itself. Because the structure of the table can change in the future, and you would be pulling unwanted data..

Performance wise

When you use select * , sql server compiler has to replace the * with column names, which is an additional task, but I think that is negligible

Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58