SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Arup Chakraborty / Twenty tips to write a good stored procedure / Latest Posts

RE: Twenty tips to write a good stored procedure

mmartin1 — Thu, 09 Dec 2010 12:20:56 GMT

In regard to operators, just quickly concerning negating operations, ie... someColumn <> 'string' ,don't forget that rows with a null in that column will not be returned. I just use [code]where somecolumn != 'string' or someColumn is null. [/code] if that what's intended.

RE: Twenty tips to write a good stored procedure

meenakshi.x.pande — Tue, 01 Dec 2009 05:48:35 GMT

Sorry Arup, I wanted to rate this topic as 5 and by mistake I rated 1. Apologies for this.The article is very good and helpful.Regards,Meenakshi Pande

RE: Twenty tips to write a good stored procedure

Thomas-282729 — Sun, 27 Sep 2009 17:46:54 GMT

These are my 2 cents are the OA:RE Capitalizing keywordsIMO, this is an outdated suggestion. Back in the day when people used monochrome editors, it made sense. It might even make sense when posting to a forum which only shows code in the same color but in general, I prefer pascal casing as it is much easier to read. Frankly, what matters most is consistency and next is readability.RE: "Use as few as possible variables."Nonsense. I challenge the author to show noticiable metrics on the performance difference. IMO, clarity of code is almost always more important than fractional performance differences due to suggestions like this.RE: Dynamic queriesIf you use sp_executesql, then you do get the benefit of query plan reuse which the author point out later. Recompilation isn't the reason against dynamic queries. Obfuscation of code and security issues are the primary reasons against it.RE: RecompilesGilaMonster's post on 10-Aug says it quite well, "recompiles aren't always bad."RE: Set vs SelectAgain, clarity of code wins over fractional performance gains. The Set statement illustrates a far clearer intent than a Select statement to set variables. However, using the Select statement to set a bunch of variables should be seen as more of a coding shortcut than a real performance improver.RE: Cast vs ConvertI would go further and emphasize that the reason to Cast is to make your intent clear. Convert can also be used to format the output which should be avoided in database access code.RE: Avoid Distinct Does Distinct really perform any better than Group By? Probably not. It is obviously a good idea to eliminate unnecessary uses of Distinct.RE: Select IntoMy preference would be for developers to avoid Select Into while it is fine for administrative tasks. The reason, again, is clarity of code especially when the resultset contains a large number of columns.

RE: Twenty tips to write a good stored procedure

Paul White — Mon, 31 Aug 2009 18:29:28 GMT

[quote][b]Andy DBA (8/31/2009)[/b][hr]Sorry for beating this to death, but[/quote]As am I. I think we've covered this ground already, and determined that the counter-intuitive behaviour is limited to NOT IN and NOT EXISTS...[quote][b]Andy DBA (8/31/2009)[/b][hr]You HAVE TO rewrite the query separately. If @value3 is NULL, for example, you have to re-write as[code]...WHERE col IN (@value1, @value2) OR col IS NULL [/code] which will succeed if col matches @value1 or @value2, or if col is NULL.[code]...WHERE col IN (@value1, @value2, @value3 ) [/code] will ALWAYS fail if @value3 is NULL no matter what value is in col.[/quote]Tsk tsk for the CAPS ;-). Also, what you have stated is incomplete or wrong, depending on how charitable the reader is:[code]SET ANSI_NULLS ON;DECLARE @T TABLE (A INT NULL);INSERT @T VALUES (1);INSERT @T VALUES (2);INSERT @T VALUES (NULL);SELECT A FROM @T WHERE A IN (1, 2) -- 2 rowsSELECT A FROM @T WHERE A IN (1, 2, NULL) -- 2 rowsSELECT A FROM @T WHERE A IN (NULL) -- no rows! (query plan is a constant scan)SET ANSI_NULLS OFF;SELECT A FROM @T WHERE A IN (1, 2) -- 2 rowsSELECT A FROM @T WHERE A IN (1, 2, NULL) -- 3 rows!SELECT A FROM @T WHERE A IN (NULL) -- 1 row!SET ANSI_NULLS ON;[/code][quote][b]Andy DBA (8/31/2009)[/b][hr]But, if your [code]WHERE col IN[/code] clause is a subselect::[code]...WHERE col IN (SELECT othercol FROM othertable) [/code] you don't have that luxury and should avoid using IN if NULLs might be returned. In this simple case just use an INNER JOIN:[code]...INNER JOIN othertable ON col = othercol[/code] if you don't want to match on NULLs and[code]...INNER JOIN othertable ON col = othercol OR col IS NULL[/code] if you do.[/quote]This adds nothing new. NULL behaviour with IN is well documented and logical.Consider:[code]SET ANSI_NULLS ON;DECLARE @T TABLE (A INT NULL);DECLARE @X TABLE (A INT NULL);INSERT @T VALUES (1);INSERT @T VALUES (2);INSERT @T VALUES (NULL);INSERT @X VALUES (1);INSERT @X VALUES (NULL);SELECT A FROM @X WHERE A IN (SELECT A FROM @T) -- 1 row (where A = 1)SET ANSI_NULLS OFF;SELECT A FROM @X WHERE A IN (SELECT A FROM @T) -- 2 rows (the NULL also matches now)SET ANSI_NULLS ON;[/code]Had you been referring to NOT IN, you would have more of a point. But again, this has been flogged to death already.For completeness then:[code]SET ANSI_NULLS ON;DECLARE @T TABLE (A INT NULL);INSERT @T VALUES (1);INSERT @T VALUES (2);INSERT @T VALUES (NULL);SELECT A FROM @T WHERE A NOT IN (1, 2) -- no rowsSELECT A FROM @T WHERE A NOT IN (1, 2, NULL) -- no rowsSELECT A FROM @T WHERE A NOT IN (NULL) -- no rowsSET ANSI_NULLS OFF;SELECT A FROM @T WHERE A NOT IN (1, 2) -- 1 row (for the NULL)SELECT A FROM @T WHERE A NOT IN (1, 2, NULL) -- no rowsSELECT A FROM @T WHERE A NOT IN (NULL) -- 2 rows! (for the non-NULL values)SET ANSI_NULLS ON;SET ANSI_NULLS ON;DECLARE @T TABLE (A INT NULL);DECLARE @X TABLE (A INT NULL);INSERT @T VALUES (1);INSERT @T VALUES (2);INSERT @T VALUES (NULL);INSERT @X VALUES (1);INSERT @X VALUES (NULL);SELECT A FROM @X WHERE A NOT IN (SELECT A FROM @T) -- No rowsSET ANSI_NULLS OFF;SELECT A FROM @X WHERE A NOT IN (SELECT A FROM @T) -- No rowsSET ANSI_NULLS ON;[/code]Paul

RE: Twenty tips to write a good stored procedure

Andy DBA — Mon, 31 Aug 2009 11:31:58 GMT

[quote][b]rja.carnegie (8/31/2009)[/b][hr]There could be a role for this IN equivalent (I hope),[code]WHERE ( col = @value1 OR col = @value2 OR col = @value3 )[/code]in which one or more of @value1, @value2, @value3 may be NULL, of course.Is it better in that case to rewrite the query separately with one condition fewer, or to re-use one version? I would like to think that the server is smart enough not to spend much time on the comparison to NULL, which counts against writing a separate version for that case.[/quote]Sorry for beating this to death, butYou HAVE TO rewrite the query separately. If @value3 is NULL, for example, you have to re-write as[code]...WHERE col IN (@value1, @value2) OR col IS NULL [/code] which will succeed if col matches @value1 or @value2, or if col is NULL.[code]...WHERE col IN (@value1, @value2, @value3 ) [/code] will ALWAYS fail if @value3 is NULL no matter what value is in col.But, if your [code]WHERE col IN[/code] clause is a subselect::[code]...WHERE col IN (SELECT othercol FROM othertable) [/code] you don't have that luxury and should avoid using IN if NULLs might be returned. In this simple case just use an INNER JOIN:[code]...INNER JOIN othertable ON col = othercol[/code] if you don't want to match on NULLs and[code]...INNER JOIN othertable ON col = othercol OR col IS NULL[/code] if you do.

