SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Preventing usage of "SELECT *..."


Preventing usage of "SELECT *..."

Author
Message
sorte.orm
sorte.orm
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 14
Exactly why is "select * " not a good idea? I searched for articles with that tag but came up empty? I can't really see any reason not to do so, as long as you prefix with tablenames/aliases. And by the term "users" does the article author mean developers or end users ?
jezman
jezman
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 129
A good example of something that would be better solved through education. The cost in usability and complication just doesn't seem worth it. The time sorting out the occasional problem that might arise from having "SELECT *..." is surely a smaller cost than maintaining and living with the above solution.

"SELECT *.." is always useful when exploring databases, I think most DBAs/Developers would find it very painful to not be able to have a quick look at the data!

Edit:

sorte.orm (11/5/2009)
Exactly why is "select * " not a good idea


The point is that "*" changes. This can be bad if other processes expect a fixed dataset. Adding a field to a table suddenly breaks x, y and z. But like I said above, the times this has tripped me up and the time it takes to resolve are so minimal that it is not really a worry. Although perhaps it depends on the nature of your system? And as below, using * also sends more data than may be necessary.
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2513 Visits: 6232
If you're not actually uising all the columns it also means you're returning a lot more data to the calling application than it actually needs. That's not so much of an issue if you're returning half-a-dozen rows, but if you're retrieving thousands of them, all those extra bytes add up to a lot greater network load on your server!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84803 Visits: 41069
sorte.orm (11/5/2009)
Exactly why is "select * " not a good idea? I searched for articles with that tag but came up empty? I can't really see any reason not to do so, as long as you prefix with tablenames/aliases. And by the term "users" does the article author mean developers or end users ?


SELECT * is pretty tough on correct index usage and violates the performance notion of returning only what you need (ie: be kind to the "pipe"). There are also some reasons from the GUI side of the house in the following short article...

http://www.adopenstatic.com/FAQ/selectstarisbad.asp

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
princess.lipscomb
princess.lipscomb
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 169
This example is good for the purpose of changing the mindset of many developers -- continuing the laziness of not putting column names and only using * for everything. Which does slow down query time because the * requires a second lookup for all the column names before it can retrieve the data.

As a risk to security of hackers that like to "Select *", this could slow them down, because they would need to get the table columns in order to retrieve more info.

The paradigm of developers would have to change drastically for this to be implemented but I will suggest such an option to see how it is received.

It might not seem plausible right now but wait a while and some of us will make that leap to stop all the select statements when possible. Not if you can stop the tablename.* from happening, this might be received more favorably.

You have provided some valuable information and please do not stop because it was not as well received as it should have been.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84803 Visits: 41069
I can't take anything away from the article... the author did a nice job on it. Well done.

I do, however, agree with some of the others... SELECT * is VERY useful for ease of discovery during development and troubleshooting of code. Making SELECT * impossible to use would be frustrating to everyone using a production system and would offer no help on development systems where developers typically have at least DBO and possibly SA privs (as they probably should).

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TravisDBA
TravisDBA
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1950 Visits: 3069
It is articles like this that cause me to pause and remember what a very smart mentor of mine once said when I first got into Database Administration years ago. "Always remember Travis, the word KISS (Keep It Simple Stupid). Don't overthink or overdevelop the solution. You will usually cause more issues for yourself or your system in the long run. " Words to live by, definitely. Although this is kind of a "neat" solution, it is way too much work to go to IMHO. Instead, just simply teach your Developer/Business Analyst staff from the start the simple paradigm to just "Retrieve what you need" from your tables, no more no less. Also, be very careful with using DENY on any table in your user databases. I have seen people in the past really screw things up bad using this because DENY overrides everything when it comes to permissions. Don't try to get "cute" with your production databases. It might come back to bite you real bad. Now with that said, I don't have a problem with creative thinking, but always remember the bottom line, keep it simple. Smile

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Brian Kukowski
Brian Kukowski
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1575
sorte.orm (11/5/2009)
Exactly why is "select * " not a good idea?

'SELECT *' pretty much guarantees that SQL Server won't be able to take advantage of a potential covering index (one that includes all fields in the SELECT), resulting in higher-overhead access to the base table

Brian Kukowski
princess.lipscomb
princess.lipscomb
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 169
Be carefull with the KISS approach. That is how we got into the SELECT * mentality in the first place.

Without coding standards, developers will code the way they have been instructed or brought from another position.

It may not seem like a pausible solution for some but it is a way to prove the point of how to stop Select * from happening especially when you have financial information stored on the machine.

Thinking outside the box is not bad but maybe one day it might be useful for a particular situation. You do not do all the same coding for every function in your production db unless you have no exceptions to your functionality.
glenn brown
glenn brown
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 490
I agree I do not think it is practical to add dummy columns and chaneg the metadata structure of your database just to stop people from writing "select *". How bout "just picking it up in peer review?"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search