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 «««1112131415»»

Preventing usage of "SELECT *..." Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 11:01 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 8,679, Visits: 9,205
amenjonathan (2/14/2011)
[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'.

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.

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

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.


Tom
Post #1063712
Posted Monday, February 14, 2011 11:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
Thanks 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).


-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1063742
Posted Monday, February 14, 2011 12:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
amenjonathan (2/14/2011)
Tom.Thomson (2/11/2011)
amenjonathan (2/11/2011)
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.

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.

2. If the ordinal position of columns is changed, SELECT * will make your solution go boom.

Only if your code is unbelievably execrable. Fix the really silly dependencies on column order, and select * does you no harm.

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.

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


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]


i've seen this happen one time

someone 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 down


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1063770
Posted Monday, February 14, 2011 12:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:17 AM
Points: 266, Visits: 604
I 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.
Post #1063802
Posted Monday, February 14, 2011 3:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:40 PM
Points: 70, Visits: 387
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.

Post #1063908
Posted Monday, February 14, 2011 8:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 5, 2014 7:35 PM
Points: 31, Visits: 59
Ninja's_RGR'us (2/13/2011)
Geoff-577403 (2/13/2011)
This can be done a lot easier with SQL 2008 Policy Management Feature.





How?


Isn't it interesting how the order of two words can change the whole meaning of a question .....

"Can this be done a lot easier with SQL 2008 Policy Management Feature"

- Not to worry, I don't think it can.
Post #1063968
Posted Tuesday, February 15, 2011 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 10:28 AM
Points: 11, Visits: 39
So as a young developer myslef. What are the pitfalls of using the Select * ?
Post #1064353
Posted Tuesday, February 15, 2011 9:21 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 8,679, Visits: 9,205
ajolson1964 (2/15/2011)
So as a young developer myslef. What are the pitfalls of using the Select * ?

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.


Tom
Post #1064368
Posted Tuesday, February 15, 2011 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 10:28 AM
Points: 11, Visits: 39
Thanks.
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 response
Post #1064375
Posted Wednesday, February 16, 2011 5:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
cphite (2/14/2011)
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.



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.


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #1064885
« Prev Topic | Next Topic »

Add to briefcase «««1112131415»»

Permissions Expand / Collapse