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

SQL Profanities Expand / Collapse
Author
Message
Posted Tuesday, August 12, 2008 7:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 4, 2012 9:20 AM
Points: 583, Visits: 1,060
I'm confused how are these opposed;-

"...not to solve technical issues, but to add value to the business."

In my job I solve technical issues that add value to the business. ;)

(thats what I get paid for anyways :D)

Tool abuse is what its all about - like an old friend of mine said he always "pitied the hammer stranglers" - he used to teach woodwork/metalwork to kids. :D


Hiding under a desk from SSIS Implemenation Work
Post #550968
Posted Tuesday, August 12, 2008 7:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I have to agree with the article, with Phil, and with Grant, all at the same time. (My sig pretty clearly sums up why I say that.)

I have run into threads here where the ideal solution was a cursor, but the person posting it was scared to use one because they'd been told "never, never, ever use a cursor". It's happened twice.

I have run into threads here where the solution was to remove a cursor. I can't count how many times, because BigInt just isn't quite enough for that. (Okay, I'm exagerating. A little. Maybe.)

The rest of these are pretty much the same.

I've used all of these, and the only one I think I'd say, "Never" on is "goto", which isn't even mentioned in the article (but it has already come up in the discussion). All the rest, I've found valid uses for, as exceptions in extraordinary circumstances. I just can't think of a good use for "goto", that If/Else, Try/Catch or Break or some such wouldn't be better. Maybe one day I will, but till then, I avoid it like the plague. Makes it too difficult to debug/refactor.

(After reading this editorial, I'm going to spend all day with George Carlin in my mind, repeating the "seven words you can't say in IT Depts"...)


- 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
Post #550986
Posted Tuesday, August 12, 2008 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 23, 2009 8:19 AM
Points: 8, Visits: 17
What's wrong with views?

BTW, I'm new to the SQL Server world and EVERY MORNING I sit at my desk for 20 mins and read the latest issue in my inbox. I LOVE it and I'm learning SO MUCH!

Ryan
Post #551012
Posted Tuesday, August 12, 2008 8:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, January 4, 2014 11:05 PM
Points: 968, Visits: 67
Good editorial on an important topic.

To me, cursors seem to be the most thoroughly-reviled feature in SQL Server. It's as though people are trying to believe that the assembly and C++ code (oh my! it's not all managed code) underlying the SQL Server engine is ... set oriented?

The first person I ever met who worked on the SQL team had the following job description: optimizing server-side cursors. How ironic.

Anyway, a lot of times when a pill-swallowing newbie, or a really excellent veteran, makes the statement that "cursors are always bad, always perform worse than well-chosen set-based logic, and should always be considered for elimination," I love asking them this question. "If that is the case, then why do so many of the fastest benchmarks posted at tpc.org use lots of cursors?" Their response is usually the very articulate, "um ... really?"

I also love telling people this statement: "there is always a cursor." It makes some people feel violated to know this, but hey, eventually, the truth will set you free, not myths. There is always a cursor defined on the database engine to handle your data retrieval and other DML. So when I write a background job using a cursor to process 5 million rows in the background, throttling the throughput so that we don't overwhelm our somewhat fragile replication infrastructure, I am often reviled. However, people who know me and have worked with me suspect that there is a method to my madness, so of course they revile me politely. I love telling them, as a loving retort, that when they use while loops to emulate cursors, they may be invoking a separate server-side cursor for each fetch (oh forgive me! I mean SELECT ... or is it the same thing?) at the top of each loop iteration. So while I know I am working with one server-side cursor, they may be working with millions of server-side cursors. The irony is deep here, since their goal, of course, is to write code that minimizes cursor usage.

Please don't read any animosity into this post. I just think this is a really amusing topic that we as a community do not understand very well at all. But, as Phil points out, there are plenty of prophets crying out in the wilderness, railing against the evils of cursors. Moreover, we have plenty of use cases where an explicitly-declared cursor is obviously a ridiculous idea. However, I do think there is a real group-think mentality going on that prevents earnest technical discussion of what is a more complex topic than we, as a community, care to admit. So if anything is frustrating to me about the discussion of cursors, or several other of these SQL Profanities, it is that trying to discuss them online, even in excellent communities like this one, too often turns into a flurry of bumper stickers: "Cursors are bad!" "Never use dynamic SQL!" "Triggers are slow!"

Alas, I am an old man and I tire easily of such bumper-sticker exchanges. After 20 years dedicated to nothing but database work, and having been designated a SQL Server SME by Microsoft themselves, the only blanket advice I would give in this context is as follows:

When choosing what programming features to use to solve a problem, there are no valid blanket statements except the statement that there are no valid blanket statements.


Cheers,
Chris



Post #551022
Posted Tuesday, August 12, 2008 8:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, January 4, 2014 11:05 PM
Points: 968, Visits: 67
@G-Squared:

The classic case for using GOTO in SQL 2000 (and earlier) was if you wanted to write one error handling section in a proc, you could put it down at the bottom, and then have a lot of code checks that basically amounted to "if there's a problem GOTO errorHandler." It wasn't the only way to do it (you could have "if there's a problem CALL my_errorHandler_sp and then RETURN") but sometimes procedures needed complicated, one-off error handling, and in that case, GOTO was not a bad solution, at least in my book.

I haven't come across any use case for this in SQL 2005 (or later) yet, since TRY/CATCH has been added.

I'm answering this in the spirit of it being a trivia question. So I don't want people to reply with better ways to handle errors. Really, though, it might be interesting for people to reply with the best use of GOTO they have seen in T-SQL. That could be fun.

Cheers,
Chris



Post #551030
Posted Tuesday, August 12, 2008 8:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
In defense of GOTO.

While not suitable to be published in production, it's a handy little statement to have when working with piecemeal assembly of large blocks of code.



Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #551038
Posted Tuesday, August 12, 2008 8:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Chris: Yes, SQL 2000 needed Goto for exactly that. You're right.

Tom: I haven't found a need for that. I can see where it might have a use, but I generally write my scripts on the assumption that, one day, I may have to turn them into procs, so I try not to use features that I would want to avoid in a proc. Also, since my main objection to goto is that it makes debugging/refactoring more difficult, I would avoid it in any script that has any chance of ever being used again. Again, that's me. That's not a law handed down from on high, or whatever. It's just the habit I maintain. Go with what's comfortable for you.


- 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
Post #551049
Posted Tuesday, August 12, 2008 8:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
chrisleonard (8/12/2008)
... Alas, I am an old man and I tire easily of such bumper-sticker exchanges. After 20 years dedicated to nothing but database work, and having been designated a SQL Server SME by Microsoft themselves, the only blanket advice I would give in this context is as follows:

When choosing what programming features to use to solve a problem, there are no valid blanket statements except the statement that there are no valid blanket statements.

My bumper sticker can beat up your bumper sticker! :D

There is a Clarke's law: "When a distinguished but elderly scientist says that something is possible, he's very probably right. When a distinguished but elderly scientist says that something is not possible, he's very probably wrong." I do love statements that fall in the same flavor as Catch-22.

I have yet to have a need to place cursors in production code, though I do use them occasionally for one-off or management purposes. I also don't do While loops, and I've never seen a need for Goto. Heck, I usually don't remember they're there!

My only quibble with the editorial is the concept of public peer review. I can see the use, but it just doesn't seem practical at the project level. I'm finishing up a file transfer process that's a five step scheduled job with SFTP, batch files, two DTS packages, and copying data between network shares. It also sends emails, and both the email and batch files are built by DTS. There is no way I could post something like that for public review without writing a novel, not to mention having to go through and obfuscate site-specific information for security purposes.

We buy almost all of our apps from vendors, so I can't do anything directly with their code. Everything I do from a development view is to add functionality that interfaces with or reports from the canned systems.

Write the best you can. If it performs well and the execution plan is reasonable and the results are correct, that may be the best you can do. If it doesn't perform well, there's a site called sqlservercentral.com where you can post your code along with table structures and insert statements to build out a dummy load where I've heard there are a few pretty helpful people who can give you a hand.
Post #551059
Posted Tuesday, August 12, 2008 8:30 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
ryan.leuty (8/12/2008)
What's wrong with views?


They have a tendency to be badly misused, much like cursors, UDFs, et al.

I've seen lots of views that included six or seven tables, dozens of columns, and the view is referenced in a proc that needs two of the columns, one each from two tables that have a direct FK relationship.

That kind of thing often kills performance, because it messes up execution plans horribly.

As with all of these, it's not the uses that are bad, it's the misuses, and the fact that misuses outnumber uses by a huge margin.

A view that defines a complex, multi-table join that's used in many procs, and thus simplifies the procs, can be very valuable. Index views of common aggregates can be wonderful. Views that are used by lazy database devs who save themselves the thirty seconds it takes to write a join or two, at the cost of killing the performance, those are bad.

(Just thought of a new sig idea: "Code doesn't kill performance. Bad developers kill performance." The NRA reference might be good enough to run with. :) )


- 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
Post #551061
Posted Tuesday, August 12, 2008 8:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
GSquared - You are right about not leaving loose ends. I was referring to scripts that were on their way to being procs. I use the GOTOs to skip already tested parts, or not yet ready for prime-time pieces. But along with various print and select statements that I may be using, they have to come out before release to production.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #551065
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse