October 20, 2009 at 7:08 am
bas de zwart (10/20/2009)
Thanks everyone for your insights. I have to be honest and have been playing a bit of the devils' advocate. I often use the (top x *) statement too, especially when working for new clients in new environments or when working with linked servers when it's harder to find the column names.I find this kind of discussion interesting as it is a method for me in trying to find the exact point where a mandatory process is replaced by best practices. Often there are grey areas of which this is just one of many.
If it helps clarify the thought process on something, it's definitely a valid question and valid debate.
There are a lot of places where compromise is needed in order to achieve best efficiency.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 30, 2009 at 10:59 am
I work a lot with an application where virtually every column in every table is a user defined data type. In stored procedures using temp tables, I can't use the app's UDT's since they don't exist in TempDB.
I do a lot of SELECT * INTO #T FROM SomeTable WHERE 1 = 2
in order to create the temp tables and not have to worry about data types.
Todd Fifield
October 30, 2009 at 3:07 pm
I find the argument to remove options and functionality in an effort to cater to ignorant/lazy people to be remarkably silly. (No offense intended) Extend that argument to anything else and it's laughable. Should we do away with knives all together because some people use them to stab? All food must now be cut with forks.
Within months of this change happening there would be a dozen third party tools to query system tables and autosupply all field names to a table.
On top of all that, in my opinion, 'SELECT *' is a relatively minor performance infraction compared to a lot of things I see people do :hehe:.
October 31, 2009 at 4:03 am
Hi
Generally I agree with "SELECT *" is almost never really needed, but to write specific procedures/queries/whatever for each requirement appears to be an overkill, in my opinion. I query specific columns whenever I work with many data or with wide tables. The cost-benefit ratio should always be a question to ask.
Greets
Flo
@jeff: Throw your pork chops :hehe:
October 31, 2009 at 7:54 pm
About the only time I use a select * inside code is when I've done something in a CTE that then defines the columns.
Even then, it's rare. But the point is, if MY code created the definition, then I see nothing wrong in doing a select *.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply