SELECT * vs SELECT k1, k2...

Question

SELECT * vs SELECT k1, k2...

FreeHansje

SSChampion

Points: 11751
More actions
September 24, 2008 at 1:44 am

#191403

Without thinking I qualify the column names of a table when using SELECT instead of using SELECT *, because this is best practice/more efficient, I have learned. But I have never wondered why this is so.
Can any1 explain this for me?
Greetz,
Hans Brouwer

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

one of the reason is

optimizer will have to search the column names and its data type from the system table. It will take some extra overheard.

karthik

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

If you only select the columns you want, you will be returning less data (obviously).

Also, SQL Server may be able to get the columns you want from an index, without having to go to the actual data in the table, so you will get a much more efficient and performant query plan. If you do a SELECT *, it will always have to go to the table, and you are more likley to get table scans than efficient index seeks.

rajankjohn SSCertifiable Points: 5061 More actions · Answer 3

apart from performance benefits, it is likely that if someone else adds an additional column, there will be problems in the application because it returns more columns than desired.

Jerry Hung SSChampion Points: 12968 More actions · Answer 4

Rajan John (9/24/2008)
apart from performance benefits, it is likely that if someone else adds an additional column, there will be problems in the application because it returns more columns than desired.

Or vice versa, a column removed may cause issue

Also, it's likely table A will have ID field that joins to table B which also has ID field

SELECT * will return 2 ID columns

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

FreeHansje SSChampion Points: 11751 More actions · Answer 5

Tnx for answering all. Glad to read there are several reasons(and good ones) for using qualified columns.

Greetz,
Hans Brouwer

rhandloff Old Hand Points: 365 More actions · Answer 6

rhandloff

Old Hand

Points: 365

September 26, 2008 at 3:13 pm

#877988

It also makes your code easier to read.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

Except in rare cases like a pass-through view (like a synonym), SELECT * will frequently (usually) make it impossible to do index seeks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)