RE: Twenty tips to write a good stored procedure

Jeff Moden — Mon, 31 Aug 2009 10:54:13 GMT

[quote][b]jacroberts (8/25/2009)[/b][hr][quote][hr]10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their precedence.=, >, <, >=, <=, <>, !=, !>, !

RE: Twenty tips to write a good stored procedure

Jeff Moden — Mon, 31 Aug 2009 10:22:56 GMT

[quote][b]arup_kc (8/24/2009)[/b][hr]Unfortunately, in this case, you only substitted one form of RBAR for another form of RBAR. The best solution in this case is to find a set-based solution to replace a cursor-based solution (and a WHILE + Temp Table is still a cursor-based solution).RBAR = Row By Agonizing Row (A Modenism)Hi Lenn,What you are telling is absolutely correct. [size="3"][font="Arial Black"]Some there are some situations where we cant avoid cursors...I am telling abt that situation.[/font][/size] Also, I have checked the Temp+While solutions, indeed it gave better performance...thats why I included that in this article.[/quote]Heh... ya just [i]gotta[/i] know that will get my attention... Now I have to go back and look again. ;-)And the fellow's name is "Lynn", not "Lenn". ;-)

RE: Twenty tips to write a good stored procedure

rja.carnegie — Mon, 31 Aug 2009 09:34:26 GMT

[quote][b]jacroberts (8/25/2009)[/b][hr][quote][hr]11. More WHERE clause hints - Avoid unnecessary conditions in the WHERE Clause. Also try to avoid a function in the WHERE clause as it [b]presents SQL engine to do[/b] index seek. Even it forces SQL full index scans or even table scans.[/quote]should be "prevents the SQL engine doing an".[/quote]And without objecting to the general point, I think in some queries, there'll be one condition that does the heavy work of selecting the records that you're playing with and then some kind of internal intermediate resultset that other conditions are applied to, so that your function is evaluated for 10 rows instead of 10 million (ouch). But on the other hand, the server may decide to select rows only on on one condition, separately select rows on another condition, and then compare and match up the two resultsets to produce the query result. I don't feel [i]very[/i]bad that I don't recall correct terminology for this sort of thing, because that wouldn't get make much closer to saying what I mean, but... did I have a point?Well, I guess (is this a dummy-level comment?) that if your query strictly looks like "SELECT * FROM table WHERE ( dbo.ufn_validity(@nearRowID) = 1 )"and you know that the following gets the sameresults,"SELECT * FROM table WHERE ( dbo.ufn_validity(@nearRowID) = 1 AND rowNumber BETWEEN @nearRowID-20 AND @nearRowID+20 )"then the second version may leave the first for dead-slow.

RE: Twenty tips to write a good stored procedure

rja.carnegie — Mon, 31 Aug 2009 09:16:45 GMT

[quote][b]Lynn Pettis (8/24/2009)[/b][hr]I understand that what you wrote was an illistration of how IN works. My point is if you actually write a WHERE clause like that, you would use OR somecol IS NULL, not OR somecol = NULL. Two different things. Have I clarified my post yet? I fully understand yours.[/quote]There could be a role for this IN equivalent (I hope),[code]WHERE ( col = @value1 OR col = @value2 OR col = @value3 )[/code]in which one or more of @value1, @value2, @value3 may be NULL, of course.Is it better in that case to rewrite the query separately with one condition fewer, or to re-use one version? I would like to think that the server is smart enough not to spend much time on the comparison to NULL, which counts against writing a separate version for that case.

RE: Twenty tips to write a good stored procedure

Jonathan AC Roberts — Tue, 25 Aug 2009 10:11:23 GMT

[quote][hr]11. More WHERE clause hints - Avoid unnecessary conditions in the WHERE Clause. Also try to avoid a function in the WHERE clause as it [b]presents SQL engine to do[/b] index seek. Even it forces SQL full index scans or even table scans.[/quote]prevents the SQL engine doing an

RE: Twenty tips to write a good stored procedure

