InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:38:44 GMT20http://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Tom.Thomson (4/7/2010)[/b][hr]The intersting reult here is that the MS reply recognises that the optimiser should recognise that the duplicated expression is just that, and evaluate it only once. So maybe it will get fixed sometime.[/quote]I think it is important to say that this only applies to the specific expansion of COALESCE.I hope it does get fixed, but I don't expect for one moment that they will change the way COALESCE is implemented (with CASE). Will be an interesting one to watch.[quote]It would be good if that were to happen for case statements too, but given that the ANSI definition syas the simple version is a shorthand for the version duplicating the expression I guess it won't. The workaround in the reply works for case too, of course.[/quote]I agree - they won't change the ANSI-esque expansion.Wed, 07 Apr 2010 02:13:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Tom.Thomson (4/7/2010)[/b][hr][quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]CirquedeSQLeil (3/31/2010)[/b][hr]Adam Machanic summarizes his testing [b][url=http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx]here[/url][/b].[/quote]In the comments, Adam notes that there seems to be no difference in SQL Server 2008...[/quote]I think that's him expressing himself badly - the figues he gives for SQLS 2008 indicate that coalesce is now measurably faster than isnull.[/quote]Did it? My memory was of mixed results. Can't actually be bothered to check again though :-DWed, 07 Apr 2010 02:09:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (4/1/2010)[/b][hr][quote][b]Hugo Kornelis (3/31/2010)[/b][hr]...I submitted a bug report... a case I constructed, based on this discussion, where COALESCE(..., 1) may erroneously return NULL (which should of course never happen).[/quote]Voted, repro'd, and a sort-of 'workaround' added.[/quote]The intersting reult here is that the MS reply recognises that the optimiser should recognise that the duplicated expression is just that, and evaluate it only once. So maybe it will get fixed sometime.It would be good if that were to happen for case statements too, but given that the ANSI definition syas the simple version is a shorthand for the version duplicating the expression I guess it won't. The workaround in the reply works for case too, of course.Wed, 07 Apr 2010 01:29:06 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]CirquedeSQLeil (3/31/2010)[/b][hr]Adam Machanic summarizes his testing [b][url=http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx]here[/url][/b].[/quote]In the comments, Adam notes that there seems to be no difference in SQL Server 2008...[/quote]I think that's him expressing himself badly - the figues he gives for SQLS 2008 indicate that coalesce is now measurably faster than isnull.Wed, 07 Apr 2010 01:11:20 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (4/1/2010)[/b][hr]Wow! That is really intriguing....[/quote]Exactly my reaction when I first looked at your Connect item! I have added it to my watch list.Thanks, Hugo.Thu, 01 Apr 2010 04:26:53 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (4/1/2010)[/b][hr][quote][b]Hugo Kornelis (3/31/2010)[/b][hr]...I submitted a bug report... a case I constructed, based on this discussion, where COALESCE(..., 1) may erroneously return NULL (which should of course never happen).[/quote]Voted, repro'd, and a sort-of 'workaround' added.The optimizer seems to break its own rules here - using the two expressions as if they were interchangeable in the final Compute Scalar, and in the Pass Through expression on the second left join too.Correct behaviour can be restored by ensuring column references are used instead of expressions:[code="sql"]IF COALESCE((SELECT TOP (1) NullableFROM DemoWHERE SomeCol = 1), 1) IS NULL[/code][/quote]Wow! That is really intriguing....Thanks, Paul!Thu, 01 Apr 2010 03:23:14 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (4/1/2010)[/b][hr]No, that won't happen. This is because of how a heap scan treats forwarding pointers.[/quote]D'oh! Of course, of course, of course ...thanks![quote]Yes, we can. Didn't I already say so in one of my previous posts?[/quote]Probably, yes. I just wanted to be sure. Summarizing, I guess.PaulThu, 01 Apr 2010 03:11:49 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr]...I submitted a bug report... a case I constructed, based on this discussion, where COALESCE(..., 1) may erroneously return NULL (which should of course never happen).[/quote]Voted, repro'd, and a sort-of 'workaround' added.The optimizer seems to break its own rules here - using the two expressions as if they were interchangeable in the final Compute Scalar, and in the Pass Through expression on the second left join too.Correct behaviour can be restored by ensuring column references are used instead of expressions:[code="sql"]IF COALESCE((SELECT TOP (1) NullableFROM DemoWHERE SomeCol = 1), 1) IS NULL[/code]Thu, 01 Apr 2010 03:06:19 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (4/1/2010)[/b][hr]On the subject of IAM-driven scans. You are, of course, absolutely right about the conditions for an IAM scan on a table with a clustered index (ordered:false, plus NOLOCK or TABLOCK), but it occurs to me that heaps introduce an extra possibility: A row that has been read by an IAM scan can be updated by a concurrent process, such that the row moves (leaving behind a forwarding pointer) to a later page that the original scan has not reached yet. I imagine it is possible to miss a row via the same mechanism, in reverse, but I haven't tested either of these. I just mention it as a point of interest, really.[/quote]No, that won't happen. This is because of how a heap scan treats forwarding pointers. The rules are simple:1. when a forwarding pointer is encountered, the pointer is followed IMMEDIATELY and the forwarded row is then read2. when a forwarded row is encountered, it is skipped (becuase it either has already been read from the forwarding pointer, or will be read if the page holding the forwarding pointer still has to be scanned).So if a row that has already been read is forwarded to a page that has not yet been read, it won't be read again - it's marked as a forwarded row and will be skipped. And in the reverse case, if a row that has not yet been read is forwarded to a page that has already been processed, it will be read once the forwarding pointer is encountered.And so, I finally get a question to an answer that I have asked myself since 2006. I finally know why Microsoft chose to implement "[url=http://sqlblog.com/blogs/hugo_kornelis/archive/2006/11/03/The-table-scan-from-hell.aspx]the table scan from hell[/url]".[quote]Anyhow...my flawed powers of recall aside, can we now agree that rows can be read twice or not at all under READ COMMITTED and REPEATABLE READ - regardless of heaps/ordered scans?[/quote]Yes, we can. Didn't I already say so in one of my previous posts?[quote]One final thing. We started off talking about common sub-expressions being evaluated more than once by CASE. That man Craig Freedman has another entry that describes the issue, and presents a work-around: [url]http://blogs.msdn.com/craigfr/archive/2008/06/27/subqueries-in-between-and-case-statements.aspx[/url]. I don't think it helps with COALESCE directly, but it is very interesting nonetheless.[/quote]Thanks for the link - anything Craig Freedman writes is always worth reading!Thu, 01 Apr 2010 02:54:54 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr]I'm sorry that I keep arguing, but I just can't let this rest...[/quote]Hey, no worries...I do not see this as arguing - it is a fascinating debate! It has also been a good 'refresher' for me, since I haven't looked at this issue in depth for over a year now. Late reply due to time zone issues.[quote]I have to maintain that this can NOT be caused by page splits.[/quote]I agree. I was mis-remembering the cause here. Page splitting is one mechanism that can cause problems at READ UNCOMMITTED, as you rightly point out. Please forgive my memory lapse there - page splits were not relevant to my point. It turns out that the whole issue is partly described in Books Online: [url=http://msdn.microsoft.com/en-us/library/ms190805.aspx]Concurrency Effects[/url]. The relevant extracts are:[b]Page Splits[/b][color="#0000FF"][i]"When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits."[/i][/color][b]Missed/duplicate reads at higher isolation levels[/b][color="#0000FF"][i]"Transactions that are running at the READ COMMITTED level do issue shared locks, but the row or page locks are released after the row is read. In either case, when you are scanning an index, if another user changes the index key column of the row during your read, the row might appear again if the key change moved the row to a position ahead of your scan. Similarly, the row might not appear if the key change moved the row to a position in the index that you had already read. To avoid this, use the SERIALIZABLE or HOLDLOCK hint, or row versioning."[/i][/color]Craig Freedman has an excellent explanation of the latter effect here: [url]http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx[/url] for anyone reading along that is interested.What neither of those state [i]explicitly[/i], is that the same effect occurs at REPEATABLE READ - it is easy to verify that though, using a test based on Craig's reproduction script.On the subject of IAM-driven scans. You are, of course, absolutely right about the conditions for an IAM scan on a table with a clustered index (ordered:false, plus NOLOCK or TABLOCK), but it occurs to me that heaps introduce an extra possibility: A row that has been read by an IAM scan can be updated by a concurrent process, such that the row moves (leaving behind a forwarding pointer) to a later page that the original scan has not reached yet. I imagine it is possible to miss a row via the same mechanism, in reverse, but I haven't tested either of these. I just mention it as a point of interest, really.[quote]The issues that Tony point out in his blog, and the one pointed out by Alex in the blog with all the C# code are not caused by page splits, but by a different issue.[/quote]I knew I had seen the point proven by Alex at some stage - it is a pity I originally referenced the wrong article, and mis-remembered the cause (page splits). I have also just realised that this covers my points above, but I am leaving them in, because it seems a shame to delete all that nice formatting and references ;-)Anyhow...my flawed powers of recall aside, can we now agree that rows can be read twice or not at all under READ COMMITTED and REPEATABLE READ - regardless of heaps/ordered scans? I do apologise that it took so many posts to get the causes nailed down, but the discussion seems to have been of interest to some, so it's not all bad news :-)One final thing. We started off talking about common sub-expressions being evaluated more than once by CASE. That man Craig Freedman has another entry that describes the issue, and presents a work-around: [url]http://blogs.msdn.com/craigfr/archive/2008/06/27/subqueries-in-between-and-case-statements.aspx[/url]. I don't think it helps with COALESCE directly, but it is very interesting nonetheless.Thanks for a most enjoyable discussion!PaulThu, 01 Apr 2010 02:09:19 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspxInteresting discussion. I suspect there are more than a few questions to be built from it. :)Wed, 31 Mar 2010 17:58:18 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspxThanks Hugo for providing that test case to demonstrate the bug.Wed, 31 Mar 2010 14:50:04 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr]my gut feeling is that, if the same data is used multiple times in a query, it should use the same value, regardless of concurrent modifications.(And, of course, if the reference is only actually used once in the query and some internal process of SQL Server duplicates it somewhere on the road from query to execution plan, having the result changed between the first and second execution is only worse).[/quote]Which is why I submitted a bug report: [url=https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null]https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null[/url]. The bug reported is a case I constructed, based on this discussion, where COALESCE(..., 1) may erroneously return NULL (which should of course never happen).For those who are unwilling to go to the Connect site, here is a copy of the repro steps.First, set up the required table as follows:[code="sql"]CREATE TABLE Demo(PrimKey int NOT NULL,SomeCol int NOT NULL DEFAULT (0),Nullable int NULL,Filler char(200) NOT NULL DEFAULT ('Rubbish'),PRIMARY KEY (PrimKey));go-- Quick and dirty way to create 10,000 rows of test dataWITH ManyRows AS(SELECT ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) AS rnFROM sys.objects AS aCROSS JOIN sys.objects AS b)INSERT INTO Demo (PrimKey)SELECT rnFROM ManyRowsWHERE rn BETWEEN 1 AND 10000;go-- Change SomeCol value in one rowUPDATE DemoSET SomeCol = 1WHERE PrimKey = 1234;go[/code]Next, open two connections. In one, paste the code below and run - it will run forever, flipping the Nullable column between 1 and NULL as fast as it can:[code="sql"]WHILE 1 = 1BEGIN;UPDATE DemoSET Nullable = NULLIF(1, Nullable)WHERE PrimKey = 1234;END;[/code]In the second connection, paste and execute the code below:[code="sql"]DECLARE @Good int, @Bad int;SET @Good = 0;SET @Bad = 0;WHILE @Good + @Bad < 1000BEGIN;IF COALESCE((SELECT NullableFROM DemoWHERE SomeCol = 1), 1) IS NULLBEGIN;SET @Bad = @Bad + 1;END;ELSEBEGIN;SET @Good = @Good + 1;END;END;SELECT @Good AS Good, @Bad AS Bad;[/code]On my system, the Bad count actually exceeded the Good count...Votes and repro confirmations of the bug are, of course, welcome.Wed, 31 Mar 2010 14:03:01 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]Hugo Kornelis (3/31/2010)[/b][hr]I've got to correct you AND myself now.[/quote]Did you read the comments in Alex's blog entry (including mine) or the previous article?My point there, as here, is that [i]page splits[/i] can cause rows to be read twice or not at all.I felt sure that you and I had discussed this effect before.Alex's previous article demonstrates COUNT(*) totals going awry, and IIRC Tony R's blog entry is about roughly the same thing, but I am not a fan :-)Please do read the comments on Alex K's blog, because I don't want you to think I am making this up as I go along...;-)[/quote]I'm sorry that I keep arguing, but I just can't let this rest...I must admit that I did not read the comments on Alex' bllog entry. I looked at the blog post, recognised it as one I have seen when it was published and recalled what it was about, then went back here to respond. I have now read the comments as well. And just for the record, I have never thought you are making things up, though I still think you don't understand [b][i]exactly[/i][/b] what is happening in the various cases.Even though, after my initial "never", I now have found that there are indeed cases where even under read committed a row can be skipped or read twice, I have to maintain that this can NOT be caused by page splits. Allow me to explain (almost everyone except Paul should probably skip the next part; it's pretty deep...)SQL Server has two ways of scanning a table or index - using the pointers to process all leaf pages in the "logical" order, or using the IAM (Index Allocation Map) to process pages assigned to the table or index in the order in which they happen to be layed out on disk. The latter version is indeed susceptible to reading rows twice or skipping them, if page splits happen during the read. That's because they can move data from a page that has already been read to a page that has not yet been read, or vice versa. When data is read by following the pointer chain, a page split doesn't change anything - as part of the page split process, the next page/prev page pointer chain is updated so that the "oold" page is replaced by two "new pages" in the original "logical" location.However, SQL Server will only use an IAM scan to read data if two requirements are both met. First, the query execution plan must ask for an *unordered* scan (which means: the data doesn't have to be ordered, but no problem if it happens to be) - for an ordered scan, only following the pointers to read data in logical order can be used. The second requirement is that there must be either a table lock or no locks used in the query. Now, with a table lock you can get no page splits, because they can only be caused by modifications, which are precluded by the table lock. Which leaves only the no locks option available for this scenario - that is, either a NOLOCK hint or a READ_UNCOMMITTED isolation level.The issues that Tony point out in his blog, and the one pointed out by Alex in the blog with all the C# code are not caused by page splits, but by a different issue. Taking Alex' post as an example, what happens is that the COUNT(*) will get the fastest performance by reading the smallest index, which is the index on the AssignedTo column. And the concurrent connection happens to be updating exactly that column. So what happens here, is that a row is read for the COUNT(*), and then the update causes to the indexed column causes it to be relocated to a different page in the same index (which might or might not cause a page split - that is irrelevant here) which is at that time not yet read. And once the scan for the COUNT(*) gets there, it waits for the locks to be released, then reads the page where that row has been added in the mean time, so that it has now been read twice. (And of course, a similar scenario where the row is moved in a different direction would cause the row to be skipped). It's a bit like trying to count children in toddlers' school - you put them in a line, then start counting heads from left to right, but since they can't stay still long enough, you are bound to count some twice and skip others.Wed, 31 Mar 2010 13:39:45 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]CirquedeSQLeil (3/31/2010)[/b][hr]Adam Machanic summarizes his testing [b][url=http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx]here[/url][/b].[/quote]In the comments, Adam notes that there seems to be no difference in SQL Server 2008...[/quote]My testing shows that any application of the two in which I would use them would be negligible in SQL2k5 and 2000.I guess the end result being - it depends.And since 2008 is where people should be moving - mute point?Wed, 31 Mar 2010 13:22:32 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]CirquedeSQLeil (3/31/2010)[/b][hr]Adam Machanic summarizes his testing [b][url=http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx]here[/url][/b].[/quote]In the comments, Adam notes that there seems to be no difference in SQL Server 2008...Wed, 31 Mar 2010 13:17:26 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Trey Staker (3/31/2010)[/b][hr]Good question. Also, I learned a lot more from the discussion from Paul and Hugo.[/quote]Agreed - good discussion.Wed, 31 Mar 2010 12:42:27 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]James Lean (3/31/2010)[/b][hr]Thanks Hugo. That would seem to be a very specific case, I wouldn't have thought it was a good reason to make a general case for using COALESCE over ISNULL.Personally I do tend to use COALESCE, even with only two arguments. That way, if you do need to add more arguments in future you don't have to worry about changing it. Unless I do need to take advantage of the implicit CASTing to the first argument's type, in which case I'll use ISNULL.As always, I suppose, "it depends". ;-)[/quote]My preference is for the use of coalesce. Especially in the scenario that I must use several comparisons. I certainly would not prefer to use isnull in that scenario.[code="sql"]isnull(isnull(isnull(somevalue,othervalue),othervalue1),othervalue2)orcoalesce(somevalue,othervalue,othervalue1,othervalue2)[/code]It seems a lot easier to read and understand as well.Wed, 31 Mar 2010 12:40:21 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr]I've got to correct you AND myself now.[/quote]Did you read the comments in Alex's blog entry (including mine) or the previous article?My point there, as here, is that [i]page splits[/i] can cause rows to be read twice or not at all.I felt sure that you and I had discussed this effect before.Alex's previous article demonstrates COUNT(*) totals going awry, and IIRC Tony R's blog entry is about roughly the same thing, but I am not a fan :-)Please do read the comments on Alex K's blog, because I don't want you to think I am making this up as I go along...;-)Wed, 31 Mar 2010 12:39:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr][quote][b]James Lean (3/31/2010)[/b][hr][quote][b]CirquedeSQLeil (3/30/2010)[/b][hr]However, you will find that many prefer the isnull due to an increase in performance.[/quote]Do you have a supporting link or further information on the performance difference between ISNULL and COALESCE? I was unaware of any significant difference between the two?[/quote]COALESCE is implemented as a quite literal implementation of its definition in the ANSI standard (which is: COALESCE(a, b, ...) is equivalent to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ... ELSE NULL END).So if any of the arguments is a subquery, the implementation if COALESCE((subquery), ...) becomes CASE WHEN (subquery) IS NOT NULL THEN (subquery) ... ELSE NULL END. And the optimizer that sits a bit further down the line does apparently not realize that the two subqueries are identical, so it happily produces a plan that will evaluate the same subquery twice.Without the subqueries, I have never witnessed a measurable performance difference.[/quote]I haven't taken the testing quite to the same level as some of the tests done by others in the community. Adam Machanic summarizes his testing [b][url=http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx]here[/url][/b].Wed, 31 Mar 2010 12:34:00 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]sknox (3/31/2010)[/b][hr]If each statement were indeed treated as its own transaction, then Hugo's query should always return the same results in all three values, because it's a single statement (even though it has subqueries, they are part of a larger single statement) and the Isolation rule would require that the statement see the data as it was before any concurrent query started, or after any concurrent query finished, but not in some intermediate state.[/quote]Well, as already mentioned in my previous post, this is not how SQL Server works. If 10,000 rows are read, each row is read in a committed state, but the 10,000th row can change between the moment the 1st one is read and the 10,000th row itself is read. The only thing that can not happen is a row being read with uncommitted data. So if a concurrent transaction executes[code="sql"]BEGIN TRANSACTION;UPDATE TheTableSET SomeColumn = 'New Value'WHERE RowNumber = 10000;ROLLBACK TRANSACTION;[/code]then under read committed, there is no way the 'New Value' can ever be read. Under read uncommitted or with the nolock hint -which is the same-, you can get this value (which logically never existed in the database) returned. You could even get data returned that violates a constraint and that therefor logically is IMPOSSIBLE to exist in the database, since there always passes at least a minimum amount of time from the moment the change is made until the moment the constraint violation is discovered and the modification is rolled back.But I'm digressing.The point I am actually trying to make is not so much about how SQL Server actually works, but how I believe the relational model is intended to work. For modifications, this is known - regardless of how the engine is actually implemented, it has to act as if all modifications are carried out in a single unsplittable time moment. This is why [font="Courier New"]UPDATE Table SET ColA = ColB, ColB = ColA;[/font] works and actually swaps the columns' values - in a procedural language, you'd need some work area for intermediate storage.For SELECT, my gut feeling is that the behaviour should be similar. But I do not know if this is actually dictated by the ANSI standard. I do have a copy of it, but it's quite hard to read and I can't invest the time right now. So whether backed bty ANSI or not - my gut feeling is that, if the same data is used multiple times in a query, it should use the same value, regardless of concurrent modifications.(And, of course, if the reference is only actually used once in the query and some internal process of SQL Server duplicates it somewhere on the road from query to execution plan, having the result changed between the first and second execution is only worse).Wed, 31 Mar 2010 12:22:25 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]Hugo Kornelis (3/31/2010)[/b][hr]No. That would be possible if you use NOLOCK or READ_UNCOMMITTED, but not at the default level (READ_COMMITTED).[/quote]I know you know this! It has been blogged many times, but here's the entry from your 'blog-colleague' Alex K:[url]http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/04/10/selects-under-read-committed-and-repeatable-read-may-return-incorrect-results.aspx[/url]Double/missed reads are definitely possible and even quite likely even at REPEATABLE READ.[/quote]I've got to correct you AND myself now.The blog post by Alex that you link to proves that you can get inconsistent results, but it does not read a row twice, nor will it skip a row. The issue in Alex' post is that one connection reads the entire table while another connection does a transaction that consists of two updates to two different rows. When the first connection reads the first row involved in the update, the second connection has not yet started updating it so it is not locked; it can be read and the first connection gets the "old" (but at that time still current) value. A few microseconds later, it tries to read the other row, but that has been locked at this time by the pending update. So the read is blocked until the update transaction is either committed or rolled back. The former is what ends up happening, so the "new" value of the row (which at [i]that[/i] time is the current and committed value) is read. I must say that I doubt if this is how Codd and ANSI intended relational databases to work, but it is well known behaviour.In order to demonstrate reading the same row twice or skipping a row, you'd have to use a script that get a SELECT COUNT(*) query wrong, even though the concurrent transaction never inserts or deletes rows. As far as I recall, Itzik Ben-Gan was the first to show that this can happen when the NOLOCK hint is used in the SELECT COUNT(*) query. But when I was googling for the article, I found an excellent description of the issue by Tony Rogerson (who credits Itzik, so I guess my memory is still doing at least [i]some[/i] things right). Here is a link: [url=http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx]http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx[/url].But while searching for this, I also found another article that Tony wrote where he demonstrates that the same thing can indeed happen under the read committed isolation level. This article was new to me, which is why I contradicted you at first, but I was indeed wrong on this, as shown by Tony here: [url=http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx]http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx[/url].Wed, 31 Mar 2010 12:08:31 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]sknox (3/31/2010)[/b][hr]..."each statement being treated as its own transaction". However, Microsoft's documentation does not explicitly state this.[/quote][url]http://msdn.microsoft.com/en-us/library/ms175523.aspx[/url] does.[quote]If each statement were indeed treated as its own transaction, then Hugo's query should always return the same results in all three values, because it's a single statement (even though it has subqueries, they are part of a larger single statement) and the Isolation rule would require that the statement see the data as it was before any concurrent query started, or after any concurrent query finished, but not in some intermediate state.[/quote]The isolation rule says:[i]Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.[/i]But, also see: [url=http://msdn.microsoft.com/en-us/library/ms189122.aspx]Isolation Levels in the Database Engine[/url]That link is really important in understanding what the various isolation level do, and do not, guarantee.Please also read the blog entry I referred to before, showing how data can be double-read or skipped entirely under READ COMMITTED or REPEATABLE READ.PaulWed, 31 Mar 2010 11:56:24 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr]I think my 'distinctly dodgy' comment warrants a little expansion :blush:AFAIK, SQL Server has never set out to guarantee anything about a query behaving as if it occurred all-at-once at a single point in time. You can approximate this behaviour, indirectly, using the SERIALIZABLE isolation level. Statement-level consistency can also be achieved using READ_COMMITTED_SNAPSHOT. Transaction-level consistency requires full SNAPHSOT. None of them will explicitly perform the sort of 'expression collapsing' you seem to be after - at least not with anything that accesses data. Compute Scalar operators are frequently used to re-use computed constant values in a query plan, of course.[/quote]This is definitely a gray area. In SQL Server, the default transaction mode is Autocommitment. This means that each statement is committed when it completes (see [url]"http://msdn.microsoft.com/en-us/library/ms187878(v=SQL.100).aspx"[/url].) That has most frequently been explained to me as "each statement being treated as its own transaction". However, Microsoft's documentation does not explicitly state this.If each statement were indeed treated as its own transaction, then Hugo's query should always return the same results in all three values, because it's a single statement (even though it has subqueries, they are part of a larger single statement) and the Isolation rule would require that the statement see the data as it was before any concurrent query started, or after any concurrent query finished, but not in some intermediate state.Wed, 31 Mar 2010 11:06:49 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Trey Staker (3/31/2010)[/b][hr]Also, I learned a lot more from the discussion from Paul and Hugo.[/quote]Hey thanks, Trey. Hugo's always good for a quality discussion :-)Wed, 31 Mar 2010 09:32:03 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspxGood question. Also, I learned a lot more from the discussion from Paul and Hugo.Wed, 31 Mar 2010 09:14:07 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspxI think my 'distinctly dodgy' comment warrants a little expansion :blush:AFAIK, SQL Server has never set out to guarantee anything about a query behaving as if it occurred all-at-once at a single point in time. You can approximate this behaviour, indirectly, using the SERIALIZABLE isolation level. Statement-level consistency can also be achieved using READ_COMMITTED_SNAPSHOT. Transaction-level consistency requires full SNAPHSOT. None of them will explicitly perform the sort of 'expression collapsing' you seem to be after - at least not with anything that accesses data. Compute Scalar operators are frequently used to re-use computed constant values in a query plan, of course.Wed, 31 Mar 2010 08:58:05 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr]No. That would be possible if you use NOLOCK or READ_UNCOMMITTED, but not at the default level (READ_COMMITTED).[/quote]I know you know this! It has been blogged many times, but here's the entry from your 'blog-colleague' Alex K:[url]http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/04/10/selects-under-read-committed-and-repeatable-read-may-return-incorrect-results.aspx[/url]Double/missed reads are definitely possible and even quite likely even at REPEATABLE READ.[quote]Except that I believe that when it is a single statement, there should not be three different scans. Especially BECAUSE that could return inconsistent results. But I think we'll just have to agree to disagree on this one! ;)[/quote]Ah right, I see your point. You think the three identical sub-queries should be 'collapsed' into one execution by SQL Server. Sounds distinctly dodgy to me :-)Wed, 31 Mar 2010 08:42:38 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]Hugo Kornelis (3/31/2010)[/b][hr]I would agree that, depending on transaction isolation level and @@TRANCOUNT, the results of the three statements might be different on a busy server. But for your code, with the single SELECT, I would consider anything but three equal values a bug.[/quote]I don't see why. The COUNT done at the default isolation level could easily read the same values twice or skip values completely.[/quote]No. That would be possible if you use NOLOCK or DIRTY_READ, but not at the default level (READ_COMMITTED).[quote]Three different scans could easily, in general, produce three different results. The fact that it is a single statement doesn't change that.[/quote]Except that I believe that when it is a single statement, there should not be three different scans. Especially BECAUSE that could return inconsistent results.But I think we'll just have to agree to disagree on this one! ;)Wed, 31 Mar 2010 08:28:02 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr]I would agree that, depending on transaction isolation level and @@TRANCOUNT, the results of the three statements might be different on a busy server. But for your code, with the single SELECT, I would consider anything but three equal values a bug.[/quote]I don't see why. The COUNT done at the default isolation level could easily read the same values twice or skip values completely. (Ignore the complications introduced by my use of system tables for the sake of an easy example). Three different scans could easily, in general, produce three different results. The fact that it is a single statement doesn't change that.[quote]And in situations such as the scenario that brought us here, where the actual subquery occurs only once in the submitted query and it's SQL Server itself that expands it, I'd consider it even more buggy![/quote]That I can see, but if we accept that the 'compressed' CASE statement is purely a syntactical convenience, and that it is merely shorthand for the searched expression...then clearly the subqueries must be evaluated x number of times, due to the fact that data access makes the expression non-deterministic.I agree that this whole area is a very odd duck though - much like the multiply-referenced CTE issue.Wed, 31 Mar 2010 08:14:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]Hugo Kornelis (3/31/2010)[/b][hr]I disagree. If the query includes some non-deterministic code (such as NEWID()), there might be room for argument. But if the query in itself is deterministic, than the results should be deterministic.[/quote]I do see your point, but I'm not sure I agree. Once the decision has been taken to expand the CASE, the sub-queries must be re-evaluated. The fact that data access is involved makes the result non-deterministic - the result depends on the state of the database.We would not expect the following code to be 'collapsed' into a single sub-query execution:[code="sql"]SELECT result1 = (SELECT COUNT(*) FROM sys.objects), result2 = (SELECT COUNT(*) FROM sys.objects), result3 = (SELECT COUNT(*) FROM sys.objects)[/code][/quote]Actually, I would expect [b]exactly[/b] that to happen. This is a single query, supposed to return results as if it were executed at a single moment in time. Transaction isolation levels govern what happens if multiple statements are executed in succession. So if I change your code to[code="sql"]SELECT @result1 = (SELECT COUNT(*) FROM sys.objects);SELECT @result2 = (SELECT COUNT(*) FROM sys.objects);SELECT @result3 = (SELECT COUNT(*) FROM sys.objects);[/code]then I would agree that, depending on transaction isolation level and @@TRANCOUNT, the results of the three statements might be different on a busy server. But for your code, with the single SELECT, I would consider anything but three equal values a bug.And in situations such as the scenario that brought us here, where the actual subquery occurs only once in the submitted query and it's SQL Server itself that expands it, I'd consider it even more buggy!Wed, 31 Mar 2010 07:56:32 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr]That is caused by a related and similar reason. This "simple" CASE is also defined in ANSI in terms of the "searched" CASE (and apparently implemented in this way):[/quote]Yep, you can see that by examining the query plan produced - the CASE is always expanded to the searched form.[quote]I disagree. If the query includes some non-deterministic code (such as NEWID()), there might be room for argument. But if the query in itself is deterministic, than the results should be deterministic.[/quote]I do see your point, but I'm not sure I agree. Once the decision has been taken to expand the CASE, the sub-queries must be re-evaluated. The fact that data access is involved makes the result non-deterministic - the result depends on the state of the database.We would not expect the following code to be 'collapsed' into a single sub-query execution:[code="sql"]SELECT result1 = (SELECT COUNT(*) FROM sys.objects), result2 = (SELECT COUNT(*) FROM sys.objects), result3 = (SELECT COUNT(*) FROM sys.objects)[/code][quote]A query is supposed to behave as if it is evaluated at a single moment in time; implementation choices should never influence the results.[/quote]I think this is the crux of the matter. You are only guaranteed this sort of behaviour when running at SERIALIZABLE or one of the row-versioning isolation levels, as I mentioned before. (Also see the example above)Wed, 31 Mar 2010 07:45:25 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]Hugo Kornelis (3/31/2010)[/b][hr]So if any of the arguments is a subquery, the implementation if COALESCE((subquery), ...) becomes CASE WHEN (subquery) IS NOT NULL THEN (subquery) ... ELSE NULL END.[/quote]That reminds me of a CASE construction like the following:[code="sql"]SELECT CASE CHECKSUM(NEWID()) % 3 + 1 WHEN 1 THEN 'One' WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' ELSE 'WTF?' END[/code]...which quite frequently hits the ELSE, and that surprises some people.[/quote]That is caused by a related and similar reason. This "simple" CASE is also defined in ANSI in terms of the "searched" CASE (and apparently implemented in this way):[code="sql"]SELECT CASE WHEN CHECKSUM(NEWID()) % 3 + 1 = 1 THEN 'One' WHEN CHECKSUM(NEWID()) % 3 + 1 = 2 THEN 'Two' WHEN CHECKSUM(NEWID()) % 3 + 1 = 3 THEN 'Three' ELSE 'WTF?' END;[/code]If you had replaced the CHECKSUM expression with a complex subquery and added another 7 WHEN clauses, the end result would be a query that evaluates the same subquery ten times in a row! <shudder>[quote][quote]And the optimizer that sits a bit further down the line does apparently not realize that the two subqueries are identical, so it happily produces a plan that will evaluate the same subquery twice.[/quote]Once expanded by the CASE, is it true to say that they are identical? It seems to me that the results are non-deterministic, so it would not be safe to collapse these evaluations. (One might argue that it would be safe at the SERIALIZABLE isolation level (or one of the row-versioning isolation levels), but the plan would not then be reusable).[/quote]I disagree. If the query includes some non-deterministic code (such as NEWID()), there might be room for argument. But if the query in itself is deterministic, than the results should be deterministic. A query is supposed to behave as if it is evaluated at a single moment in time; implementation choices should never influence the results.Wed, 31 Mar 2010 07:02:25 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspxlol ownedgood questionWed, 31 Mar 2010 06:55:54 GMTsistemas 95572http://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]James Lean (3/31/2010)[/b][hr]That would seem to be a very specific case, I wouldn't have thought it was a good reason to make a general case for using COALESCE over ISNULL. Personally I do tend to use COALESCE, even with only two arguments. That way, if you do need to add more arguments in future you don't have to worry about changing it. Unless I do need to take advantage of the implicit CASTing to the first argument's type, in which case I'll use ISNULL. As always, I suppose, "it depends". ;-)[/quote]Indeed it does depend.I think it's a shame, in a way, that almost every QotD or thread that mentions ISNULL or COALESCE always attracts a certain element determined to prove that one is always better than the other. (I'm not including you in that, by the way)My own view is that the two functions are very different, and each has its merits. I use both regularly, depending on the query I am writing. I realise that such a balanced view lacks any sort of excitement factor, but there it is :-)Wed, 31 Mar 2010 06:49:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]Hugo Kornelis (3/31/2010)[/b][hr]So if any of the arguments is a subquery, the implementation if COALESCE((subquery), ...) becomes CASE WHEN (subquery) IS NOT NULL THEN (subquery) ... ELSE NULL END.[/quote]That reminds me of a CASE construction like the following:[code="sql"]SELECT CASE CHECKSUM(NEWID()) % 3 + 1 WHEN 1 THEN 'One' WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' ELSE 'WTF?' END[/code]...which quite frequently hits the ELSE, and that surprises some people.[quote]And the optimizer that sits a bit further down the line does apparently not realize that the two subqueries are identical, so it happily produces a plan that will evaluate the same subquery twice.[/quote]Once expanded by the CASE, is it true to say that they are identical? It seems to me that the results are non-deterministic, so it would not be safe to collapse these evaluations. (One might argue that it would be safe at the SERIALIZABLE isolation level (or one of the row-versioning isolation levels), but the plan would not then be reusable).I blame the expansion of the CASE - which is apparently a documented design decision. I can live with it, though.Last thing: the optimizer does a great job at avoiding the extra subquery evaluations where possible. The plan produced is quite likely to feature a PassThrough expression on the joins, so that the subquery is not re-evaluated if a prior term in the COALESCE already produced a non-NULL value.Wed, 31 Mar 2010 06:44:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspxThanks Hugo. That would seem to be a very specific case, I wouldn't have thought it was a good reason to make a general case for using COALESCE over ISNULL.Personally I do tend to use COALESCE, even with only two arguments. That way, if you do need to add more arguments in future you don't have to worry about changing it. Unless I do need to take advantage of the implicit CASTing to the first argument's type, in which case I'll use ISNULL.As always, I suppose, "it depends". ;-)Wed, 31 Mar 2010 03:02:57 GMTJames Leanhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]James Lean (3/31/2010)[/b][hr][quote][b]CirquedeSQLeil (3/30/2010)[/b][hr]However, you will find that many prefer the isnull due to an increase in performance.[/quote]Do you have a supporting link or further information on the performance difference between ISNULL and COALESCE? I was unaware of any significant difference between the two?[/quote]COALESCE is implemented as a quite literal implementation of its definition in the ANSI standard (which is: COALESCE(a, b, ...) is equivalent to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ... ELSE NULL END).So if any of the arguments is a subquery, the implementation if COALESCE((subquery), ...) becomes CASE WHEN (subquery) IS NOT NULL THEN (subquery) ... ELSE NULL END. And the optimizer that sits a bit further down the line does apparently not realize that the two subqueries are identical, so it happily produces a plan that will evaluate the same subquery twice.Without the subqueries, I have never witnessed a measurable performance difference.Wed, 31 Mar 2010 02:43:21 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]CirquedeSQLeil (3/30/2010)[/b][hr]However, you will find that many prefer the isnull due to an increase in performance.[/quote]Do you have a supporting link or further information on the performance difference between ISNULL and COALESCE? I was unaware of any significant difference between the two?Wed, 31 Mar 2010 02:35:43 GMTJames Leanhttp://www.sqlservercentral.com/Forums/Topic893447-2661-1.aspx[quote][b]richard.maw (3/30/2010)[/b][hr]Can anyone give an account of why the semantics of isnull and coalesce (with two args) is different?[/quote]Here is a nice article about this: [url]http://blogs.msdn.com/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx[/url][quote]COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine....COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function[/quote]Wed, 31 Mar 2010 00:02:18 GMTvk-kirov