Selecting columns in a query

Wednesday, March 23, 2005

I've always known this, but I thought I would share it.

I just did a quick performance test on two queries using the Sql Server Profiler. The difference between the two queries was

Query 1: select * from table where...........

Query 2: select col1, col2, ..... from table where........

There is a small, but noticable performance difference in the two queries. Query 1 was significantly slower on the first call versus Query 2, somewhat slower on the second call, and performed the same on subsequent calls. While this was particular to my application, I think it is something I would suggest to all developers to specifically specify the columns in a query.

Wally

4 Comments

Another great reason for ALWAYS specifying the columns is that if the underlying data-structure changes you do not have to re-write your code. You can just use the "Select newcol1 as col1" to keep your code working.

Geoff - Wednesday, March 23, 2005 4:53:00 PM

How many columns does "table" have?

Is "select *" slower if the number of columns increases.

Ron Krauter - Wednesday, March 23, 2005 7:28:00 PM

Select * From = Naughty/Lazy programming ! :o)

Charlie Barker - Thursday, March 24, 2005 12:51:00 PM

Wally,

Another great reason to ALWAYS specify your column names: what if someone later on adds a varchar(300) column on to your table? Your app certainly doesn't need it... so no sense marshalling that info over the wire...

Explicitly specifying your columns in a SELECT is a best practice.

Michael K. Campbell - Tuesday, March 29, 2005 11:27:00 PM

Comments have been disabled for this content.