Twenty tips to write a good stored procedure

  • Grant Fritchey

    SSC Guru

    Points: 396622

    Justa Developer (8/13/2009)


    Grant Fritchey (8/13/2009)


    Introductory error handling article[/url]. It includes 2000 and 2005/2008 syntax.

    Oh, that is lovely, thanks Grant! I'm learning tons and tons!

    Pleasure!

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ta.bu.shi.da.yu

    Hall of Fame

    Points: 3985

    Jeff Moden (8/13/2009)


    jswong05 (8/13/2009)


    True portability is a myth.

    True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.

    I wouldn't want them to... competition spurs innovation.

    Actually - I do want them to. Standards improve interoperability, which spurs competition because it removes vendor lock-in.

    Random Technical Stuff[/url]

  • Jeff Moden

    SSC Guru

    Points: 996865

    ta.bu.shi.da.yu (8/13/2009)


    Jeff Moden (8/13/2009)


    jswong05 (8/13/2009)


    True portability is a myth.

    True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.

    I wouldn't want them to... competition spurs innovation.

    Actually - I do want them to. Standards improve interoperability, which spurs competition because it removes vendor lock-in.

    Ummm.... no... I've gotta disagree. Another name for "vendor lock-in" is "market share" and that really drives the competition. And how can you spur competition if everyone is following the same droll standards?

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • ta.bu.shi.da.yu

    Hall of Fame

    Points: 3985

    Jeff Moden (8/13/2009)


    ta.bu.shi.da.yu (8/13/2009)


    Jeff Moden (8/13/2009)


    jswong05 (8/13/2009)


    True portability is a myth.

    True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.

    I wouldn't want them to... competition spurs innovation.

    Actually - I do want them to. Standards improve interoperability, which spurs competition because it removes vendor lock-in.

    Ummm.... no... I've gotta disagree. Another name for "vendor lock-in" is "market share" and that really drives the competition. And how can you spur competition if everyone is following the same droll standards?

    Well, I guess we'll agree to disagree. I could use the same argument for open source software - i.e. Open Source means market share. The only reason that vendor lock-in equals market share is because it makes it hard to switch to a competing product - in other words it *reduces* competition.

    I believe that standards help. Microsoft switched from NTLM to Kerberos, implemented Active Directory on the LDAP standard and ditched WINS for DNS. Funnily enough, all of a sudden their market share rose dramatically...

    Those droll standards allow people to compete AND gain market share.

    Incidentally, I'm not saying that SQL extensions are necessarily a bad thing. But standardizing extensions sure helps reduce vendor lockin. In my company, we have to support Oracle and SQL Server installations in equal measure. That keeps our market share high. But I can tell you, it's a royal pain.

    Random Technical Stuff[/url]

  • Jeff Moden

    SSC Guru

    Points: 996865

    ta.bu.shi.da.yu (8/13/2009)


    Well, I guess we'll agree to disagree.

    Heh... I can do that.

    But I can tell you, it's a royal pain.

    Now there's something we can both agree on. 🙂

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996865

    rja.carnegie (8/10/2009)


    Last I heard, SELECT @variable1 = expression, @variable2 = expression, ... ; is deprecated.

    For me, that would really be a bad thing. Do you happen to have an MS reference? I might just be missing it, but I don't see it on the 2k5 or the 2k8 deprecation list...

    http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx

    http://msdn.microsoft.com/en-us/library/ms143729.aspx

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Paul White

    SSC Guru

    Points: 150442

    Jeff Moden (8/13/2009)


    rja.carnegie (8/10/2009)


    Last I heard, SELECT @variable1 = expression, @variable2 = expression, ... ; is deprecated.

    For me, that would really be a bad thing. Do you happen to have an MS reference? I might just be missing it, but I don't see it on the 2k5 or the 2k8 deprecation list...

    http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx

    http://msdn.microsoft.com/en-us/library/ms143729.aspx

    I may be mis-remembering it, but I think we established (earlier in this thread) that it is not deprecated; there is simply a cryptic note in Books Online against the SELECT @local_variable entry:

    For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

    I have little insight into the reasons for that statement - though maybe it has something to do with the fact that some operations (like calling XML methods) can only be achieved with SET. Personally, I will continue to prefer SELECT for multiple assignments.

    While I'm here, I just want to add my '+1' to supporting 'non-standard' extensions. SQL code portability is indeed a myth, and a costly one at that.

    Paul

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    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

    I haven't tested this, but I believe that LEFT JOIN will join all rows and then perform the filter operation where the previous two will stop for the corresponding entry as soon as you have found one. In the example above if a customer has multiple addresses, it is better to go for NOT IN or NOT EXISTS

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • Gail Shaw

    SSC Guru

    Points: 1004484

    I need to correct myself here. Surprised no one picked up on this....

    GilaMonster (8/10/2009)


    Also, try to avoid IN. While checking the existence of some values, then use EXISTS instead of IN. Because IN counts the NULL values also, hence slower than EXISTS. Since EXISTS returns Boolean(Yes/No) but IN returns all values hence result set for IN is heavier than EXISTS.

    SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)

    SELECT * FROM employee WHERE NOT EXISTS (SELECT emp_no FROM emp_detail)

    <snip>

    The equivalent query using exists is this:

    SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM emp_detail where employee.emp_no = emp_detail.emp_no)

    The two queries

    SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)

    and

    SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM emp_detail where employee.emp_no = emp_detail.emp_no)

    are not logically equivalent in all cases.

    If the column returned by the subquery in the IN is declared as NOT NULL (emp_no in this case), then these two queries are equivalent and the execution plans are the same.

    If the column returned by the subquery in the IN is declared as nullable (emp_no in this case) , but has no nulls, the queries will return the same results but the EXISTS will run much faster than the IN.

    If the column returned by the subquery in the IN is declared as nullable (emp_no in this case), and has one or more null values, the two queries will return completely different results. Specifically, the query using IN will return no rows.

    A simple example to show this is:

    select * from sys.databases where name not in ('master','model')

    select * from sys.databases where name not in ('master','model', null)

    Note that this is only the case with NOT IN and NOT EXISTS.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Paul White

    SSC Guru

    Points: 150442

    GilaMonster (8/16/2009)


    I need to correct myself here. Surprised no one picked up on this....

    I think I posted something about the query missing table aliases so it wasn't clear which column was being referred to (emp_no may not exist in whatever schema emp_detail resolves into for the executing user). I must admit I rather left it alone since it seemed to want to be a correlated subquery, but wasn't, was missing schema prefixes, and had no table definitions...so I gave it minimal attention.

    GilaMonster (8/16/2009)


    A simple example to show this is:

    select * from sys.databases where name not in ('master','model')

    select * from sys.databases where name not in ('master','model', null)

    Note that this is only the case with NOT IN and NOT EXISTS.

    The behaviour of the second statement rather depends on the setting of ANSI_NULLS. If set to ON, we get a constant scan since it can never return rows because of the NULL comparison. If set to OFF (deprecated and not recommended in any case), we get a plan much like the first statement. It may be exactly the same - I haven't checked it.

    Paul

  • jp-430442

    SSC Enthusiast

    Points: 154

    I dont agree with most of the points listed here.. :w00t:

  • Grant Fritchey

    SSC Guru

    Points: 396622

    jp (8/17/2009)


    I dont agree with most of the points listed here.. :w00t:

    Which, the original 20 or the comments that followed?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • rja.carnegie

    SSC Eights!

    Points: 923

    GilaMonster (8/16/2009)


    A simple example to show this is:

    select * from sys.databases where name not in ('master','model')

    select * from sys.databases where name not in ('master','model', null)

    Note that this is only the case with NOT IN and NOT EXISTS.

    The behaviour of the second statement rather depends on the setting of ANSI_NULLS. If set to ON, we get a constant scan since it can never return rows because of the NULL comparison. If set to OFF (deprecated and not recommended in any case), we get a plan much like the first statement. It may be exactly the same - I haven't checked it.

    I'm in trouble, I don't understand the non-deprecated behaviour 🙁

    IN(...) doesn't seem to mind if a NULL is in there. But NOT (name IN (...) ) apparently also does the same as (name NOT IN (...) ). Can someone walk me through this please? :kiss:

  • Paul White

    SSC Guru

    Points: 150442

    rja.carnegie (8/17/2009)


    IN(...) doesn't seem to mind if a NULL is in there. But NOT (name IN (...) ) apparently also does the same as (name NOT IN (...) ). Can someone walk me through this please? :kiss:

    IN is like an OR...any one of the terms matching results in TRUE. But, *all* the IN(...) items have to be NULL to allow the QO to shortcut the expression to a constant scan - since that is the only way that the expression is guaranteed to return UNKNOWN.

    NOT IN has to compare all of them (like AND) - this falls with the NULL - since it returns UNKNOWN. So any of the terms being NULL guarantees that the expression will return UNKNOWN (since all values must be evaluated).

    Wrapping the IN with NOT() simply turns TRUE into FALSE or vice-versa. UNKNOWN stays as UNKNOWN. The situation is resolved by the IN before NOT has to look at it.

    Does that help?

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Paul White (8/16/2009)


    The behaviour of the second statement rather depends on the setting of ANSI_NULLS.

    Considering I never switch it off, anything I post about nulls can be assumed Ansi_nulls on

    If set to ON, we get a constant scan since it can never return rows because of the NULL comparison.

    The exec plan's a lot more complex than that. The one I got in testing last night (with subquery, not a list) had 4 clustered index scans, one row spool, two nested loop joins and a hash join. (SQL 2008 SP1)

    Edit: That was with no indexes present on the 'join' columns. I didn't get around to testing the case where there were indexes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 196 through 210 (of 245 total)

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