February 11, 2011 at 12:10 pm
jimbobmcgee (2/11/2011)
how about using this approach on a test server only, as part of a rigorous test regime; enforcement is not wrong if you are trying to move people away from an approach, to a better one.
The trouble is that in many cases what you will be doing is moving people from a good approach to a worse one.
I'm not going to waste space explaining that again as I did that already in this thread 13 months ago; if you want to look at it, see http://qa.sqlservercentral.com/Forums/FindPost845467.aspx.
Tom
February 11, 2011 at 12:48 pm
Jason-299789 (11/5/2009)
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.
It must be nice in your world:) I imagine most DBAs live in a less ideal situation where the legacy code base is inherited from 2 or 3 generations of predecessors, the company is pushing for new features not refactoring, the users are pushing for performance, there are few (if any) documented standards, .....
February 11, 2011 at 1:57 pm
Pretty interesting read. It conforms perfectly with the zealous DBA's I already know... lock it down first, ask questions later... then when the developers can't figure out why the size of the table doesn't match the size of the data when they're asked to help audit performance... Tada, we can't see the whole table. Or when we have to ask that relationships/constraints be removed because it's overnormalized, etc., etc.
Again, I think the article is neat, just pointing out, for instance here, that instead of using VARCHAR, you've used CHAR for the unselectable and presumably NULL column, thus increasing overhead.
The articles don't have to be perfect code, but when it's essentially in response to imperfect development code, it better be darn close. It's been a typical experience of mine that DBAs think they own the data. 99% of the time they don't even know what's in the data, so enforcing referential integrity, and stuff like this should always be done in conjunction with development. Sorry, but DBAs serve development, not the other way around.
February 11, 2011 at 2:28 pm
I started my career as dba, and in the mean time discovered that my true calling is in fact development.
Now if my dba disables select * from any of my tables we are going to have us a problem...
Yes I know using it for instance in a view cause trouble, so i know where to use it and not to use it.
Scenario:
Clients pops around at my desk and ask me to quickly have a look at the latest sale order that was placed since he believes there might be a problem with the stock allocations.
Simple:
Select
*
From
SaleOrder so
Inner Join SaleOrderLine sol
on so.SaleOrderID = sol.SaleOrderID
[F5]
Quick, simple, and it allows me to either spot the problem in 15 seconds, or prove to the client it is actually working correctly.
Now disable this function and you also disable these quick checking queries that we all run directly on the db.
rather than spending time implementing something like this, show the developers / users WHY you want them to select column1, column2, etc instead of select *
create a view with select * from and then edit the table afterwards. Show them. If you find a developer/user is not listening, well a little bit of reprimanding as never killed any one. If you cannot do that, simply report to his senior to address the issue.
Just my 2c
Now leave my tables alone 😀
February 11, 2011 at 2:34 pm
Jeff Moden (11/5/2009)
sorte.orm (11/5/2009)
Exactly why is "select * " not a good idea?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...
I agree with everyone that Select * is not good.
I also have to say the extra data weight and processor usage placed by adding this solution to a database would be worse.
The best solutions would be checking for and blocking any code with "SELECT *" or "SELECT .*" in it from being published to production.
If you don't have that level of control over your production application code Select * should not be what you are worried about.
February 11, 2011 at 2:39 pm
ZA_Crafty (2/11/2011)
I started my career as dba, and in the mean time discovered that my true calling is in fact development.Now if my dba disables select * from any of my tables we are going to have us a problem...
Yes I know using it for instance in a view cause trouble, so i know where to use it and not to use it.
Scenario:
Clients pops around at my desk and ask me to quickly have a look at the latest sale order that was placed since he believes there might be a problem with the stock allocations.
Simple:
Select
*
From
SaleOrder so
Inner Join SaleOrderLine sol
on so.SaleOrderID = sol.SaleOrderID
[F5]
Quick, simple, and it allows me to either spot the problem in 15 seconds, or prove to the client it is actually working correctly.
Now disable this function and you also disable these quick checking queries that we all run directly on the db.
rather than spending time implementing something like this, show the developers / users WHY you want them to select column1, column2, etc instead of select *
create a view with select * from and then edit the table afterwards. Show them. If you find a developer/user is not listening, well a little bit of reprimanding as never killed any one. If you cannot do that, simply report to his senior to address the issue.
Just my 2c
Now leave my tables alone 😀
I see your point and agree on the no ban option. However you can always right-click in smss and script the select / insert statements which only takes 5 seconds anyways. And you won't have to type anything!
February 11, 2011 at 2:49 pm
I have a practical example of where "Select *" fails:
Our product has a VIEW called "Customerz" defined as "SELECT * FROM CustomerTable" (view & table name changed to protect the guilty). This view is used in SSRS as part of a data model (and due to permissions settings / rules presented by the powers that be, we have to use the view in the data model)
I added a New column to CustomerTable, then checked the view. It still listed only the original 5 columns for CustomerTable. The only way I could force the view to display all 6 columns (original 5 + the one I added to the base table) was to ALTER the view.
Fortunately, I was able to find the problem quickly, however, I wonder how many other "gems" of SELECT * in views will bite us later on.
This is a very good example of how this campaign against SELECT * sometimes turns out into a witch-hunt (just like the infamous campaign against GOTO operator in many programming languages). This is a very good example where the blame is not being placed where it actually belongs.
Ok, let's say that your view did not use asterisk from the very beginning. Let's say your view was defined this way:
select Column1, Column2, ... Column5 from CustomerTable
Now, you add Column6 to the CustomerTable and want your view to include it, too. Well, guess what? You still have to alter your view to make that happen. Worse yet, you also have to actually modify it first - by adding that pesky new column to the list. How is that better than just altering without having to modify anything? Using asterisk actually saves you time and leaves less opportunities for a mistake. How can that be bad thing?
The problem here is not with using asterisk. The problem here is that, in spite of having an asterisk there, the RDBMS is too primitive to realize that it could've (and IMHO should've) updated the view automatically. After all, asterisk is not there just as a shortcut; it has certain semantics: namely, a request to include all columns from the given table/rowset. And the problem is that this semantics is not respected by RDBMS when the schema of the table/rowset changes. The problem is that it still remains a programmer's responsibility to respect it.
IMHO, SELECT * - just like about any other language element - can be extremely useful and can be easily abused. As many readers already mentioned, you just need to educate your team about what is a good use and what is an abuse. And any attempt to prohibit use of SELECT * across the board... well, IMHO is a total waste of time and creative resources.
February 11, 2011 at 3:02 pm
yarik (2/11/2011)
This is a very good example of how this campaign against SELECT * sometimes turns out into a witch-hunt (just like the infamous campaign against GOTO operator in many programming languages). This is a very good example where the blame is not being placed where it actually belongs.
I would have to agree with your comparason, but not your attitude about it.
Devorak proved that GOTO statements caused more performance issues than any other mis-used keyword in a high level language.
IMHO: "SELECT *" is the SQL equivalent to GOTO and I think treating it like Devorak treated GOTO is healthy and sane. Not a witch hunt, but more like replacing bad COBAL with good RGP. :hehe:
February 11, 2011 at 3:09 pm
There are also some reasons from the GUI side of the house in the following short article...
YG&LF!! I started reading the stuff at that URL, and hit the second reason given for disliking SELECT *
Reason 2 is that it makes it more difficult to work by column offset instead of column name.
Working by column offset is real crime against safe and sensible coding, far worse that SELECT * would be even if all the calumnies against it actually made sense!
edit: and then I hit reason 3: more of the same nonsense;
and reason 4 - apparently not using SELECT * will ensure that all your column lengths will fit into the spaces provided!
and to cap it all: reason 5: if you let your ASP hand in any old SQL instead of using stored procedures (that's disgracefully bad practise) and scatter your select statements all around so that they are not defined anywhere near where they are used (so functions/subroutines or indeed any form of code structuring in ASPs are obviously not your stick, even if the ASP is 1000 lines or more long as suggested - this too is pretty awful practise) then using SECLECT * may exarcebate your self-inflected difficulties.
Then it ends with reason 6: "maybe it causes this pronblem, but I can;t find any evidence that it does". ANd this farrago or utter tripe is supposed to persuade me that I should never, under any circumstances, use SELECT *?
I haven't attacked reason 1, but I could argue that SELECT <column list> is bad thing in stored procedures because it increases the space required to cache syscomments (and in the even syscomments won't all fit into the available space it increases disc traffic too) (and maybe the increased SP size and also the increased size of any ad hoc SQL queries means compilation/recompilation takes longer) and these performance losses offset the suggested performance gain in reason 1.
And the fact remains that there is plenty of code which wants all the columns, and will still want all the columns when extra columns are added, and needs to be written so that it doesn't need to change every time a new column is added, and the only sensible way to write such code is to use SELECT * (the only other way is to query catalogue views to get the column set, construct your query as a string, and use EXECUTE to execute that string, and I don't regard that as sensible in the majority of cases).
Tom
February 11, 2011 at 3:38 pm
Tom.Thomson (2/11/2011)
There are also some reasons from the GUI side of the house in the following short article...
YG&LF!! I started reading the stuff at that URL, and hit the second reason given for disliking SELECT *
Care to translate?? YG&LJF
February 11, 2011 at 4:00 pm
Interesting approach and I am sure it would work in some cases.
We have to be careful about banning all "select *" and judiciously choose which tables we would implement something like this. I'm not advocating that select * is necessarily good, just advocating sound judgment and that a blanket approach is not necessarily the wisest approach.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 11, 2011 at 4:27 pm
Seems this solution is a sign of missing governance or process in the organization, and can slow down the people affected, including the DBA the developer and everybody else. Instead try implementing something called code standards and reviews, that way a SELECT * will never end up in production.
February 11, 2011 at 4:53 pm
As others have noted it is good to see the thought that goes into solutions like this. However, I have a major objection 🙂
SELECT * is valid SQL. As such it is part of a toolset. Now, if you don't want people using the toolset, then put the toolbox out of reach.
If you find that people are misusing SELECT *, in my opinion, the solution is NOT to castrate the toolset, the solution is to educate people in using it.
If you give a power drill to a 6 year old, don't be surprised if there are holes in the furniture (and maybe some blood on the carpet). Teach the kid how to use a drill safely and you have no problem.
This "Nanny knows best" attitude permeates many levels of IT, not just SQL. I've never understood why companies spend thousands on tools and software but won't spend time or money to ensure people know how to use them properly.
I see no problem with users using SELECT * (provided they understand the implications.) If performance is degraded then we address improving it, we don't lower the level of service we are claiming to provide.
Like so much in life, the answer here is in education. The innovation and imagination that has provided the suggested solution can then be applied to real problems that affect the company, and everybody wins.
February 11, 2011 at 5:42 pm
I didn't have time to read all the posts but here are a couple more I didn't see in the first couple pages:
1. If a column is added to the table, likely your solution will go boom as it has an additional column to deal with it didn't know existed when you developed it.
2. If the ordinal position of columns is changed, SELECT * will make your solution go boom.
If you need to look at a table definition, script it out. I assume people are using SSMS when they are looking at a new system? I use SELECT TOP 1000 quite often to do this. It gives you a nice list of bracketed col names in ordinal asc.
-------------------------------------------------------------------------------------------------
My SQL Server Blog
February 11, 2011 at 6:09 pm
Ninja's_RGR'us (2/11/2011)
Care to translate?? YG&LJF
Ye Gods & Little Fishes
(A right pondian exclamation of amazement, now somewhat outdated)
The J was a typo - I must have edited too slowly if you saw it.
Tom
Viewing 15 posts - 106 through 120 (of 140 total)
You must be logged in to reply to this topic. Login to reply