Jonathan AC Roberts — Tue, 25 Aug 2009 10:07:53 GMT

[quote][hr]10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their precedence.=, >, <, >=, <=, <>, !=, !>, !

RE: Twenty tips to write a good stored procedure

Jonathan AC Roberts — Tue, 25 Aug 2009 10:00:59 GMT

Thought I'd get in with the first comment, only a spelling mistake that gets through a spell checker:Update: I would recommend you to use SET NOCOUNT ON for the [b]shake [/b]of performance unless there is a very good reason for using it.

RE: Twenty tips to write a good stored procedure

Steve Jones - SSC Editor — Tue, 25 Aug 2009 09:44:00 GMT

Arup has updated the content and I have replaced this content with the updates.

RE: Twenty tips to write a good stored procedure

Andy DBA — Mon, 24 Aug 2009 13:24:00 GMT

Yes. It is now clear that your post was about evaluating NULLs in WHERE clauses. I agree that it is correct to use "is NULL" as shown in three of the example queries in my 8/18/2009 post. I apologize for incorrectly assuming you misinterpreted my illustration of how IN works.

RE: Twenty tips to write a good stored procedure

Lynn Pettis — Mon, 24 Aug 2009 11:12:53 GMT

I understand that what you wrote was an illistration of how IN works. My point is if you actually write a WHERE clause like that, you would use OR somecol IS NULL, not OR somecol = NULL. Two different things. Have I clarified my post yet? I fully understand yours.

RE: Twenty tips to write a good stored procedure

Andy DBA — Mon, 24 Aug 2009 10:57:15 GMT

[quote][b]Lynn Pettis (8/24/2009)[/b]First, whoever wrote should not have used somecol = NULL. And if you pull the NOT inside the paranes, the change that should be made.Sorry for the confusion.[/quote]I disagree. I wrote "somecol = NULL" to illustrate why NOT IN doesn't work if nulls exist in the data. Of course it's incorrect to use it in a query, but it accurately represents what IN does and it's why the NOT IN query doesn't work if nulls exist in the data. With ANSI NULLS ON, "somecol = NULL" evaluates to UNKNOWN instead of TRUE or FALSE.Once UNKNOWN is in the expression, the NOT doesn't matter because NOT UNKNOWN is the same as UNKNOWN. If readers are focused on changing "NOT(expression1 OR expression2 OR UNKNOWN)" to "(NOT expression1) AND (NOT expression2) AND (NOT UNKNOWN)", they're probably distracted by a simple boolean logic exercise instead of focusing on the reason why the query doesn't work.

RE: Twenty tips to write a good stored procedure

Lynn Pettis — Mon, 24 Aug 2009 09:51:04 GMT

Not disputing the NOT IN not working if nulls exist in the data.I'm disputing the explicit code that has been shown. First, whoever wrote should not have used somecol = NULL. And if you pull the NOT inside the paranes, the change that should be made.Sorry for the confusion.

RE: Twenty tips to write a good stored procedure

Andy DBA — Mon, 24 Aug 2009 09:12:08 GMT

[quote][b]Lynn Pettis (8/24/2009)[/b][hr][quote][b]Andy DBA (8/24/2009)[/b][hr][quote][b]rja.carnegie (8/24/2009)[/b][hr][quote][b]Andy DBA (8/18/2009)[/b][quote][b]Lynn Pettis (8/24/2009)[/b]Andy, only change I'd make to your code is this:... OR somecol IS NULL) ...If you then moved the NOT inside the parens, that would change to this:... AND somecol IS NOT NULL) ...[/quote]NO NO NO NO :pinch: Please re-read my original post. It's not "my code". It's the equivalent of what NOT IN does. The whole point is that NOT IN doesn't do an IS NULL comparison. It does an = comparison to each item in the list. If there's a NULL, it does the equvivalent of ...OR [b]somecol = NULL[/b]..., so it will NEVER NEVER NEVER return TRUE and the WHERE clause will ALWAYS fail regardless of the value in somecol. (Sorry about the SHOUTING, it's Monday morning and I'm cranky.:w00t:)To see this please run the DDL from my original post and then run the following:SELECT p.* FROM parent p WHERE p.match_col NOT IN (SELECT c.match_col FROM child c)This will return an empty result set even though the data set has orphans. Please then run the following to list the orphans.--NULL compared to NULL not considered a matchSELECT P.* FROM parent p LEFT JOIN child C ON P.match_col = C.match_colWHERE C.childid IS NULL--NULL compared to NULL is considered a matchSELECT p.* FROM parent PLEFT JOIN child C ON P.match_col = C.match_col OR (P.match_col IS NULL AND C.match_col IS NULL)WHERE C.childid IS NULL--notice we're checking the childid here and not match_colSo, my point is: The NOT IN query doesn't work if the comparison list contains NULLS.

