Solved

Select * vs Select column names ?

Posted on 2008-10-16
23
3,202 Views
Last Modified: 2012-05-05
Recently we have big discussion in company why to use or not to use Select * in our SP's and there were many "for and against" so i need some expert opinions.

We all agreed that additional I/O is against, aditional "query" need to be put against table for retriving those column names when u you put * wich is again against.
But on the other hand we all programming with classes and mostly our classes looks like tables and filling classes with SP most of the time require all fields, especilly if you fill your class with several SP's then altering table need all SP+s to be altered too wich with select * wont be issue.
When i mean on Select * and using many SP's to "fill" your same class you will use
Select tableName.*, otherFileds.... from tableName join other table ... wich again will work fine with changing your table columns.

Checking execution plan i didnt get any different plans using * or not.

Is there anything i am missing here or actually Select * in some cases will be much better solution then using column names ?

Tnx
0
Comment
Question by:Cvijo123
  • 8
  • 5
  • 3
  • +4
23 Comments
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 150 total points
Comment Utility
I personally am a long handed select * person.  By that I mean.


select Field1, field2, field3
from yourTable


The reasons are: simple.  Let's say you are using the stored procedure to insert data into a table.  

ie:
insert into MyTable(field1,field2,field3)
exec up_MyProc

And all is going along fine until you add Field4.  Your procedure will immediately start returning the 4th column (unlike a view that does select *) and your insert will start failing.
0
 

Expert Comment

by:jsonnenvzla2
Comment Utility
At Enterprise manager, right click on the table to obtain a complete detailed sql statement for the record, simply cut and paste
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Hello Cvijo123,

Production code should *always* specify the columns.  Brandon's post offers a great example for why.

For one-off, ad hoc queries, I will sometimes use * to save a few seconds, but I always specify the columns
for anything that goes into code and anything that goes into sprocs and views.

Regards,

Patrick
0
 
LVL 2

Assisted Solution

by:Deepika_Rastogi
Deepika_Rastogi earned 50 total points
Comment Utility
0
 
LVL 13

Expert Comment

by:AielloJ
Comment Utility
Explicit column names are the only way to go.  With security concerns being a priority, stored procedures and views that capture all the columns in the tables accessed in the query are a security risk.  The code today may require most of the columns to be spelled out, but what about the future?  Is there a possibility that columns may be added to the design that shouldn't be generally accessable?  That would require a lot of rework to the code.  Technical requirements and best practices for many government and industry database projects explicitly prohibit 'SELECT *'.  The general line of thought in almost all database projects is the minimum amount of data exposure to get the job done is the requirement.  Code that contains 'SELECT *' won't pass a security and quality audit in company's that do them.
0
 
LVL 5

Author Comment

by:Cvijo123
Comment Utility
thanks all for answering but for:

Brandon and matthewspatrick: we dont use that kind of inserts using exec SP into table.

Aiello: where would be security risk when i put select * ? injection is not possible and if table conatins some data that need to be hidden of course Select * wont be used.

I mybe didnt ask question good, what i wanted to know is: Will SP's with explicit columns execute faster than using Select *, or any other difference that SQL engine will do with those 2 approach.

In another word what for SQL means using * and using columns ?

0
 
LVL 2

Expert Comment

by:Deepika_Rastogi
Comment Utility
One reason that selecting specific columns is better is that it raises the probability that SQL Server can access the data from indexes rather than querying the table data.

Visit
http://weblogs.asp.net/jgalloway/archive/2007/07/18/the-real-reason-select-queries-are-bad-index-coverage.aspx
for details.

also visit
http://stackoverflow.com/questions/65512?sort=newest
for more views.

0
 
LVL 5

Author Comment

by:Cvijo123
Comment Utility
Deepika: i dont think that is actually true.
If i select only fields that are indexed than SQL doenst have to go to actual page data to fetch rest of columns in other cases it always need to go to leaf level on index and jump to page that hold column data.
SQL will use index what i will put in where clausule or join one, so using indexes wont be wrong imo.

Only way it should work faster is to select only those fields that are indexed ( not good in my case)

If u read my first question i use those SP's to fill my classes so in 95% of time i will always need all columns in table and than your suggestion doesnt work.
0
 
LVL 13

Expert Comment

by:AielloJ
Comment Utility
Cvijo123:

SQL injection doesn't have much to do with not using SELECT *.  The issue is the potential exposure of data elements in applications that shouldn't have access  to them.  Your post speaks of using them in stored procedures.  In most organizations stored prcedures are written with the intent of be reusable in many applications.  Are you willing to take the risk that a stored procedure you write today using SELECT * will never use a table or view that now contains columns that are considered sensitive in nature?  Databases have a tendency to evolve beyond their original intent.

Let's take a small and oversimplified example:  The tables in a simple employee name, department, and plant location, lookup system is used as the starting point for a corporate HR system.  After all management reasons, it already has all of the employees, departments, and their locations in it.  Let's add the SSN and other columns required to support HR's needs.  At this point all of your stored procedures using SELECT * have made this sensitive data available in the class modules that used SELECT *.  This could be a malpractice liability for you or your organization.  Your code would definitely fail the security and bet practices audits of most of the organizations I've been at.  Then you're going to have to rewrite it anyway.

