Preventing usage of "SELECT *..."

Question

Post reply

Preventing usage of "SELECT *..."

Raghuram (AJ)

Ten Centuries

Points: 1214
More actions
November 4, 2009 at 9:44 pm

#227704

Comments posted to this topic are about the item Preventing usage of "SELECT *..."
The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner

Viewing 15 posts - 1 through 15 (of 140 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Interesting but i feel its not practical to add dummy columns to tables. 🙂

"Keep Trying"

Scott.Deagan Grasshopper Points: 23 More actions · Answer 2

Another solution that works well in most situations is to simply avoid allowing users to execute SQL statements at all - ensure that all access to tables is performed via stored procedures.

gary-915906 Valued Member Points: 74 More actions · Answer 3

We have similiar issues here with the use of *, espically with young developers.

As all our DB projects are under source control, ive been able to control checking in SQL containing * using Code Analysis and Check in policies.

Regards

Gary Howlett

www.garyhowlett.co.uk

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 4

Indeed a good article but practically difficult to use.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

In interesting solution, for a not so real problem, as any decent organisation has standards in place and will ensure that all code conforms to the corporate standards.

If not the developer resonsible needs to rewrite the code in line with the standards, they eventually learn that they cant write Selcet * as they have to do double the work.

Besides with the 2008 GDR, you can enforce these at a code entry level.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

John Muir SSC Enthusiast Points: 124 More actions · Answer 6

I think you can still use 'select anyfield, tblname.* from tblname' though.

Michael Lysons SSCertifiable Points: 6492 More actions · Answer 7

I love posts like this. Sure, the specific example used may not be practical, but I just like the thinking behind coming up with the solution. Often that in itself can open up other avenues of thought that would otherwise have remained hidden.

Ponte SSC-Addicted Points: 448 More actions · Answer 8

May not be practical.

May not be standard.

But it is a good/smart solution.

🙂

Regards.

Fernando Ponte
factdata.com.br

John Muir SSC Enthusiast Points: 124 More actions · Answer 9

But it does not solve the problem when I can use

select tblname.* from tblname

with the same result.

sorte.orm SSC Rookie Points: 25 More actions · Answer 10

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 SSChasing Mays Points: 611 More actions · Answer 11

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 SSCoach Points: 15320 More actions · Answer 12

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 SSC Guru Points: 1004406 More actions · Answer 13

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.

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)

princess.lipscomb Say Hey Kid Points: 669 More actions · Answer 14

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.