RE: Twenty tips to write a good stored procedure

Lynn Pettis — Mon, 24 Aug 2009 08:24:15 GMT

[quote][b]Andy DBA (8/24/2009)[/b][hr][quote][b]rja.carnegie (8/24/2009)[/b][hr][quote][b]Andy DBA (8/18/2009)[/b]For example:WHERE somecol NOT IN (1,2, NULL) is equivalent toWHERE NOT (somecol = 1 OR somecol = 2 OR somecol = NULL)[/quote]Wait, isn't that backwards? IN is equivalent to a list of ORs, NOT IN is equivalent to a list of NOT... AND NOT... AND NOT?[/quote]It's not backwards because I still have the NOT outside the parens. The important point here is that because there is a NULL comparison in the expression, the entire expression evaluates to UNKNOWN regardless of the other items in the list or the value in the comparison column. If you're trying to produce a list of records without matching records in another table, (sometimes called "orphans" eg. customers without address records) a NULL in the comparison list will cause the NOT IN query to NEVER return any results. Any orphans that may be present will be missed.The NOT EXISTS and LEFT JOIN queries don't have this problem and can even be coded to treat NULL = NULL as a "match" if desired. I wouldn't design a new system like that, but you don't always have control over the data set or business rules.[/quote]Andy, only change I'd make to your code is this:... OR somecol IS NULL) ...If you then moved the NOT inside the parens, that would change to this:... AND somecol IS NOT NULL) ...

RE: Twenty tips to write a good stored procedure

Lynn Pettis — Mon, 24 Aug 2009 08:21:49 GMT

[quote][b]arup_kc (8/24/2009)[/b][hr]Unfortunately, in this case, you only substitted one form of RBAR for another form of RBAR. The best solution in this case is to find a set-based solution to replace a cursor-based solution (and a WHILE + Temp Table is still a cursor-based solution).RBAR = Row By Agonizing Row (A Modenism)[/quote]Hi Lenn,What you are telling is absolutely correct. Some there are some situations where we cant avoid cursors...I am telling abt that situation. Also, I have checked the Temp+While solutions, indeed it gave better performance...thats why I included that in this article.[/quote]Situations that require cursors or while/temp tables are rare and usually are found in maintenance routines. I have been in the situation where I have seen them used and yes they worked, and unfortunately the attempts I tried to eliminate the cursor failed (ended up with what I now know of as a triangular join, just as bad if not worse), but now that I have learned more I'm not in a position anymore to try and eliminate the cursor (I don't work there anymore).

RE: Twenty tips to write a good stored procedure

Andy DBA — Mon, 24 Aug 2009 08:05:32 GMT

[quote][b]rja.carnegie (8/24/2009)[/b][hr][quote][b]Andy DBA (8/18/2009)[/b]For example:WHERE somecol NOT IN (1,2, NULL) is equivalent toWHERE NOT (somecol = 1 OR somecol = 2 OR somecol = NULL)[/quote]Wait, isn't that backwards? IN is equivalent to a list of ORs, NOT IN is equivalent to a list of NOT... AND NOT... AND NOT?[/quote]It's not backwards because I still have the NOT outside the parens. The important point here is that because there is a NULL comparison in the expression, the entire expression evaluates to UNKNOWN regardless of the other items in the list or the value in the comparison column. If you're trying to produce a list of records without matching records in another table, (sometimes called "orphans" eg. customers without address records) a NULL in the comparison list will cause the NOT IN query to NEVER return any results. Any orphans that may be present will be missed.The NOT EXISTS and LEFT JOIN queries don't have this problem and can even be coded to treat NULL = NULL as a "match" if desired. I wouldn't design a new system like that, but you don't always have control over the data set or business rules.