A basic function of the DBA's in almost every organization is to scrutinize for and demand rework of just what you're advocating.  In any organization doing a mediocre to good job at data security, your code will never be migrated to production.
0
 
LVL 5

Author Comment

by:Cvijo123
Comment Utility
Aiello: i agree what u said but i wrote in my first post i fill my classes wich are same as tables in more than 90% of time and when i use * i said i use tableName.* .. so joining some sensitive table in SP wont result showing all fields from other table than one that actually need all data to fill class. So if my SP is selcting employee and i use it to fill my class with employee data i would use SP like

Select
employee.*,
otherTable.someFiled
from employee
inner join otherTable
on employee.id = otherTable.id
where ....


But i see your point and agree that using Select * wtih all joins is wrong.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
"Only way it should work faster is to select only those fields that are indexed ( not good in my case)"

This is the benefit of the include statement in the index.  It allows the benefit of not having to index the data, but it allows you to include non-indexed columns in the index to prevent having to go back to the page to lookup on the data.  
0
 
LVL 5

Author Comment

by:Cvijo123
Comment Utility
@Brandom it is feature for SQL 2005+ and we are still on SQL 2000 :(
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
Ugh.  Sorry to hear that. lol.  I remember those times.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
Comment Utility
Next time do yourself a favor and search the questions here on this site.  This question is constaintly coming up, usually from developers that have "upgraded" from MS Access.   Unfortunately until you have some valid experience behind you you will not not see any benefit in explicitly defining your columns. So I would suggest you continue the way you are doing it.  Eventually (in five years probably) you will figure it out. It is by far the best way to learn.

The bottom line is that whereever you see SELECT * usually implies that no time has been spent analyzing the requirements for the project and hence the project is doomed to failure.

Good luck.  I suspect you are going to need it.
0
 
LVL 5

Author Comment

by:Cvijo123
Comment Utility
@acperkins: dont u think i searched the answer  and didnt find solution that satisfied me so i asked with some additional info ?

As u can see i put advanced on this subject wich means i have prettty good knowladge on SQL so answers like "using index is benefit" is far away from answer i am searching. I aksed about speed of executing and in first quesstion you can see i wrote and make ppl  to know that answeres here that have been answered are familiar to me.

If your solution is to say good luck you are going to need then i am defenetly at wrong place !
I pay account for this site becouse i tought here i would find answeres (and ask one) in much proffesional way than searching thrue google but you proved it wrong.
0
 
LVL 13

Expert Comment

by:AielloJ
Comment Utility
Cvijo123:

I was done with this post until I read your comments to acperkins.  Your personal attack on him or her was unwarranted and unprofessional.  I offerred my advice based upon my years in proper technical database design, knowledge of industry best practices, involvement on code quality and reusability committees in fortune 500 and government, and as a project manager from small to multimillion dollar projects.  Each time it was essentially rejected.  Every single response to your post advised against SELECT * for reasons varying from technical to best practices.  There isn't a single credible guide to database design that advocates SELECT *.  In fact, it is explicitly warned against for many reasons.  You stated you provided "additional info", which doesn't alter the fact that the underlying practice of using SELECT * is a bad one.  From what I've read, you aren't seeking a solution, but someone to concur with your current practices.  Your methods would not pass the code reviews in any of the places I've provided services to.

You rated yourself as advanced, but in my opinion, that self rating involves more than technical skill alone.  Knowledge on SQL alone is not sufficient in this day of hacking, SQL injection attacks, identity theft, etc.  It also requires studying the underlying concepts of proper database design, and the reasons certain practices are advised for or against.  Let's not forget about the lawyers that will have no tolerance for coding practices that do not comply with published industry standards and best practices should there be any failures of the software to perform.

Your post has been answered by several people whose names I recognize and respect the opinions of.  While I can't speak for acperkins, I believe the comment you are taking offense at was born out of frustration and not character.  I feel the same frustration when someone with many less years experience rejects sound advice, or does so without being able to or trying to understand the concepts being explained to them.  You wrote of having done your research, but there's apiece you missed.  Acperkins rank on this board is in the top 30 with points totalling over six million.  No one gets paid here, so anyone with that many points is either insane or very willing to help others.  I vote for willing to help, how about you?

As you stated, you pay for your acoount at this site to find answers in a professional way, and you received them.  You were offerred sound advice from serveral experts, you chose to reject it, and then cast aspersions at the character of one who finally got frustrated with the constant replys rejecting the advice given.  I believe an apology is in order.
0
 
LVL 5

Author Comment

by:Cvijo123
Comment Utility
@AielloJ: i really appriciate all experts help here and of course i noticed their suggestions and as u said i seek for more answeres to convince our ppl that Select * isnt good.
As i first start this past i was one in our company that always using explicit column names and i wanted this board to convince other colegue of mine to use same practice as mine. Answeres here wasnt "enough" for them so i pushed it more and ask for "mybe" more reasons that ppl can think of.

I used this board becouse i tought i can ask for more deep questions about some issue without problems wich i obviously cant.
If i am not setisfied witn answeres or mybe need more tought than 3 ppl i dont see big problem about asking more.
If some expert got frustrated as u said and "finally" with my rejecting advices answerd "good luck you are going to need it" why didnt he used "request attention" or skip this topic becouse it was his first answer without "very willing to help other" note. Of course i saw his rank and that's why i got upsed by answer.
Mybe i wasnt clear about what i am searching for here or some additional info i need to clarify or anything esle.
Anyway to stop this flame and wrong interpretation of my "rejecting" answers and my need to hear all reasons why not to use * i will close this becouse it cant go in good way anymore.

0
 
LVL 13

Accepted Solution

by:
AielloJ earned 250 total points
Comment Utility
Cvijo123:

It seems from your reply, that what you needed wasn't clear.  Sometimes what is written is misinterpreted for a number of reasons.  It appeared that you were the one advocating for the use of SELECT *.  Unfortunately, everyone interpreted it the same way.

You still have a legitimate question, and now that what you need has been clarified we can provide help that fits your issue.  I'm pretty certain that others will too.

I have personally been in your position as far as quality and standards.  You're obviously dealing with colleagues that don't have enough experience and shouldn't be in decision making positions.  Based on my similar past experiences, nothing will be "enough" to convince them.  Their minds are made up and not open to change.

You have much of what you need.  You just have to realize it.  You have several experts that warned of problems from the technical to the industry best practices.  You have the moderator who also gave an opinion and the fact that the larger DBA community is also in agreement.  The opinions are unanimous, for several reasons that SELECT * is bad.  If what has been presented so far hasn't convinced them, or at least made them willing to consider that your way is correct then you may be in the wrong place.  To successfully make your case you not only need to compile everything that has been given you here, but also research database design best practices and look for a compiled document of best practices.

Try Googling: database design best practice

There were hundreds of pages, some of which, will address this issue directly.  Your issue is sounding less technical and more office politics.  You have put the question out there and received unanimous support for your approach.  The number of responses isn't large, but they are unanimous.  They have also put it upon you to defend your point while not willing to do research to backup theirs.  If logic and reason are to be used to come up with the best solution, then THEY should be willing to do similar research looking for experts to concur with their methods.  I guarantee they will not find a single one on a forum of real experts.  You might also be able to take what you found to your department management and ask their help in making this decision in a logical manner.

If that all fails, maybe you just need to tell them what acperkins told you.  LOL  This post may have gotten old to the point that it won't catch the notice of anyone else.  If that is the case, then open a new post with the same title and make sure you state that you are trying to convince your colleagues that SELECT * is not a good thing.  For the deeper detailed answers you need, I would suggest asking for book names or on-line references that explicity warn against using SELECT *.

You have unanimous support for your point, they should be willing to try to do the same for theirs.  Otherwise they'll keep you chasing more and more information that will never be "enough".
0
 
LVL 5

Author Comment

by:Cvijo123
Comment Utility
Than you all for helping me on this question.
I split points becouse all the answeres was correct imo and made my collegues more close to understand why not to use *.
Our project is not very small (containing over 900 tables and more than 2500 SP's) so i asked for help here trying convencing that we change our way of making SP's  and i hope i am little closer now than i was before.

Specail thanks to Aiello for making additional effort (wich was probably painfull :) ) and explained in much better way that i did to my colleagues.

As i am still not at the end of my "project" to change something this was step that defenetly helped to start with.
One again my aplogy for pushing this for more details but i needed that for many reasons as i explained in my posts.

Tnx
0
 
LVL 5

Author Closing Comment

by:Cvijo123
Comment Utility
Tnx again for everyone trying to help in any way.
I appriciate efforts you guys did even with my pushing and asking for more wich i explained in last posts.
I hope you wont have this "marked" as bad experiance with my name becouse my goal for this question was simple.
Hope i will get your suggestion and answeres in future too !
0
 
LVL 13

Expert Comment

by:AielloJ
Comment Utility
Cvijo123:

I hope your determined effort to make sure your company does things the proper way is something your company values, especially on a project that size.  Sometimes despite our best efforts to convince people to do things the proper way they don't come around to our way of thinking.  I hope that's not the case in your company.  I'm certain that some of those webpages I put in my last post and books on database design you'll find all the proof you need.  Challenge those that don't agree to find documents supporting their views.

It wasn't painful to try to correct an unfortunate misunderstanding, and I'm glad I could contribute what I can.  Your sticking with it and apologizing for the misunderstanding is appreciated.  Time to quit apologizing and continue on with your "project".  There's a great bunch of people and talent here on EE.  I'm confident most will be willing to help anywhere they can now and in the future.  There is some wisdom in the sarcasm of a prior post.  Some people just insist on learning things the hard and EXPENSIVE way.  Just make sure that your management knows your position.  It will help down the road.  Until you find a website or book with what you need, try to keep a post open.  It's just a matter of time before someone here will have the name of a book or website that will have all the proofs you'll need.

Best of luck (sincerely)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now