Preventing usage of "SELECT *..."

Question

Preventing usage of "SELECT *..."

Viewing 15 posts - 16 through 30 (of 140 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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.

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)

TravisDBA SSCoach Points: 15780 More actions · Answer 2

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. 🙂

"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 Ten Centuries Points: 1013 More actions · Answer 3

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 Say Hey Kid Points: 669 More actions · Answer 4

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 Old Hand Points: 301 More actions · Answer 5

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?"

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

As others have said, there are more uses for SQL than an application returning specific rowsets.

SELECT * when you only want some of the columns is indeed bad.

SELECT * when you coded your app so poorly that it breaks when columns are added is idiotic.

SELECT * when you're investigating with hand-written, one-off SQL is tremendously useful (it makes it easier for humans to spot patterns).

SELECT * when you actually need all 212 columns (or even if you need 200 of the 212 columns, the 12 columns you don't need are small, and so on) is not bad, particularly if you might have a network traffic issue sending a couple hundred (long) column names in your SQL.

Yes, preventing SELECT * is an interesting exercise, but be careful. You're using global enforcement instead of education to prevent and analysis to find.

Al-279884 Old Hand Points: 383 More actions · Answer 7

Interesting article, but I do not think the proposed solution will cure the "lazy developer" syndrome. That's because the real problem are bad development practices, not the SELECT * FROM ... statement itself. If you do not fix the problem directly and use work-arounds you will probably run into it again. Let say I am a lazy developer that used SELECT * FROM before, and now I cannot do it anymore. Is it going to make me use better practices? No, I will find the next comfortable solution: drag the [Columns] node into Query Editor...

TravisDBA SSCoach Points: 15780 More actions · Answer 8

princess.lipscomb (11/5/2009)
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.

Stopping select * is not the correct approach for protecting users from possibly reading financial data on tables with a select *. Instead, using TDE and EncryptByKey() function for encrypting financial sensitive columns is the correct approach there IMHO. I think you may have misunderstood what I meant by KISS. A large part of the KISS approach is figuring out the appropriate solution for the problem. Not just simply doing what is convenient. 🙂

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

jcraddock Default port Points: 1498 More actions · Answer 9

To paraphrase Lawrence from Office Space, "I believe you'd get your axx kicked for doin' something like that, man"

Select * is very useful when you need to do quick looks for rooting out problems. It is no reason to handicap your guys.

JF1081 SSC Eights! Points: 982 More actions · Answer 10

Al-279884 (11/5/2009)
... I will find the next comfortable solution: drag the [Columns] node into Query Editor...

I never knew this was possible. I do not use SQL as much as I would like but I do know that SELECT * can be a pain when I only want a few columns. But there are times when I need several columns and still do not like using *. It would be pretty easy to just drag the node and delete the few columns I do not want then do a find and replace on the ', ' to replace it with ', [tblName].' if I had multiple tables in a JOIN.

Also, we have some pretty long table and column names so dragging the table or even a single column is fast and reduces spelling errors.

Thanks!

Kit G SSCrazy Points: 2888 More actions · Answer 11

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 ?

Using SELECT * / Omitting the Column List is a good article on why "Select *..." is a bad habit and why it should be avoided in your production code. "Select *" does have its uses in developing queries (which is why banning it entirely might create more problems on the development end than it solves on the production end). While I am new to T-SQL and pretty much only deal in ad-hoc queries at the moment, I was taught the pitfalls of "SELECT *" or "INSERT *" in code that's going to be regularly used. To many professionals, the use of "*" is a sign of sloppy programming and a lack of understanding.

While it's an interesting solution, I don't think it has much practical value as stated. But, I did learn that you can deny access to columns which I didn't know before, I thought access denial stopped at the table level, not that you could go down to a column level. Might have some application in preventing someone from accessing the column storing credit card numbers or some other critical data that needs to be secured, giving the data another layer of security.

Good article, I enjoyed reading it and learned something new.

-- Kit

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

Kit G (11/5/2009)
To many professionals, the use of "*" is a sign of sloppy programming and a lack of understanding.

I have to agree, I view it as sloppy, there are never cases where I would want to do that. Now I can think of a couple cases where I might not list the insert field list, but ALL of them are for temp tables that I might have created within my stored procedure and never something outside. Still even in those cases I usually still list them out, if for nothing but clarity..

Good article, well thought out, not very practical though in the real world.. But it might lead to other solutions.. I still prefer to use checkin policies, code reviews, and code searches..

CEWII

shotman Right there with Babe Points: 769 More actions · Answer 13

That was a good example with a twist in it. Still looking for a more viable solution 😉

jts2013 Hall of Fame Points: 3226 More actions · Answer 14

Certainly an interesting idea, but as some others have said I woul dhope the DBA would have the authority to vet any code gpiong into the live environment.

sjsubscribe SSCrazy Points: 2080 More actions · Answer 15

Yet another tool to be tucked away for use when appropriate.

Another tool along these lines would be to expand select statement itself to allow for an exclusion set of columns. For example:

select all except (columnX, columnY, columnZ) from Table.....

This could have other uses as well and reduce the need for some uses of select *