RE: Twenty tips to write a good stored procedure

arup chakraborty — Mon, 24 Aug 2009 07:02:04 GMT

Unfortunately, in this case, you only substitted one form of RBAR for another form of RBAR. The best solution in this case is to find a set-based solution to replace a cursor-based solution (and a WHILE + Temp Table is still a cursor-based solution).RBAR = Row By Agonizing Row (A Modenism)[/quote]Hi Lenn,What you are telling is absolutely correct. Some there are some situations where we cant avoid cursors...I am telling abt that situation. Also, I have checked the Temp+While solutions, indeed it gave better performance...thats why I included that in this article.

RE: Twenty tips to write a good stored procedure

Lynn Pettis — Mon, 24 Aug 2009 06:50:56 GMT

[quote][b]arup_kc (8/24/2009)[/b][hr]As an example, the cursor/temp table issue. In several SP's I have replaced the cursor with a combination of temaptable+while and it gave me better performance.[/quote]Unfortunately, in this case, you only substitted one form of RBAR for another form of RBAR. The best solution in this case is to find a set-based solution to replace a cursor-based solution (and a WHILE + Temp Table is still a cursor-based solution).RBAR = Row By Agonizing Row (A Modenism)

RE: Twenty tips to write a good stored procedure

arup chakraborty — Mon, 24 Aug 2009 03:09:35 GMT

Hi everybody,Thanks for constant support. At least I have got a lot of tips fromt he forum discussions. The first thing I should say, I had some misconceptions which are rectified now.Along with that, I am trying to modify the article with the newly learned tips. Hope this will be helpful for all of us.But one thing I must say, this article is on PERFORMANCE. For me, its a very relative term. Thats why I have advised everybody atleast to check with profiler before/after applying the tips. Its difficult to frame rigid performance tips because of the relativity.As an example, the cursor/temp table issue. In several SP's I have replaced the cursor with a combination of temaptable+while and it gave me better performance.Again, I am thanking to Steve and SSC and all forumaists...for there time and energy.

RE: Twenty tips to write a good stored procedure

rja.carnegie — Mon, 24 Aug 2009 02:25:27 GMT

[quote][b]Andy DBA (8/18/2009)[/b]For example:WHERE somecol NOT IN (1,2, NULL) is equivalent toWHERE NOT (somecol = 1 OR somecol = 2 OR somecol = NULL)[/quote]Wait, isn't that backwards? IN is equivalent to a list of ORs, NOT IN is equivalent to a list of NOT... AND NOT... AND NOT?

RE: Twenty tips to write a good stored procedure

Jeff Moden — Fri, 21 Aug 2009 07:06:01 GMT

[quote][b]arup_kc (8/18/2009)[/b][hr]Hi,Sorry for late replying, actually I was out of net for last 3 weeks. I have seen the comments. I do agree that I had some misconceptions, which are now very clear to me. Thanks to every body...but my main aim is fulfilled I.E. LEARNING. If sqlservercentral.com will not publish this article, then probably I would apply these in my application (I think some of u would do the same). Again, thanks to sqlservercentral.comAnyway, I need some more time to go in depth and reply. And obviously I will do that.Thanks to all again.[/quote]Ya know... that's a outstanding attitude. Well done, Arup.

RE: Twenty tips to write a good stored procedure

arup chakraborty — Fri, 21 Aug 2009 05:07:43 GMT

[quote][b]Anton Kaborka (8/10/2009)[/b][hr]Don't you mean to advise "set nocount on"?[/quote]Hi Anton, u r right...sorry 4 the typo error

RE: Twenty tips to write a good stored procedure

Paul White — Wed, 19 Aug 2009 02:44:09 GMT

Good on you Arup. :cool:Paul

RE: Twenty tips to write a good stored procedure

ta.bu.shi.da.yu — Wed, 19 Aug 2009 02:07:08 GMT

[quote][b]arup_kc (8/18/2009)[/b][hr]Hi,Sorry for late replying, actually I was out of net for last 3 weeks. I have seen the comments. I do agree that I had some misconceptions, which are now very clear to me. Thanks to every body...but my main aim is fulfilled I.E. LEARNING. If sqlservercentral.com will not publish this article, then probably I would apply these in my application (I think some of u would do the same). Again, thanks to sqlservercentral.comAnyway, I need some more time to go in depth and reply. And obviously I will do that.Thanks to all again.[/quote]He speaks! Thanks for the comment :-) I totally agree - I'm here on SSC to learn as well. Sometimes the only way is by making mistakes...

RE: Twenty tips to write a good stored procedure

arup chakraborty — Tue, 18 Aug 2009 23:55:20 GMT

Hi,Sorry for late replying, actually I was out of net for last 3 weeks. I have seen the comments. I do agree that I had some misconceptions, which are now very clear to me. Thanks to every body...but my main aim is fulfilled I.E. LEARNING. If sqlservercentral.com will not publish this article, then probably I would apply these in my application (I think some of u would do the same). Again, thanks to sqlservercentral.comAnyway, I need some more time to go in depth and reply. And obviously I will do that.Thanks to all again.

RE: Twenty tips to write a good stored procedure

Andy DBA — Tue, 18 Aug 2009 12:13:23 GMT

[quote][b]rja.carnegie (8/17/2009)[/b]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:[/quote]I see Paul already gave this answer to rja.carnegie, so please forgive me for adding more detail. What he is saying is that with ANSI NULLS ON, the WHERE clause will always fail if the list of "IN (...) items" contains one or more NULLS. (This holds true for a subquery, or a "hardcoded" list, but if a hardcoded list contains a NULL, the Query Optimizer can greatly simplify the plan because it "knows" that the WHERE clause will never evaluate to TRUE. It will always be UNKOWN and therefore will always fail)For example:WHERE somecol NOT IN (1,2, NULL) is equivalent toWHERE NOT (somecol = 1 OR somecol = 2 OR somecol = NULL) ANSI NULLS ON causes NULL comparisons to be evaluated as UNKNOWN, so if somecol contains, let's say a 3, this evaluates to WHERE NOT (FALSE OR FALSE OR UNKNOWN) which is equivalant to WHERE NOT (UNKNOWN) which is equivalant to WHERE UNKNOWNWhich will fail and not return any rows.Obviously if a column is defined as NOT NULL, it can't contain NULLs, so a simple subquery list that just selects from that column will never contain NULLs and there is no issue with using the NOT IN technique.In my opinion, it's much safer to leave ANSI NULLS ON and avoid using the NOT IN technique if your comparison list allows NULLs. Decide if you want NULL compared to NULL to be considered a "match" and explicitly build it into your query as shown in these simple examples (Yes, the tables should have indexes ;-) Remember, the goal here is to list parent records that DON'T have a matching column (match_col) in any of the child records.IF OBJECT_ID(N'parent', N'U') IS NOT NULL DROP TABLE parentCreate table parent(match_col int NULL,id_description nvarchar(255) NULL )IF OBJECT_ID(N'child', N'U') IS NOT NULL DROP TABLE childCreate table child(childid int NULL,match_col int NULL)--Put in some test dataINSERT parentvalues(1,'match_col is 1. Have a match in child table.')INSERT parentvalues(2,'match_col is 2. No match in child table')INSERT parentvalues(NULL,'match_col is null. Does it match NULLs in child table?')INSERT childvalues(1,1)INSERT childvalues(2,NULL)--NULL compared to NULL is considered a match--LEFT JOIN techniqueselect p.* from parent Pleft joinchild C on P.match_col = C.match_col or (P.match_col is NULL and C.match_col is NULL)where C.childid is null --notice we're checking the childid here and not match_col--NULL compared to NULL is considered a match--NOT EXISTS techniqueselect * from parent Pwhere NOT EXISTS (select 1 from child C where C.match_col = P.match_col) ANDNOT (P.match_col IS NULL and EXISTS (select 1 from child D where D.match_col IS NULL))--equivalent result sets but very different execution plans--NULL compared to NULL not considered a match--LEFT JOIN techniqueselect P.* from parent Pleft joinchild C on P.match_col = C.match_colwhere C.childid is null--NULL compared to NULL not considered a match--NOT EXISTS techniqueselect * from parent Pwhere NOT EXISTS (select 1 from child C where C.match_col = P.match_col)

RE: Twenty tips to write a good stored procedure

Paul White — Tue, 18 Aug 2009 06:25:45 GMT

Gail,I think you have mis-read one or more posts at some stage.My points about ANSI_NULL and the Constant Scan iterator quoted this code:[code]select * from sys.databases where name not in ('master','model')select * from sys.databases where name not in ('master','model', null)[/code]So some of your replies have not made sense to me.Paul

RE: Twenty tips to write a good stored procedure

GilaMonster — Tue, 18 Aug 2009 02:22:58 GMT

[quote][b]Paul White (8/17/2009)[/b]Your posted examples were lists not sub-queries, so I don't really see what you are objecting to?[/quote]I posted one list as a quick way to show behaviour. All the other queries that I'd been talking about in that post and the one that was quoted, and the one where the relative performance was questioned and mentioned were IN and EXISTS with subqueries.

RE: Twenty tips to write a good stored procedure

Paul White — Mon, 17 Aug 2009 17:17:16 GMT

[quote][b]GilaMonster (8/17/2009)[/b][hr][quote][b]Paul White (8/16/2009)[/b][hr]The behaviour of the second statement rather depends on the setting of ANSI_NULLS. [/quote]Considering I never switch it off, anything I post about nulls can be assumed Ansi_nulls on[/quote]It is still a true statement :-)If nothing else, think of all the stored procedures out there with ANSI_NULLS OFF.[quote][b]GilaMonster (8/17/2009)[/b][hr][quote] If set to ON, we get a constant scan since it can never return rows because of the NULL comparison. [/quote]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.[/quote]Well of course the execution plan is more complex - unless you include a NULL in a hard-coded list with ANSI_NULLS ON, in which case you get a constant scan and that's it. Your posted examples were lists not sub-queries, so I don't really see what you are objecting to?Paul

RE: Twenty tips to write a good stored procedure

rja.carnegie — Mon, 17 Aug 2009 08:41:20 GMT

And free onion rings.

RE: Twenty tips to write a good stored procedure

GilaMonster — Mon, 17 Aug 2009 07:42:18 GMT

[quote][b]Paul White (8/16/2009)[/b][hr]The behaviour of the second statement rather depends on the setting of ANSI_NULLS. [/quote]Considering I never switch it off, anything I post about nulls can be assumed Ansi_nulls on[quote] If set to ON, we get a constant scan since it can never return rows because of the NULL comparison. [/quote]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.

RE: Twenty tips to write a good stored procedure

Paul White — Mon, 17 Aug 2009 07:18:17 GMT

[quote][b]rja.carnegie (8/17/2009)[/b][hr]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:[/quote]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?

RE: Twenty tips to write a good stored procedure

rja.carnegie — Mon, 17 Aug 2009 06:57:32 GMT

[quote][quote][b]GilaMonster (8/16/2009)[/b][hr]A simple example to show this is:[code]select * from sys.databases where name not in ('master','model')select * from sys.databases where name not in ('master','model', null)[/code]Note that this is only the case with NOT IN and NOT EXISTS.[/quote]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.[/quote]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:

RE: Twenty tips to write a good stored procedure

Grant Fritchey — Mon, 17 Aug 2009 06:20:49 GMT

[quote][b]jp (8/17/2009)[/b][hr]I dont agree with most of the points listed here.. :w00t:[/quote]Which, the original 20 or the comments that followed?

RE: Twenty tips to write a good stored procedure

jp-430442 — Mon, 17 Aug 2009 04:39:35 GMT

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