InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:32:38 GMT20http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxHello,With SQL Server 2008 (at least) there is a problem [code="vb"]DENY SELECT ON OBJECT::MA_TABLE(DUMMY_COLUMN) TO User1;[/code]work like a charm (return an error) when I try to make a "SELECT *"but in a stored procedure I can do a "select *"[code="other"]CREATE PROCEDURE [dbo].[myProc]ASBEGINSELECT * FROM MA_TABLE;END[/code]Why ? I need to get an error when I execute a stored procedure which contains a "SELECT *"RegardsTue, 12 Apr 2011 07:53:19 GMTsebastien.delestrehttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]cphite (2/14/2011)[/b][hr]Interesting solution, but honestly I think a far better solution is to train your developers to avoid doing things like SELECT * in production code, along with having a good code review process. Personally, I find SELECT * to be extremely useful when starting to build a query, especially when a database has less than desirable table and column names. [/quote]I fully agree with you. When starting to create a query I always use select * BUT I never use * in an aggregate function like count(*) etc. To me it is totally wrong to do that, I rather use a column in my table.Wed, 16 Feb 2011 05:40:38 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxThanks.When I posted the question I had not read pages 10 - 14 that somewhat covered the reasons for not using it. Your answer got to the heart of the matter. Really goes to knowing the data, # columns ,etc as well. I do see the use of it when in preproduction but as a matter of routine use in production , views yada yada yada,yea not a great idea.thanks again for the responseTue, 15 Feb 2011 09:35:24 GMTajolson1964http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]ajolson1964 (2/15/2011)[/b][hr]So as a young developer myslef. What are the pitfalls of using the Select * ?[/quote]There is one real pitfall and one maybe pitfall:1) the real pitfall: if a lot of the columns aren't needed, you are passing a lot of data around; if this is going between a server and a client over a limited bandwidth network that can be a pain.2) the maybe pitfall: if some app developer has written code that breaks if it gets more columns than it expects, using select * means that schema changes will break the app if an extra column has been added to the view or table that the app uses. My view is that in almost every case an app developer who writes such code is incompetent, and if he can do things like that he's probably going to do other stupid and dangerous things too, so maybe this isn't a pitfall but a good detector of bad app programming.Select * should NEVER be used in an app, because of 1 above.It should ALWAYS be used in a stored procedure or view whose function is to deliver ALL the available data about some object, since if it isn't used that SP or view has to be rewritten when the schema changes; if it is used the SQL Server optimiser will automatically recompile an SP next time it is used (no development action required) and a view has to be recompiled (which doesn't happen automatically, unfortunately) but does not have to be rewritten.Tue, 15 Feb 2011 09:21:33 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxSo as a young developer myslef. What are the pitfalls of using the Select * ?Tue, 15 Feb 2011 09:04:59 GMTajolson1964http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]Ninja's_RGR'us (2/13/2011)[/b][hr][quote][b]Geoff-577403 (2/13/2011)[/b][hr]This can be done a lot easier with SQL 2008 Policy Management Feature. :smooooth:[/quote]How?[/quote]Isn't it interesting how the order of two words can change the whole meaning of a question ..... "[u]Can [/u]this be done a lot easier with SQL 2008 Policy Management Feature"- Not to worry, I don't think it can.:blush:Mon, 14 Feb 2011 20:20:18 GMTGeoff-577403http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxInteresting solution, but honestly I think a far better solution is to train your developers to avoid doing things like SELECT * in production code, along with having a good code review process. Personally, I find SELECT * to be extremely useful when starting to build a query, especially when a database has less than desirable table and column names. Mon, 14 Feb 2011 15:29:33 GMTcphitehttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxI believe there's a very large difference between preventing the use of SELECT * FROM.. in a production environment and using it in a development environment.For development it's almost a requirement to be able to see ALL the data at times. In dev and troubleshooting cases it's got quite valid uses.For production code it's sloppy and inefficient. If you can't do a COUNT([field]) aggregrate because of NULL values and/or empty columns then you need a key that isn't blank or NULL, imo.If you're doing decent UAT then any SELECT * that slipped by will be caught with testing. That's the point of testing.Regarding the situation of things breaking because of schema changes shouldn't there be code review if the underlying database REMOVES columns? Adding columns shouldn't have any effect on code with fields listed since it won't even see them. And if you need to use the columns then you're into change management anyway.I don't see this being used in anything I'm doing soon but it's a trick I'll keep in mind for those situations where we want to kick lazy developers in the butt and have them write cleaner code.Mon, 14 Feb 2011 12:39:59 GMTJustMariehttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]amenjonathan (2/14/2011)[/b][hr][quote][b]Tom.Thomson (2/11/2011)[/b][hr][quote][b]amenjonathan (2/11/2011)[/b][hr]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.[/quote]Only if you wrote disgracefully bad code in the first place. Get rid of the disgracefully bad code and SELECT * will work perfectly with your good code.[quote]2. If the ordinal position of columns is changed, SELECT * will make your solution go boom.[/quote]Only if your code is unbelievably execrable. Fix the really silly dependencies on column order, and select * does you no harm.[quote]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.[/quote]I can't even understand what you are getting at here. Secect TOP 1000 * is obviously what you mean, but you appear to be claiming that that *, which you often use, is evil, but then this * is benign? But it is plain to see that this SELECT TOP 1000 is not benign, what's actually evil here is your use of 1000 where 1 is enough if all you want is the column names (or preferably SELECT TOP 1 * WHERE 1=0 ,if there are any long columns, and you don't keep an SP to query the catalog views for the columns of a particular table on hand).[/quote]I'm not a code developer, so I can't argue the specifics of 1 and 2, but in my limited exposure to net developers, every time they've used SELECT * changing the table definition broke their code. Maybe they're horrible programmers. Not sure. Still it makes my gut reaction 'don't do it'.For the scripting, yes you don't know what I'm talking about. I'm talking about right clicking a table in SSMS object explorer and choosing either SELECT TOP 1000 or CREATE TABLE script. Choosing either will cause SSMS to return a nice list of column names in ordinal asc, which can then be used instead of SELECT * for those who would rather not type out all the col names (also avoids typos)./****** Script for SelectTopNRows command from SSMS ******/SELECT TOP 1000 [SSISConfigurationID] ,[ConfigurationFilter] ,[ConfiguredValue] ,[PackagePath] ,[ConfiguredValueType] FROM [xxxxxx].[dbo].[SSISConfiguration][/quote]i've seen this happen one timesomeone creates a publication by accident that also creates a GUID column on the table. forgot the name, but i think it's called updatable publication or subscription. in one of the apps there is a select * with a where condition that returns one row or so of data. the app breaks. lots of people are up past 2am fixing this since critical apps are downMon, 14 Feb 2011 12:09:31 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxThanks for the explaination. I talked to a net programming buddy of mine here, and he said to always use the col index, so I'll have to show him your explaination and see what he says (not saying what you say is wrong, because to me that sounds correct).Glad we can both teach each other something!Another way to add a layer between app dev and the db is to present the app with views instead of the tables themselves. This gives the db team a lot more wiggle room with changing the underlying schema, even if temporarily until the app code can change (if needed).Mon, 14 Feb 2011 11:28:37 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]amenjonathan (2/14/2011)[/b][hr][quote]I'm not a code developer, so I can't argue the specifics of 1 and 2, but in my limited exposure to net developers, every time they've used SELECT * changing the table definition broke their code. Maybe they're horrible programmers. Not sure. Still it makes my gut reaction 'don't do it'.[/quote]Put it this way: a net developer should be taught never to use database column numbers - the only acceptable way of identifying a column is by its name (that's both a very strong rule of system design and development and a key property of the relational model - in relational algebra a row is a map, not a vector). Anyway, in a good development shop the first use of column numbers causes some mentoring and training, the second causes a fairly severe ticking off, the third causes a written warning, and the fourth is a very serious offense, possibly a sacking offence; the reason for this is that the use of column numbers effectivel freezes the schema, and makes it impossible for the database people ever to change anything without entailing an expensive application rewrite. In fact net programmers shouldn't even be able to access tables directly in their code, only call stored procedures and maybe (but preferably not) select from views since building detailed knowledge of the schema into the application program can freeze the schema pretty thoroughly too.[quote]For the scripting, yes you don't know what I'm talking about. I'm talking about right clicking a table in SSMS object explorer and choosing either SELECT TOP 1000 or CREATE TABLE script. Choosing either will cause SSMS to return a nice list of column names in ordinal asc, which can then be used instead of SELECT * for those who would rather not type out all the col names (also avoids typos).[/quote]Oh, I misunderstood - I thought you were talking about writing a script beginning "select top 1000" to use directly rather than using the built in scripting to generate a script you could cut the useful bit from to use elsewhere.Mon, 14 Feb 2011 11:01:42 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxNice article, thanks for posting. Good discussion starter as well. ;)Mon, 14 Feb 2011 10:05:14 GMTAdam Sottosantihttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxI feel this would be practical in certain situations, especially in the ever changing and dynamic code we like to implement.Good to know someone has an idea on how to regulate the usage of select *.:-)Thank youMon, 14 Feb 2011 09:45:59 GMTjwbart06http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]Tom.Thomson (2/11/2011)[/b][hr][quote][b]amenjonathan (2/11/2011)[/b][hr]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.[/quote]Only if you wrote disgracefully bad code in the first place. Get rid of the disgracefully bad code and SELECT * will work perfectly with your good code.[quote]2. If the ordinal position of columns is changed, SELECT * will make your solution go boom.[/quote]Only if your code is unbelievably execrable. Fix the really silly dependencies on column order, and select * does you no harm.[quote]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.[/quote]I can't even understand what you are getting at here. Secect TOP 1000 * is obviously what you mean, but you appear to be claiming that that *, which you often use, is evil, but then this * is benign? But it is plain to see that this SELECT TOP 1000 is not benign, what's actually evil here is your use of 1000 where 1 is enough if all you want is the column names (or preferably SELECT TOP 1 * WHERE 1=0 ,if there are any long columns, and you don't keep an SP to query the catalog views for the columns of a particular table on hand).[/quote]I'm not a code developer, so I can't argue the specifics of 1 and 2, but in my limited exposure to net developers, every time they've used SELECT * changing the table definition broke their code. Maybe they're horrible programmers. Not sure. Still it makes my gut reaction 'don't do it'.For the scripting, yes you don't know what I'm talking about. I'm talking about right clicking a table in SSMS object explorer and choosing either SELECT TOP 1000 or CREATE TABLE script. Choosing either will cause SSMS to return a nice list of column names in ordinal asc, which can then be used instead of SELECT * for those who would rather not type out all the col names (also avoids typos)./****** Script for SelectTopNRows command from SSMS ******/SELECT TOP 1000 [SSISConfigurationID] ,[ConfigurationFilter] ,[ConfiguredValue] ,[PackagePath] ,[ConfiguredValueType] FROM [xxxxxx].[dbo].[SSISConfiguration]Mon, 14 Feb 2011 09:18:24 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxHi Raghuraj This is abhishek ............its really useful article you published about preventing usage of "Select *".I just want to know can apply the process in same way for Insert/Update/delete.RegardsAbhishekSun, 13 Feb 2011 23:37:53 GMTabhishek_parihar1http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]Geoff-577403 (2/13/2011)[/b][hr]This can be done a lot easier with SQL 2008 Policy Management Feature. :smooooth:[/quote]How?Sun, 13 Feb 2011 07:01:51 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxThis can be done a lot easier with SQL 2008 Policy Management Feature. :smooooth:Sun, 13 Feb 2011 06:19:00 GMTGeoff-577403http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxNice article, thanksIulianSat, 12 Feb 2011 05:35:21 GMTIulian -207023http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxThanks for the article, I like the idea and will keep it in my toolbox. But it will not solve the 'select *' problem because, as several people have already indicated, there is no 'select *' problem. There is not even a problem of returning too many columns. For example, in many situations that I have seen, returning a minimal set of columns will violate a fundamental relational principal: it will produce a relation (i.e. data set) without a key. If that's deliberate then fine. But not if it is the result of blindly trying to deliver a silver bullet because then the minimalistic approach will simply be a design flaw waiting to be exposed. In a controlled environment the cost of fixing such a flaw can easily outweigh the "benefit" of not including some (obvious) extra columns in the first place. The real problem then, as others have pointed out, is 'lazy developers' that do not think hard enough about what columns should be retrieved. There are no silver bullets to fix that, not in the real world.Fri, 11 Feb 2011 23:56:32 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxIt is a good trick and could be difficult to implement - RMJ:-DFri, 11 Feb 2011 23:03:34 GMTR. M. Josephhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]amenjonathan (2/11/2011)[/b][hr]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.[/quote]Only if you wrote disgracefully bad code in the first place. Get rid of the disgracefully bad code and SELECT * will work perfectly with your good code.[quote]2. If the ordinal position of columns is changed, SELECT * will make your solution go boom.[/quote]Only if your code is unbelievably execrable. Fix the really silly dependencies on column order, and select * does you no harm.[quote]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.[/quote]I can't even understand what you are getting at here. Secect TOP 1000 * is obviously what you mean, but you appear to be claiming that that *, which you often use, is evil, but then this * is benign? But it is plain to see that this SELECT TOP 1000 is not benign, what's actually evil here is your use of 1000 where 1 is enough if all you want is the column names (or preferably SELECT TOP 1 * WHERE 1=0 ,if there are any long columns, and you don't keep an SP to query the catalog views for the columns of a particular table on hand).Fri, 11 Feb 2011 18:22:28 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]Ninja's_RGR'us (2/11/2011)[/b][hr]Care to translate?? YG&LJF[/quote][b]Y[/b]e [b]G[/b]ods & [b]L[/b]ittle [b]F[/b]ishes(A right pondian exclamation of amazement, now somewhat outdated)The J was a typo - I must have edited too slowly if you saw it.Fri, 11 Feb 2011 18:09:30 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxI 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.Fri, 11 Feb 2011 17:42:45 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxAs 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.Fri, 11 Feb 2011 16:53:01 GMTPete Dashwoodhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxSeems 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.Fri, 11 Feb 2011 16:27:00 GMTThe Danish Dynamohttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxInteresting 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.Fri, 11 Feb 2011 16:00:32 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]Tom.Thomson (2/11/2011)[/b][hr][quote] There are also some reasons from the GUI side of the house in the following short article...[url]http://www.adopenstatic.com/FAQ/selectstarisbad.asp[/url][/quote]YG&LF!! I started reading the stuff at that URL, and hit the second reason given for disliking SELECT *[/quote]Care to translate?? YG&LJFFri, 11 Feb 2011 15:38:45 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote] There are also some reasons from the GUI side of the house in the following short article...[url]http://www.adopenstatic.com/FAQ/selectstarisbad.asp[/url][/quote]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).Fri, 11 Feb 2011 15:09:22 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]yarik (2/11/2011)[/b][hr][quote]This is a very good example of how this campaign against [b]SELECT *[/b] sometimes turns out into a witch-hunt (just like the infamous campaign against [b]GOTO[/b] operator in many programming languages). This is a very good example where the blame is not being placed where it actually belongs. [/quote]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:Fri, 11 Feb 2011 15:02:12 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote]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. [/quote]This is a very good example of how this campaign against [b]SELECT *[/b] sometimes turns out into a witch-hunt (just like the infamous campaign against [b]GOTO[/b] 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:[code="sql"]select Column1, Column2, ... Column5 from CustomerTable[/code]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 [i]also have to actually modify it first[/i] - by adding that pesky new column to the list. How is [i]that[/i] better than just altering without having to modify anything? Using asterisk actually saves you time and leaves less opportunities for a mistake. How can [i]that[/i] be bad thing?The problem here is not with using asterisk. The problem here is that, [i]in spite of having an asterisk there[/i], 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 [i]all[/i] 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, [b]SELECT *[/b] - just like about any other language element - can be extremely useful [i]and[/i] 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 [b]SELECT *[/b] across the board... well, IMHO is a total waste of time and creative resources.Fri, 11 Feb 2011 14:49:40 GMTyarikhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]ZA_Crafty (2/11/2011)[/b][hr]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 2cNow leave my tables alone :-D[/quote]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!Fri, 11 Feb 2011 14:39:30 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]Jeff Moden (11/5/2009)[/b][hr][quote][b]sorte.orm (11/5/2009)[/b][hr]Exactly why is "select * " not a good idea? [/quote] 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...[url]http://www.adopenstatic.com/FAQ/selectstarisbad.asp[/url][/quote]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.Fri, 11 Feb 2011 14:34:42 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxI 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 2cNow leave my tables alone :-DFri, 11 Feb 2011 14:28:53 GMTZA_Craftyhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxPretty 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.Fri, 11 Feb 2011 13:57:08 GMTZebedatioushttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]Jason-299789 (11/5/2009)[/b][hr]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.[/quote]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, .....Fri, 11 Feb 2011 12:48:01 GMTRay Herringhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]jimbobmcgee (2/11/2011)[/b][hr]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.[/quote]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 [url]http://www.sqlservercentral.com/Forums/FindPost845467.aspx[/url].Fri, 11 Feb 2011 12:10:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxOh lighten up. It's 2011. Ever hear of corporate standards? What is the big deal over select * anyhow? It's a tool like any other tool developers or dba's use. This isn't a philosophy class it's business. Things need to get done. Restricting features and usage is just bad.Fri, 11 Feb 2011 11:59:39 GMTJames Bailey-431215http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]jimbobmcgee (2/11/2011)[/b][hr]I might be fuelling the fire here, as I'm merely playing devil's advocate rather than agreeing wholeheartedly with the approach...But, for the people who say that the DBAs/analysts should be able to run SELECT * (for quick-fire checks or the data structure, etc), how about using roles/groups to decide whether this denial is applied? You could deny select on the dummy column for just the developer users and not the DBAs, so it would help to capture these issues during the testing process (maybe as part of an automated first-stage peer-review process).As a sysadmin/DBA in a small dev company who have only recently committed to drafting some sort of coding standards/peer-review system (based on some lengthy meetings regarding improving our quality), tips to 'assist' our developers in moving away from lazier processes are very welcome. As others have mentioned, peer-review takes time and can only reasonably be done by someone who also knows the codebase well enough (unless you reduce it to basic steps, such as '1, check for SELECT *; 2, ...', which just gets you a human equivalent of the same restrictive permission setting).Also, it is a particularly small-picture view to take the stance that developers who forget to follow the standard be relieved of their job -- you would lose a lot of prior client/application knowledge for comparitively little gain.So, how about a middle ground approach? I know it will be different for many people (and I'm not saying I advocate), but it is still quite a common model to have applications (particularly web apps) that use a single user account to access the database, which is part of the connection string -- be it a SQL user or Windows (domain)? (user|computer) account. In those cases how about setting up a script that adds the dummy column to every table and sets up the deny policy against the single user? You would run this during your promotion from dev to test and your test team (sh|w)ould pick up the exception as part of their process. The developers would be told exactly what to expect and what is required, to prevent them closing the bug report as 'unable to reproduce'; and it would give them a reasonable reminder each time so that they eventually move away from the muscle-memory use of SELECT *.TL;DR: 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.[/quote]DENY to roles is so much better. Great idea on implementing this approach for lower ennvironments only! Thank you!Fri, 11 Feb 2011 11:54:41 GMTRaghuram (AJ)http://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspxI might be fuelling the fire here, as I'm merely playing devil's advocate rather than agreeing wholeheartedly with the approach...But, for the people who say that the DBAs/analysts should be able to run SELECT * (for quick-fire checks or the data structure, etc), how about using roles/groups to decide whether this denial is applied? You could deny select on the dummy column for just the developer users and not the DBAs, so it would help to capture these issues during the testing process (maybe as part of an automated first-stage peer-review process).As a sysadmin/DBA in a small dev company who have only recently committed to drafting some sort of coding standards/peer-review system (based on some lengthy meetings regarding improving our quality), tips to 'assist' our developers in moving away from lazier processes are very welcome. As others have mentioned, peer-review takes time and can only reasonably be done by someone who also knows the codebase well enough (unless you reduce it to basic steps, such as '1, check for SELECT *; 2, ...', which just gets you a human equivalent of the same restrictive permission setting).Also, it is a particularly small-picture view to take the stance that developers who forget to follow the standard be relieved of their job -- you would lose a lot of prior client/application knowledge for comparitively little gain.So, how about a middle ground approach? I know it will be different for many people (and I'm not saying I advocate), but it is still quite a common model to have applications (particularly web apps) that use a single user account to access the database, which is part of the connection string -- be it a SQL user or Windows (domain)? (user|computer) account. In those cases how about setting up a script that adds the dummy column to every table and sets up the deny policy against the single user? You would run this during your promotion from dev to test and your test team (sh|w)ould pick up the exception as part of their process. The developers would be told exactly what to expect and what is required, to prevent them closing the bug report as 'unable to reproduce'; and it would give them a reasonable reminder each time so that they eventually move away from the muscle-memory use of SELECT *.TL;DR: 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.Fri, 11 Feb 2011 11:44:02 GMTjimbobmcgeehttp://www.sqlservercentral.com/Forums/Topic813982-1294-1.aspx[quote][b]sorte.orm (11/5/2009)[/b][hr]I asked why "select *" is such a bad thing, and as far as I can tell there has not been any single answer that was good imho. Data transfer amount was mentioned, but you can't make stupid developers smart by applying constraints like preventing "select *". A developer can still select all columns manually, there's no way to tell if he selects more data than he needs. You just have to rely on developers not being stupid, and if you find stupid developers you have to educate them. Indexes not being used correctly can't really be the case as far as I can tell? The where clause determines which index is used, not the data selected?! If this is not the case, then I would certainly appreciate a link to educate my ignorance on this subject.Most sql code is written by hand in a sql console, then tuned and finally implemented in code. That normally starts with select * something, then it is modified to it's final version including just the columns that's needed. We have a lot of select * in our production code because we have created a module that does automatic databinding based on control name. However just one row is selected at a time, so I can hardly see speed or excessive data transfer being valid arguments. Atleast not when you take into account that to implement a new field in a edit screen the only work required is adding the db column, and placing a textbox with the same name as the db column in the interface. No code required.The case of removing columns can easily break code, but that breakes code regardless of the deveopers using select * or select column-names.This solution was quite cunning imo, but it adresses a problem that's not really a problem by creating a much bigger problem (not beeing able to do select *). However I'm sure there's cases where this hack could prove helpful.EDIT:@SQL Noob - your post was relevant I think, but I trust developers that do a "select *" really knows what they are doing so that extra columns, or a changed column ordering or something like that doesn't break their code. Also we don't allow end users to access datatables directly, if that was a requirement I would replicate the column in question so that the main database was still safe from evil locks and people that doesn't have a clue on what they are doing.[/quote]CREATE TABLE MyOldTable (col1 VARCHAR(100), col2 VARCHAR(100))CREATE TABLE MyNewTable (col1 VARCHAR(100), col2 VARCHAR(100))No problem:INSERT INTO MyNewTableSELECT * FROM MyOldTableNow:ALTER TABLE MyOldTable ADD col3 VARCHAR(100)Will no longer work:INSERT INTO MyNewTableSELECT * FROM MyOldTableHowever, if the code had been written as:INSERT INTO MyNewTable ( col1, col2 )SELECT col1, col2FROM MyOldTableThe code would continue to work even after MyOldTable was changed.Fri, 11 Feb 2011 11:40:55 GMTDoug Bishop