February 11, 2011 at 6:59 am
I have seen many posts in here saying it is good for theory, but the solution is not practical. I disagree.. this is coming from a large scale data warehouse implementation over 100 tables. This was an effective way of restricting select * to database and application developers.
February 11, 2011 at 7:02 am
vishal.gamji (2/11/2011)
I have seen many posts in here saying it is good for theory, but the solution is not practical. I disagree.. this is coming from a large scale data warehouse implementation over 100 tables. This was an effective way of restricting select * to database and application developers.
But you could never use SELECT COUNT(*) or SELECT 1 (which is used a lot in EXISTS queries). That is a pretty big disadvantage to me.
Not to mention all the maintenance that goes to creating that extra column and denying all the select permissions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2011 at 7:02 am
Agreed everyone has start somewhere, but if a junior developer is mentored guided there is still no excuse. Additionally this should be picked up in a code review before it enters a production server.
I have had the pain of cleaning up data when people have been adding data in SSIS using select * coupled with a column order change.
February 11, 2011 at 7:05 am
ianemery-640190 (2/11/2011)
Agreed everyone has start somewhere, but if a junior developer is mentored guided there is still no excuse. Additionally this should be picked up in a code review before it enters a production server.
Indeed. I was referring to the "employ only decent developers" statement.
I don't know how the job market is at your place, but here there aren't enought decent developers for everyone 🙂
So it does make more sense to just do code reviews and proper mentoring.
ianemery-640190 (2/11/2011)
I have had the pain of cleaning up data when people have been adding data in SSIS using select * coupled with a column order change.
Ugh. That is just nasty...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2011 at 7:17 am
Well... using SELECT *, 1 or the column name has the same affect in an IF EXISTS clause, and COUNT(PriKey) can always be used for a count.
February 11, 2011 at 7:20 am
There are other ways to avoid "SELECT *" ... if you are using Visual Studio Database Projects or the Data Tier Application (DAC), the rule of avoiding SELECT * can be enforced at the project level as part of the static code analysis.
February 11, 2011 at 7:27 am
February 11, 2011 at 8:18 am
Koen Verbeeck (2/11/2011)
ianemery-640190 (2/11/2011)
It is really simple- employ decent developersIf you pay peanuts you get ….
A good organisation will understand the worth of their data.
If everybody did this, how would junior developers get employed?
Decent junior developers would get employed. The ones that aren't decent wouldn't.
Because a decent junior developer knows when to ask for help and does so when he should senior developers would have time to do their jobs properly, including mentoring and training decent junior developers because they wouldn't be spending so much of their time doing thiongs that the juniors should have delivered easily but have failed to deliver, undoing things the juniors didn't ask for help on but just ploughed ahead and wrote rubbish, and (eventually) jumping through the hoops and paperwork required to terminate someone's employment (and it would be better too for the non-decent junior developers not to be on the receiving end of that).
Tom
February 11, 2011 at 8:31 am
I'm sorry I don't intend to be rude but I see no practical value in the whatsover, just more work.
Poor programming shouldn't be rewarded by thinking up ways to get around it. There is one simple answer - do it properly or find another job! Certainly any programmer who works for me that cannot follow simple instructions because they are "lazy" or "know best" isn't around for very long.
February 11, 2011 at 8:40 am
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.
February 11, 2011 at 8:43 am
I am a developer on a very large point-of-sale system that is constantly changing.
I think it's irresponsible to say "using select *" is always bad. It is bad if you are already using bad practices like using column indexes to retrieve data in your objects. In fact using select * has reduced our maintenance cost when updating tables. We don't have to update every proc that references the table. Obviously I don't use select * for every proc but for type tables and tables that have a small number of columns I certainly do and it does save cost with no impact.
Now if there was a performance cost to using select * I would consider not using it but there isn't.
All you need in place are consistent rules for business objects and SQL programming and you will be fine.
February 11, 2011 at 8:57 am
There seem to be a lot of ‘yes’ men developers. In the past I have explained things and asked do you understand? any questions etc. yes they reply. Then only to find they have not understood at all. There are some good junior developers and there are people I would not trust my TV remote control with- petrol pump attendants in the making..
My biggest concern going forward is- as each year passes with storage becoming cheaper and cheaper, how many companies are storing more and more information and not thinking about the processes and practices around how they store the information. It is starting to come back to bite the ignorant companies. They want BI against all the data they now have and when the quality of the data is poor how good is their BI going to be? Business decisions are made against the data. Garbage in garbage out….
The select * discussion is a perfect example of where things can go seriously wrong.
Cleaning data is not a simple task and as the size of the data stored goes exponential so does the time to clean the data, it is all too easy to make mistakes cleaning the data. Sadly this is a cost that is ignored and not thought about at time when they started collecting the data.
A lot of my work involves reporting, and honestly most company’s data is of a poor quality.
February 11, 2011 at 9:08 am
This was always something that annoyed me especially with tables that had many columns. It's a quick solution I could try. I'd be curious to see if I get any complaints;)
What about using a database trigger to catch the use of * but not (*)? Seems like a perfect application for it.
February 11, 2011 at 9:18 am
I thought this topic sounded familiar:
http://www.sqldev.org/sql-server-database-engine/prevent-select--query-80094.shtml
February 11, 2011 at 9:21 am
create View_Table_1 as
select IdentityKey, ColumnOne from dbo.Table_1;
select * from View_Table_1
Viewing 15 posts - 76 through 90 (of 140 total)
You must be logged in to reply to this topic. Login to reply