Twenty tips to write a good stored procedure

  • Adam Seniuk

    SSCrazy

    Points: 2281

    Hi All,

    I don't post a lot but I do read almost all of the articles posted here. When I first started out doing SQL, I had some of these same misconceptions about how things worked and even my own ideas on how they "work". I ran tests, read lots of articles, and asked many more experienced co-workers and found out a lot of what many of you experts out there already know, never assume anything on your own with out asking questions first and always assume what you know could be wrong.

    I do think that this article even with its many flaws is a very good example of how as a group of experts can correct what people believe is true to what is actually true. I have to say that some of these myths do come from schooling, I remember 9+ years ago when I was in school that the instructors perpetuated the cursor / while loop myth and the temp table myths. Also #14 is a myth, from my testing of Cursors vs While loops... 2K5/2K8 SQL static cursors kick the hell out of while loops, but then in turn get there butts kicked by set based operations.

    Thanks for the info and I vote for updating the article with the combined efforts posted here.

    Adam

    (Been in IT for 8 years, wrangled SQL for 6 now...)


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

    I have contacted the author to ask them to comment here (after reading all comments).

  • Jeff Moden

    SSC Guru

    Points: 997113

    Lynn Pettis (8/11/2009)


    mtassin (8/11/2009)


    Lynn Pettis (8/10/2009)


    mtassin (8/10/2009)


    I'm sure she wasn't hinting at you. I am pretty sure I have an idea or two who she was thinking about as she wrote this particular post.

    I'm glad to think so. The guys around here tend to think highly of me when it comes to SQL... but then I get to come here and read what those I look up to post and I realize that even after 12 years of this stuff... there's much more to learn πŸ™‚

    Heh... I have to agree with you. This site has taught me quite a lot in the last four years.

    Heh... by the way, Lynn... I love your new avatar... it's exactly the same as the old one except you have pink skin this time. πŸ˜›

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Tore Bostrup-382308

    Old Hand

    Points: 368

    Publishing the article as is (with advice that will lead beginners astray) is OK. Promoting it as the headline article in the daily email is somewhat of an editorial blunder in my book.

    I have found that SQL Server does a pretty good job of selecting a good execution plan if I:

    1: Write queries with sets in mind,

    2: Have the proper indexes on the tables,

    3: Avoid constructions that doesn't allow use of those indexes (be careful with the use of functions and expressions in WHERE clauses, but it is not always bad),

    4: Avoid stored procedures with parameter based logic that confuses the optimizer: I had an example where another developer had written an SP that performed three similar but different functions (with different execution plans). Every (!) time the SP was executed, the optimizer chose the wrong plan. I split it into three separate SPs, and had the application determine which one to execute, which took the procedure from occasionally timing out after (extended timeout) of 45 seconds, to sub-second response.

    5: Handling large datasets (10 million rows and up) may require rethinking some things. While selecting a few rows from a multi-million row table using an index with good selectivity is vastly different from performing mass operations.

    (Since Oracle has been mentioned... :->) The two biggest differences between Oracle and SQL Server:

    a) With Oracle, you have more tools and features to tweak performance

    b) With Oracle, you have to know and use those tools to get consistent performance


    Regards,

    Tore Bostrup

  • mtassin

    SSC-Insane

    Points: 23099

    Jeff Moden (8/11/2009)


    Heh... by the way, Lynn... I love your new avatar... it's exactly the same as the old one except you have pink skin this time. πŸ˜›

    Yours is pretty spiffy too... makes me wish I had like Graphix skills.. I'd find a way to get the flag flapping in mine... :Whistling:



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Paul White

    SSC Guru

    Points: 150442

    Lynn Pettis (8/11/2009)


    One correction to the above regarding table variables

    Thanks Lynn! All these years I have used OUTPUT to send rows to a 'real' tbale or a table variable, it never occurred to me that the 'real' table could also be a 'real' temporary table. I must have read the BOL OUTPUT entry an hundred times! How odd - thanks so much for the enlightenment πŸ™‚

  • gary.proctor

    Mr or Mrs. 500

    Points: 580

    Wow! Who needs a peer review or editorial review? 16 pages of feedback in under 48 hours!?! I think the author got the message loud and clear. I agree that opportunities exist for this particular post, I too was equally disappointed after reading the content, as the headline definitely caught my attention.

    It is almost shameful that this has turned into a case of kicking a person when they are down. I think the message was loud and clear well before page 2 or 3 not to mention page 16 of comments...

    To their defense... At least this person was willing to contribute. Many of the names I am seeing are unfamiliar to me... I am willing to bet that they have yet to offer original submissions.

  • Lynn Pettis

    SSC Guru

    Points: 442359

    Not all posts are related to the article itself. That is one of the benefits of this community and where you actually learn new things or ideas.

  • John te Lintelo

    Say Hey Kid

    Points: 702

    Andy DBA (8/10/2009)


    So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any circumstances in SQL Server where it might outperform them? Does it really take a 3% hit for the filter? (OK, 3 questions)

    NOT IN:

    SELECT * FROM Sales.Customer WHERE CustomerID NOT IN (SELECT CustomerID from Sales.CustomerAddress)

    NOT EXISTS:

    SELECT * FROM Sales.Customer C WHERE NOT EXISTS (SELECT 1 FROM Sales.CustomerAddress CA where C.CustomerID = CA.CustomerID)

    LEFT JOIN:

    SELECT * FROM Sales.Customer C LEFT JOIN Sales.CustomerAddress CA ON C.CustomerID = CA.CustomerID WHERE CA.CustomerID IS NULL

    Using a LEFT JOIN will not return the same results if there are multiple CustomerAddress records that match a Customer record.

  • mtassin

    SSC-Insane

    Points: 23099

    Lynn Pettis (8/11/2009)


    Not all posts are related to the article itself. That is one of the benefits of this community and where you actually learn new things or ideas.

    And some of us discover new things, and find out that folks here have already written about them.

    I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • rja.carnegie

    SSC Eights!

    Points: 923

    I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).

    Don't trouble on my account for "Ashton-Tate/Microsoft SQL Server" v1.0 or "Compact Edition". πŸ˜‰

    2005 and 2008, Standard and Enterprise, and 32/64 bit is already rather a lot to address. I'd say write about the ones that you use, and somebody will correct you about the others!

  • mtassin

    SSC-Insane

    Points: 23099

    rja.carnegie (8/12/2009)


    I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).

    Don't trouble on my account for "Ashton-Tate/Microsoft SQL Server" v1.0 or "Compact Edition". πŸ˜‰

    2005 and 2008, Standard and Enterprise, and 32/64 bit is already rather a lot to address. I'd say write about the ones that you use, and somebody will correct you about the others!

    Yes perhaps I should have qualified it... current versions of SQL server... which would likely be 2000, 2005, and 2008. Yes Microsoft dropped support or whatever with 2000 but its still out there in force.

    Never even heard of Ashton-Tate/Microsoft SQL Server... surprises me since my company's main product is DBASE 3 based.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Tim Walker.

    SSCertifiable

    Points: 5173

    rja.carnegie (8/12/2009)


    Don't trouble on my account for "Ashton-Tate/Microsoft SQL Server" v1.0 or "Compact Edition". πŸ˜‰

    Ashton-Tate (!) - Did you type this into the site directly or use DisplayWrite on your IBM luggable ... πŸ˜›

    .

  • jungnaja

    Old Hand

    Points: 362

    Thanks to my insomnia…I read the comments after reading the whole article.

    Thanks the author for posting this and many thanks to the experts for the corrections. A cup is a half full -- not all 20 tips are wrong.....and some of the wrong ones somewhat benefit me.....I wouldn’t know what I thought was right actually was wrong.

  • Vinoo

    Say Hey Kid

    Points: 670

    mtassin (8/12/2009)


    Lynn Pettis (8/11/2009)


    Not all posts are related to the article itself. That is one of the benefits of this community and where you actually learn new things or ideas.

    And some of us discover new things, and find out that folks here have already written about them.

    I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).

Viewing 15 posts - 151 through 165 (of 245 total)

You must be logged in to reply to this topic. Login to reply