January 28, 2008 at 3:54 am
I've stopped using NOLOCK just because of the drawbacks. I want to see all the data, so I'll take a little bit of a performance hit just to make sure I get everything every time.
January 29, 2008 at 6:18 am
No wonder applications break so easily. I learned not to use 'select *' in my Database 101 class in college years ago. It was very easy to understand where the pitfalls are in using it for anything other than just running adhoc queries in Query Analyzer, yet here we are hearing from 'annejo' (previous post) of 'experienced' developers needing substantial proof that it is undesirable before they will abandon the practice. Sad! I would hate to see what other bad practices they are performing if they can't even get the easy ones.
In a nutshell, 'select *' is just not robust code for all the reasons brought forward in this forum.
If it was easy, everybody would be doing it!;)
January 29, 2008 at 6:28 am
The point of my test was because someone at Microsoft (according to the OP) said that what many people have assumed for years about true about the pitfalls of Select * wasn't necessarily true. I.E., that performance would suffer dramatically.
Yes, there are other pitfalls, which we've all acknowledged, but it was interesting to "bust" another urban legend about SQL Server with a real test.
January 29, 2008 at 6:36 am
Sorry Brandie, I wasn't referring to all the testing you people were doing to answer the OP...that was interesting. I was referring to a post by 'annejo' about having trouble convincing people at work that 'select *' was an evil practice.
If it was easy, everybody would be doing it!;)
January 29, 2008 at 6:40 am
Oh, yes. I have the same issue. I have developers who learned T-SQL from SS 6.5 or 7.0 and still refuse to update a lot of their practices, including the "Select *" to account for the changes in the engine and the newly discovered ways of improving performance.
And they really like using NOLOCK for queries that need to return real time financial data (which goes to the big-wigs) because they want their results to come back fast. @Shudder.
January 30, 2008 at 4:38 pm
Can't see how that can be the issue - because parser must be looking up the column names anyway even if fully specified so that it can error on e.g.
SELECT COLUMNTHATDOESNOTEXITST FROM MY TABLE
I think the only main issue is one of "good habits" as it does bring back more data that you need in most cases (SQL fast, network slow). But the protective issue against unexpected columns appearing in the front end if tables are changed is also important (but sometimes that flexibility is useful - like most things I guess "it depends ..."
James Horsley
Workflow Consulting Limited
January 31, 2008 at 7:46 am
I know everyone will put their 2 cents in on this topic, so here's mine. I don't really consider the index or speed considerations, here's my reasoning.
I use SELECT * when :
1. I am looking at data in Query Analyzer (typing in all the columns for a wide table takes too long)
2. Never in any system in production
I never use SELECT * when :
1. In production
I would never never use it in production for the simple fact that if the table changes, then the procduction code has to be changed. If you select only the columns you need, then adding a column at the end of the table won't break your code.
That pretty much sums it up.
January 31, 2008 at 8:55 am
I'm with Will. That's how I handle things, though I'm usually a select top 10 * from xxxx
for myself.
January 31, 2008 at 9:04 am
Neat trick to get around not knowing column names...
Select Distinct sc.Name + ', '
from sys.objects so
join sys.columns sc
on so.Object_ID = sc.Object_ID
where so.Name = 'MyTable'
Cut & Paste the result set into your SELECT statement, making sure to remove the very last comma before the FROM.
You could also do a dynamic PIVOT on the above, but that's a lot more work than a simple CnP.
January 31, 2008 at 9:17 am
I do something similar for those long tables
SELECT
' ' + 'alias.' + COLUMN_NAME + CASE WHEN ORDINAL_POSITION=@@RowCount THEN '' ELSE ',' END + char(13) + char(10)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME='MYTABLE'
ORDER BY
ORDINAL_POSITION
Just goes a couple of steps further (I like my cols one per line and stepped in) - and using the INFORMATION_SCHEMA view makes it a bit more portable
James Horsley
Workflow Consulting Limited
January 31, 2008 at 9:20 am
here's another trick for those in 2005.
1. write the statement with SELECT * in it
2. highlight the code for that one statement, then right-click on it and pick "design query in edit"
3. click OK
and voila - * has been replaced by column names. you can be slovenly and STILL get specific column names.....:):P
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 31, 2008 at 9:25 am
Matt,
I like that one! Of course, it doesn't work if the table doesn't actually exist in the database you've got the query editor set at (forgot to change over from Master when I tested yours, DOH! @=), but then you've got a real quick CnP without having to do additional edits to get the column names on one line. COOL!
January 31, 2008 at 9:39 am
I'm assuming that the right-click trick is 2005 only cause it didn't work on my 2000 server.
January 31, 2008 at 9:41 am
I'll still stick to right clicking on the table in QA's Explorer window, and selecting script object to [Clipboard] [New Window]. Works with Mgmt Studio too, but the results are kinda mangled.
One good reason to not use *, besides eliminating uneeded fields, is to strip off the useless padding from char fields. Many apps that have been around for some time are loaded with char fields. Also why return the time element of a datetime data type,if it is always going to contain '00:00:00.000'?
Tom Garth
January 31, 2008 at 9:45 am
Red-gate has a nifty tool that came with my toolbelt - SQL Prompt - I used it when it first came out and free, and it was SLOWWWWWWW...but now it is pretty slick...you write "Select * from " then go back to the "*" and it has a tip to "Hit tab to expand", works real slick.
-- Cory
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply