Select * vs Select column names ?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jsonnenvzla2
At Enterprise manager, right click on the table to obtain a complete detailed sql statement for the record, simply cut and paste
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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 ?
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 ?
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
"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.
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.
ASKER
@Brandom it is feature for SQL 2005+ and we are still on SQL 2000 :(
Ugh. Sorry to hear that. lol. I remember those times.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
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.
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.
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.
ASKER
@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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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 !
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 !
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)
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)