Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Preventing usage of "SELECT *..." Expand / Collapse
Author
Message
Posted Thursday, November 5, 2009 5:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:52 AM
Points: 3, 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 ?
Post #814171
Posted Thursday, November 5, 2009 6:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:29 AM
Points: 23, Visits: 118
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.
Post #814181
Posted Thursday, November 5, 2009 6:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 1,625, Visits: 5,555
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!
Post #814183
Posted Thursday, November 5, 2009 6:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #814187
Posted Thursday, November 5, 2009 6:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 14, 2011 10:13 AM
Points: 25, 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.

Post #814188
Posted Thursday, November 5, 2009 6:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #814192
Posted Thursday, November 5, 2009 6:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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. ..."
Post #814201
Posted Thursday, November 5, 2009 6:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 465, Visits: 1,230
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
Post #814203
Posted Thursday, November 5, 2009 7:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 14, 2011 10:13 AM
Points: 25, 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.
Post #814212
Posted Thursday, November 5, 2009 7:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 1:41 AM
Points: 16, Visits: 366
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?"
Post #814219
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse