SELECT *

Question

SELECT *

Adam Sandler

SSCrazy

Points: 2697
More actions
June 28, 2007 at 4:26 am

#120442

I have a confession to make - I am using select * in parts of my db.

Everything I read says not to do this, ie I should be selecting just the columns I need.

However what I need is a consistent "Presenation" of my data ie say Company Details - whatever kind of report or view is run, the company details must always contain the same data.

So I have a specific View_CompanyDetails which holds exactly the data my users want.

Literally 10s or Hundreds of other views or sps will then include the SELECT * FROM View_CompanyDetails for the user.

If I follow the general advice of never using SELECT * - if for example it beacme critical to have differnat data in View_CompanyDetails - I would have to go any manually change potentailly hundreds of other objects?

IN essence I would be explitly choosing may data in View_CompanyDEtails and then being forced into explicitly choosing the data again (redundantly cos View_CompanyDetails is the data I really want)

IN essence can I use SELECT *

?

It seems ridiculous not too? Or is there something else I need to think about?

Cheers

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

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Yes you can use select * if you want to select all columns people say that you should specify the column names explicitly so that query optimizer can use the indexes available to the max extent. Since you say thats a view nothing wrong in using select *. You might have few minor performance problems.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 2

Just be aware that when the procedures compile, SQL Server will resolve the SELECT * into column names. This means that if you change the underlying view (not saying that you ever would) then the store procedures will not reflect the changes and you may have to recompile all the procedures - been there, seen it, done it, got the T shirt.

J

Adam Sandler SSCrazy Points: 2697 More actions · Answer 3

Thanks both of you.

Jeremy - yes I know, I have a procedure which goes thru each subsequent view (very clumsily) and use sp_RefreshView

but then if I wasn't using SELECT * - I would have to go thru and rfresh all the views by hand anyhow.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

There are many valid reasons not to perform a SELECT * from a table. You probably know them. But, instead of using SELECT C1, C2,... from the table, you are creating views with SELECT C1, C2,... from the table and then doing a SELECT * on the view. A simple, elegant solution which, I imagine, works well for you. Nothing wrong w/that.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Concur...

--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)

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 6

There are a few situations where select * from a table might be beneficial, from a maintenance standpoint. Example: My customer uses a service vendor that performs USPS address validation, zipcode+4 lookup, and also looks up political districts like city council, state representative district etc.

The vendor was given a view that used select field1, field2 etc. for all the fields in the table; but over the course of time, one obsolete field was deleted from the underlying table (breaking the view), and later on another type of political district was added (requiring the view to be changed). Even though they don't need all the fields, if the view had been a simple select * from table then it would have continued to work without any changes.

So as with all things SQL, it depends. MTCW