InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 03:43:29 GMT20http://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxI need some help here... how can the same amount of data sent on the wire require less resources, less work on the 2nd run... all that because of the query itself?Mon, 01 Dec 2008 07:02:47 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxsee Previous query's Network statistics (Byte sent and receide) hereunder:Network Statistics Number of server roundtrips 1 1.0000 TDS packets sent from client 1 1.0000 TDS packets received from server 1 1.0000[b] Bytes sent from client 1184 1184.0000 Bytes received from server 816 816.0000[/b]Mon, 01 Dec 2008 06:03:21 GMTarkhanhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]arkhan (12/1/2008)[/b][hr]One line statement:-[b]SELECT ID,TransactionDate,Balance,CASE WHEN id<5 THEN 0 ELSE(select SUM(balance) FROM Accounts where ID between acc.id-4 and acc.id) end as RunningTotal from Accounts acc[/b]The Network rtraffic would be lesser, as per below statistics:-Network Statistics Number of server roundtrips 1 1.0000 TDS packets sent from client 1 1.0000 TDS packets received from server 1 1.0000 Bytes sent from client 378 378.0000 Bytes received from server 771 771.0000[/quote]Ummm.... lesser than what? I don't see any actual comparison here...Mon, 01 Dec 2008 05:48:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxCan you explain to me why the network traffic would change for the exact same result set... having the exact same amount of data?I know that query is about 30 bytes smaller... but I mean come on, that won'thurt any network any time soon!Mon, 01 Dec 2008 05:21:30 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxOne line statement:-[b]SELECT ID,TransactionDate,Balance,CASE WHEN id<5 THEN 0 ELSE(select SUM(balance) FROM Accounts where ID between acc.id-4 and acc.id) end as RunningTotal from Accounts acc[/b]The Network rtraffic would be lesser, as per below statistics:-Network Statistics Number of server roundtrips 1 1.0000 TDS packets sent from client 1 1.0000 TDS packets received from server 1 1.0000 Bytes sent from client 378 378.0000 Bytes received from server 771 771.0000Mon, 01 Dec 2008 05:05:08 GMTarkhanhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxI know what you mean, I used to take all those comments as they weredirected straight at me some time... forgetting the nature of the boeard :)... but that's makes you so god ;).Sat, 29 Nov 2008 10:30:10 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxThat would be true. Some good fellow had the link to my Triangular join article with the comment that folks should take a gander. I didn't mean to scare him away with my comment. :blush:Sat, 29 Nov 2008 07:48:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Jeff Moden (11/28/2008)[/b][hr]Heh... Read it? I wrote it! ;)[/quote]Somebody deleted some posts because that message is not making any sense now!Sat, 29 Nov 2008 05:13:51 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxHeh... Read it? I wrote it! ;)Fri, 28 Nov 2008 20:40:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxThanks for the correction Jeff... you clearly said what I meant silently :P.A 5 row triangular join won't kill many servers.... but what if the report needs to return the running total for @X days... that could quickly because a huge bottleneck for the server. I have no time to test now, but my guess is that going from 5 to even only 15 days could really hurt that query!Fri, 28 Nov 2008 09:34:14 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Ninja's_RGR'us (11/28/2008)[/b][hr]I must admit I was wrong on this one....That 1M rows query only processes 95MB of data (from stats in actual plan). I was expecting some sort of triangular join here, but the speed is really not all that bad. My statement returns the 1M rows in les than 8 sec (download included)... so that version might very well make it to production.Thanks pp.pragnesh[/quote]Actually, triangular joins [i]are [/i]present... the thing is that they're very very small... limited to 4 or 5 rows each and they "stop" when the rules of distinction have been met in this case. Like I said in the article I wrote about triangular joins and just like most anything else... they're not ALL bad... "It Depends".Fri, 28 Nov 2008 09:24:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Hugo Kornelis (11/28/2008)[/b][hr][/quote]Yes, I agree... you cannot rely on anything other than an OrderBy on Select statements.And, yes... I believe that everyone knows that "deprecation" comes with plenty of warnings over at least 1 version and frequently more.I'll have to look for the deprecation warning on the alias = expression thing, but I do remember it being in the MS documentation for 2k5... I just can't remember where. Might have been in the "what's new" bit of BOL, but I don't know for sure. I'll look for it when I get a bit of time.All of the things you say are mostly true... everything you say about a change, could actually happen. But, the "quirky" update and the way it operates has been in existance since before the beginning and hasn't changed because it's at the root of the engine. You can certainly post your warnings about the risk, but I do think they are a bit on the over cautious side... psuedo-cursors of this nature have been around for a very long time.Fri, 28 Nov 2008 09:21:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx... of course the cost of this query would be quite more considerable (maybe 2 or 3 times) if this was a real table with 1000 bytes of data and if the clustered index was on the date column is it probably would. Just something to keep in mind when using in real life.Fri, 28 Nov 2008 04:51:00 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxI must admit I was wrong on this one....USE tempdb GOCREATE TABLE dbo.Accounts(ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,TransactionDate datetime,Balance float)goDECLARE @i as INTset @i = 0while @i < 1000000begin insert into dbo.Accounts (TransactionDate, Balance) SELECT DATEADD(DD, @i, GETDATE()), CEILING(RAND() * 999) SET @i = @i + 1end--SELECT * FROM dbo.Accountsgo--activate execution plans before running this onegoSELECT Accounts.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance, ( SELECT Sum( Balance ) FROM ( SELECT Top 5 A.Balance FROM Accounts A WHERE A.ID <= Accounts.ID ORDER BY ID DESC ) AS A ) AS RunningTotalFROM dbo.Accounts goDROP TABLE dbo.AccountsThat 1M rows query only processes 95MB of data (from stats in actual plan). I was expecting some sort of triangular join here, but the speed is really not all that bad. My statement returns the 1M rows in les than 8 sec (download included)... so that version might very well make it to production.Thanks pp.pragneshFri, 28 Nov 2008 04:48:45 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxTry running that on a 1M rows table then tell us how long it takes to run... assuming your query is done before xmas!!!Fri, 28 Nov 2008 04:25:06 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxThis query takes care if some IDs are missing..... :)SELECT Accounts.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance, ( SELECT Sum( Balance ) FROM ( SELECT Top 5 A.Balance FROM Accounts A WHERE A.ID <= Accounts.ID ORDER BY ID DESC ) AS A ) AS RunningTotalFROM AccountsFri, 28 Nov 2008 04:19:27 GMTpp.pragneshhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxBelow my Query is taking if some IDs are missing... :)SELECT Accounts.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance, ( SELECT Sum( Balance ) FROM ( SELECT Top 5 A.Balance FROM Accounts A WHERE A.ID <= Accounts.ID ORDER BY ID DESC ) AS A ) AS RunningTotalFROM AccountsFri, 28 Nov 2008 04:17:48 GMTpp.pragneshhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Jeff Moden (11/27/2008)[/b][hr][quote][b]Lynn Pettis (11/27/2008)[/b][hr]Okay, can't wait to see Jeff's response. I was using a technique I picked up from him for completing running totals.[/quote]Hugo is putting out a lot of "what ifs" and he and I have already had this argument on the "Running Total" article. He hasn't been able to make your code or my code or any properly written running total code using the "quirky update" break. If you add the correct index hint to your code, he won't be able to break that, either.[/quote]Hi Jeff,All my "what ifs" are actually dependencies on undocumented behaviour you take. Fine if you choose to take those risks, but if you're going to encourage others to do so, without adding a waring in large red letters that the behaviour may change without notice, then I'm going to add that warning.I just posted code that shows that a SELECT query with the index hints you recommend will not always return rows in sorted order. I'll admit that I did not (yet) break the update. More on that below.[quote]I think that people are way too paranoid about undocumented features, especially this one. In order for them to release a service pack that would destroy the ability of the "quirky" update to work, they would have to rewrite half the engine to change the way clustered indexes work behind the scenes and how Update works behind the scenes.[/quote]The only reason I could not break the update is that the query optimizer creates a plan that forces an ordered clustered index scan. And I don't know about you, but I fail to see why it doesn't allow an unordered scan. Probably because some details of how exactly the update is processed internally. Details that might be changed - and that would not require rewriting half the engine.[quote]The way clustered indexes work and the way Update works with indexes are both fully documented. Putting 2+2 together, is apparently not.[/quote]The way UPDATE works with variables in the SET clause is also documented. I never realised this before, but I just saw it when I was checking some other info. Here's the quote from Books Online for SQL Server 2005 (sept 2007 update). You can find it in the page about the UPDATE statement:"Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE statement affects a single record. If the UPDATE statement affects multiple records, to return the old and new values for each record, use the OUTPUT clause."[quote]The other thing is that using ONLY fully documented features is no guarantee that you're not going to have to rewrite code. For example, in SQL Server 2000 SP3a, it was fully documented as to what privs you needed to use the wonderful sp_MakeWebTask extended store procedure. SP4 came out and suddenly you needed SA privs to use it... a change to a fully documented procedure broke a lot of people's code.[/quote]Agree. This is not Microsoft's usual style, but they sure emssed up here.[quote]Here's another fully documented feature right straight out of BOL...(...)[color="RED"][font="Arial Black"]column_alias = expression [/font][/color](...)... yet the boogers at Microsoft have deprecated it.[/quote]They did? Hmm, that must have gone past me. Where is this documented? (Not that I mind much, I personally always preferred the "expression AS alias" version).However, this actually just proves my point. Of course changes will be made. But the normal path for changes to documented features is to announce deprecation without removing support for one or two versions, then continuing to support it in backward compatibility mode only for one or two more versions. This will give you an advance warning, and plenty time to replace the code. Not so with changes to undocumented features - they can (and will!) be changed without notice. Remember views with TOP 100 PERCENT ... ORDER BY in SQL Server 2000, that stopped working in SQL Server 2005? Or GROUP BY always returning ordered results, even without ORDER BY, in SQL Server 6.5?[quote]If you follow the best practice of testing service packs on non production machines before you put it into production, you'll never get caught with your pants down.[/quote]Except that some behaviour will only occur under heavy load. I had to go to great lengths to cause pages to be allocated out of order in my repro code, and I only did so because I knew that this would break your code. Low-use test systems will not usually get such allocations - but heavily used production systems might.[quote]Heh... and until they either break the hell out of the engine or someone comes up with a better and faster way to do running totals, my recommendation would be to continue to use the "quirky" update to do them.[/quote]And my recommendation would be to calculate them on the client :D. And if that is really impossible, then use either a cursor or a set-based query with a correlated subquery, depending on the data distribution.Fri, 28 Nov 2008 02:47:07 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Lynn Pettis (11/27/2008)[/b][hr]Yep, the code you wrote works about the way you say it will, but I figured out the problem in your code in the second block. First, I wouldn't write a query using the NOLOCK hint unless absolutely necessary. Take your code in the second code block, and change your last query to use the query hint (index = 1) IN PLACE OF the (nolock) you have. Better yet, copy the query and run both (one with nolock and one with index = 1). You will see that the one with (index = 1) returns an ordered set.Edit: And just for s and g's, I ran it also with no hint on the table with the clustered primary key, still returned an ordered result set.Another Edit: Added a query using both hints (nolock, index = 1), returned an ordered result set.[/quote]Hi Lynn,My bad. I blundered when I wrote the code; I intended to use the locking hint Jeff recommends in his article, which is TABLOCKX.I just tested with both "WITH (TABLOCKX)" and "WITH (TABLOCKX, INDEX = 1)", and I got an unordered result in both cases.As I said to Jacob in my previous reply, both NOLOCK and TABLOCK allow an index scan to be driven from the IAM pages instead of the NxtPage pointers. Row- and pagelocking do not allow this in the current version (I tested on SQL Server 2005, SP2). However, I see no reason why Microosoft could not choose to implement some other mechanism to prevent duplicated or missed rows when using an index scan with row or page locking from the IAM pages - after all, they have already implemented such a mechanism for heaps. (Google "table scan from hell" for more info). I did not test this behaviour on SQL Server 2008 yet - for all I know, it may lareaby have been changed!! :w00t:Fri, 28 Nov 2008 02:19:50 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Jacob Luebbers (11/27/2008)[/b][hr]Hey Hugo,[quote][b]Hugo Kornelis (11/27/2008)[/b][hr]Point taken. Though the flipside is that TABLOCK increases the chance to get an unordered scan instead of an ordered one. More on that below.[/quote]Maybe I'm being thick, but I don't see from your comments how TABLOCKX will increase the chance of an unordered scan... am I missing something?[/quote]Hi Jacob,That's quite technical. Basically, there are several requirements before you get a scan that uses the IAM pages (accessing pages allocated to the table in their "physical" order -the quotes are because the file may be physically fragmented or striped in which case the real physical order might be different- to take full advantage of read ahead and to minimize head movement) instead of a scan that uses the PrevPage/NextPage pointers to read leaf pages in their logical order.1. The optimizer has to ask for an unordered scan. It will ask for an ordered scan if it needs the rows to be sorted in the logical order imposed by the index, either for a later operation in the plan or because of an ORDER BY on the outermost query. In other cases, it will ask for an unordered scan, defering the decision to the query engine2. The number of pages in the table has to be above a certain threshold. The IAM scan is faster, but there is a fixed overhead cost of setting it up, so if only a few pages are involved that overhead will be more than the efficiency gain and the IAM scan will not be chosen. This is why I used lots of rows in my example code.3. Either no locking (dirty reads) or full table locking has to be in effect. I think that this is to prevent a row that has already been read from being moved to another page (due to an update of the value in the clustered index column, or due to page splits) while the scan is in process, and then being read once more. With table locking, this can't happen, and with dirty reads, you state that you are willing to take the risk. However, I never really understood this reasoning, for a change to the indexed column can also cause a row to be read twice or not at all if it happens during an ordered scan....[quote]Jeff's final solution (bottom of the article) doesn't use an ordered subquery - just the clustered index hint to guarantee order.[/quote]Sure, but I was writing this as a reply to your reaction on the merry go round scan. However, I later saw that I didn't read Jeff's article good enough. He never mentions merry go round scans, but only a "merry go round index" - what I would probably describe as a heavily fragmented index. This is unrelated. A merry go round scan (aka advanced scan) occurs when the engine detects that a scan is already in progress - in this case, it will "piggyback" on the existing scan, and once that finished it will return to the start of the index and read from there until it arrives it the starting point. This is an Enterprise Edition only feature, and it's described at [url=http://msdn.microsoft.com/en-us/library/aa175258(SQL.80).aspx]http://msdn.microsoft.com/en-us/library/aa175258(SQL.80).aspx[/url].Fri, 28 Nov 2008 02:09:53 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote]I believe that Phil Factor can provide a link from the "Simple-Talk" forum that would provide similar methods.[/quote]It wasn't me, but Robyn, [url=http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/]Robyn Page's SQL Server Cursor Workbench (24 January 2007 -http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/)[/url][i]'--and here is a very different technique that takes advantage of the quirky behavior of SET in an UPDATE command in SQL Server'[/i]. ... it is an old Sybase SQL Server trick which has been around the SSC forums for years. The only problems I've ever had was when using this together with concatenation. In SQL Server 2000, one occasionally needed to add a blank string to the expression, but they fixed it in 2005. Microsoft may huff and puff and say that the technique is unsupported but it is all wind. If they changed the behavior of UPDATE now, all sorts of systems dating back over the past decade wouldn't be upgradeable to the new version. Because the technique gets more and more important as the database size increases, I've seen it in complex financial systems at the heart of international companies. Microsoft Marketing Department would soon put a stop to the hotheads. I have to agree with Jeff about [b]sp_makewebtask.[/b] It was useless for its original purpose of putting out web-based reports, but it was a gloriously simple way of making regular spreadsheet-based reports, and we all used it as such. I don't think that Microsoft really did much research before they deprecated it. Fri, 28 Nov 2008 01:27:11 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Hugo Kornelis (11/27/2008)[/b][hr][quote][b]Jeff Moden (11/27/2008)[/b][hr][quote][b]Hugo Kornelis (11/27/2008)[/b][hr]What I was refering too is the lack of ROW_NUMBER() in SQL Server 2000. This means you'll either have to take your chance with IDENTITY, at the risk of gaps, as the author of this article did; or you have to use a correlated subquery to calculate the row number on the fly, which can result in dramatic performance as the amount of rows grows. Plus, the queries tend to get long and hard to understand.[/quote]Nope... in SQL Server 2000, just do a SELECT INTO a temp table with the IDENTITY function and the author's code works just fine without any difficulty for length or understanding.[/quote]Hi Jeff,You're right. When using SELECT INTO a temp table with the IDENTITY function (*), then there will not be any gaps and the range of A.ID-4 up to A.ID will always have 5 rows. But unless I overlooked something, this was not in the article. To me, the article appears to imply that any IDENTITY row can be used for this. And since many tables already have an IDENTITY column, often with gaps in the sequence due to deleted data or rolled back inserts, I thought it'd be better to point out this danger.(*) There is another potential problem here. I know that there is only one situation where Microsoft [i]guarantees[/i] that identity values are given out in the expected order when using ORDER BY in an INSERT statement, but I can never recall if this guarantee is for SELECT INTO with the IDENTITY() function, of for INSERT ... SELECT on a table with a predefined IDENTITY column. And I can never find this particular bit of documentation when I need it. I [b]think[/b] that SELECT INTO with the IDENTITY() function is the supported scenario, but if you are going to use this in production you'd probably better doublecheck first, for my memory is known to .... aaahh, what was I going to say again? :)[/quote]Shoot... I know it wasn't in the article, Hugo... that's why I posted it! :DI'll see if I can find it, but I believe the only time that the IDENTITY function doesn't sort correctly on a SELECT INTO/ORDER BY is when the ORDER BY is based on column aliases and those column aliases refer to indeterminate functions used in the Select List.Thu, 27 Nov 2008 21:32:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Lynn Pettis (11/27/2008)[/b][hr]Okay, can't wait to see Jeff's response. I was using a technique I picked up from him for completing running totals.[/quote]Hugo is putting out a lot of "what ifs" and he and I have already had this argument on the "Running Total" article. He hasn't been able to make your code or my code or any properly written running total code using the "quirky update" break. If you add the correct index hint to your code, he won't be able to break that, either. I think that people are way too paranoid about undocumented features, especially this one. In order for them to release a service pack that would destroy the ability of the "quirky" update to work, they would have to rewrite half the engine to change the way clustered indexes work behind the scenes and how Update works behind the scenes. The way clustered indexes work and the way Update works with indexes are both fully documented. Putting 2+2 together, is apparently not. The other thing is that using ONLY fully documented features is no guarantee that you're not going to have to rewrite code. For example, in SQL Server 2000 SP3a, it was fully documented as to what privs you needed to use the wonderful sp_MakeWebTask extended store procedure. SP4 came out and suddenly you needed SA privs to use it... a change to a fully documented procedure broke a lot of people's code.Here's another fully documented feature right straight out of BOL...[font="Arial Black"]SELECT Clause[/font]Specifies the columns to be returned by the query.SyntaxSELECT [ ALL | DISTINCT ] [ TOP n [ PERCENT ] [ WITH TIES ] ] ::= { * | { table_name | view_name | table_alias }.* | { column_name | expression | IDENTITYCOL | ROWGUIDCOL } [ [ AS ] column_alias ] | [color="RED"][font="Arial Black"]column_alias = expression [/font][/color] } [ ,...n ] ... yet the boogers at Microsoft have deprecated it. It was absolutely wonderful for building up a Select, testing the hell out of it, and then easily converting it to an Update. Also makes for some pretty code if you format it correctly.So, the continuous din from people insisting that only documented features be used because the undocumented ones could change any time are wrong because they change even the documented features. Use what you need at the time to get the job done. If you follow the best practice of testing service packs on non production machines before you put it into production, you'll never get caught with your pants down.Heh... and until they either break the hell out of the engine or someone comes up with a better and faster way to do running totals, my recommendation would be to continue to use the "quirky" update to do them.Just in case anyone wants to see the full article on using the "quirky" update to do running totals and other super high speed "miracles" that would normally require a cursor or while loop, here's the link...[url=http://www.sqlservercentral.com/articles/Advanced+Querying/61716/][font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]... Lynn, bless his soul, actually has it in his signature line.I believe that Phil Factor can provide a link from the "Simple-Talk" forum that would provide similar methods.Thu, 27 Nov 2008 21:24:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Hugo Kornelis (11/27/2008)[/b][hr][quote][b]Jacob Luebbers (11/27/2008)[/b][hr]Hey Hugo,[quote][b]Hugo Kornelis (11/27/2008)[/b]...* The optimizer might choose a non-clustered index to drive the query...[/quote]The technique also uses an index hint specifying the clustered index, with TABLOCKX to ensure that no other modifications could occur to the table during our UPDATE.[/quote]Hi Jacob,Point taken. Though the flipside is that TABLOCK increases the chance to get an unordered scan instead of an ordered one. More on that below.[quote][b]Jacob Luebbers (11/27/2008)[/b][quote][b]Hugo Kornelis (11/27/2008)[/b]...* The query engine might perform a merry-go-round scan...[/quote]Jeff's article covers this (following on from comments from Gail on the merry-go-round index behaviour): [url=http://www.sqlservercentral.com/articles/Advanced+Querying/61716/]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url].[/quote]I see that Jeff defends his use of ORDER BY in a subquery by refering to observed behaviour, not to any documentation. Exactly the danger I am warning about.In fact, this behaviour of TOP 100 PERCENT ... ORDER BY did in fact change with the release of SQL Server 2005, much to the chagrin of many people who were relying on this undocumented "trick". (In all fairness, I must admit that even some of Microsoft's tools rely on this behaviour. And that apparently a very influential customer cooerced Microsoft into bringing this behaviour back in SQL 2005 and SQL 2008, although it does now require a trace flag to be set). If this doesn't prove how dangerous it is to rely on these tricks, then what does?[quote][b]Jacob Luebbers (11/27/2008)[/b][quote][b]Hugo Kornelis (11/27/2008)[/b]...* The optimizer might choose to use an unordered scan of the clustered index instead of the current ordered scan...[/quote]The initial insert into the table is ordered explicitly. There was some lively discussion on whether or not the results of a subsequent unordered scan would reliably come back in that order without the guarantee of an ORDER BY... so far no-one has shown a case where they [i]don't[/i] (to my knowledge).[/quote]I'll show you two. The first relies on how SQL Server allocates pages to tables. The first 8 pages come from mixed extents; after that all allocation is in uniform extents (i.e. a whole block of 8 pages is allocated to the table at once). I first create a filler table that allocates a mixed extent and some uniform extents. After deleting the table, the extents are free again, but they apparently keep their "mixed/uniform" mark. So than I allocate 6 tables that only use one page (all from the mixed extent), and then the actual table I will test with. The first 2 pages come from the existing mixed extent, and than a new mixed extent is allocated, after the previously allocated (and now empty) uniform extents. The rest of the table uses uniform extents, first the empty ones allocated previously (between the first and second mixed extent), then new ones (after the second mixed extent).[code]use tempdb;gocreate database testit;gouse testit;gocreate table filler (a char(8000));goinsert into filler default values;go 100drop table filler;gocreate table fill1 (a int);create table fill2 (a int);create table fill3 (a int);create table fill4 (a int);create table fill5 (a int);create table fill6 (a int);insert into fill1 default values;insert into fill2 default values;insert into fill3 default values;insert into fill4 default values;insert into fill5 default values;insert into fill6 default values;gocreate table testtable (id int identity primary key, val int, filler char(4000));declare @i int;select @i = 1;while @i < 5000begin; insert into testtable (val, filler) select @i, str(@i); set @i = @i + 1;end;goselect * from testtable with (nolock) option (maxdop 1);gouse tempdb;godrop database testit;go[/code]The second example mimicks concurrent behaviour. While you are filling your table, someone else frees some space. Space that will now be used for your table.[code]use tempdb;gocreate database testit;gouse testit;gocreate table filler (a char(8000));goinsert into filler default values;go 100create table testtable (id int identity primary key, val int, filler char(4000));declare @i int;select @i = 1;while @i < 5000begin; insert into testtable (val, filler) select @i, str(@i); set @i = @i + 1; -- SIMULATE CONCURRENT ACTIVITY if @i = 2000 truncate table filler;end;goselect * from testtable with (nolock) option (maxdop 1);gouse tempdb;godrop database testit;go[/code](Sorry for the all-lowercase by the way, but I just lost my post because I took too long and I can't be bothered to do the nice formatting again)[quote][b]Jacob Luebbers (11/27/2008)[/b][quote][b]Hugo Kornelis (11/27/2008)[/b]...My point is that every undocumented behaviour that you observe, no matter how consistent it appears, should be considered to be a coincidal side effect of how the current version of the software interacts with your current hardware configuration and current data distribution. And that is NOT a safe foundation for building production software....[/quote]Agree with you here - however I'm willing to take that risk in certain cases. The massive performance gain in certain cases using this technique is compelling, and if the process using it can bear this risk (and the maintainers of that process are mindful of potential changes in this behaviour with updates to the engine) I say go for it.Regards,Jacob[/quote]This is where we will have to agree to disagree. I've witnessed this too often. When you warn about the danger, management is always "prepared to take the risk, and they will "of course monitor, and plan followup action". But when the shit hits the fan, it's suddenly your fault...Maybe you have been lucky not to have experienced this yet ;) But I won't take any chances :D[/quote]Yep, the code you wrote works about the way you say it will, but I figured out the problem in your code in the second block. First, I wouldn't write a query using the NOLOCK hint unless absolutely necessary. Take your code in the second code block, and change your last query to use the query hint (index = 1) IN PLACE OF the (nolock) you have. Better yet, copy the query and run both (one with nolock and one with index = 1). You will see that the one with (index = 1) returns an ordered set.Edit: And just for s and g's, I ran it also with no hint on the table with the clustered primary key, still returned an ordered result set.Another Edit: Added a query using both hints (nolock, index = 1), returned an ordered result set.Thu, 27 Nov 2008 18:33:47 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxHey Hugo,[quote][b]Hugo Kornelis (11/27/2008)[/b][hr]Point taken. Though the flipside is that TABLOCK increases the chance to get an unordered scan instead of an ordered one. More on that below.[/quote]Maybe I'm being thick, but I don't see from your comments how TABLOCKX will increase the chance of an unordered scan... am I missing something?[quote][b]Hugo Kornelis (11/27/2008)[/b][hr]I see that Jeff defends his use of ORDER BY in a subquery by refering to observed behaviour, not to any documentation. Exactly the danger I am warning about.[/quote]Jeff's final solution (bottom of the article) doesn't use an ordered subquery - just the clustered index hint to guarantee order.[quote][b]Hugo Kornelis (11/27/2008)[/b][hr]I'll show you two...[/quote]I'll take your word for it (I don't have the time right this sec to run through your demo code). Thanks for the examples though - I'll try to get some coherent thoughts on them over the weekend.Cheers!Regards,JacobThu, 27 Nov 2008 17:34:33 GMTJacob Luebbershttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Jacob Luebbers (11/27/2008)[/b][hr]Hey Hugo,[quote][b]Hugo Kornelis (11/27/2008)[/b]...* The optimizer might choose a non-clustered index to drive the query...[/quote]The technique also uses an index hint specifying the clustered index, with TABLOCKX to ensure that no other modifications could occur to the table during our UPDATE.[/quote]Hi Jacob,Point taken. Though the flipside is that TABLOCK increases the chance to get an unordered scan instead of an ordered one. More on that below.[quote][b]Jacob Luebbers (11/27/2008)[/b][quote][b]Hugo Kornelis (11/27/2008)[/b]...* The query engine might perform a merry-go-round scan...[/quote]Jeff's article covers this (following on from comments from Gail on the merry-go-round index behaviour): [url=http://www.sqlservercentral.com/articles/Advanced+Querying/61716/]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url].[/quote]I see that Jeff defends his use of ORDER BY in a subquery by refering to observed behaviour, not to any documentation. Exactly the danger I am warning about.In fact, this behaviour of TOP 100 PERCENT ... ORDER BY did in fact change with the release of SQL Server 2005, much to the chagrin of many people who were relying on this undocumented "trick". (In all fairness, I must admit that even some of Microsoft's tools rely on this behaviour. And that apparently a very influential customer cooerced Microsoft into bringing this behaviour back in SQL 2005 and SQL 2008, although it does now require a trace flag to be set). If this doesn't prove how dangerous it is to rely on these tricks, then what does?[quote][b]Jacob Luebbers (11/27/2008)[/b][quote][b]Hugo Kornelis (11/27/2008)[/b]...* The optimizer might choose to use an unordered scan of the clustered index instead of the current ordered scan...[/quote]The initial insert into the table is ordered explicitly. There was some lively discussion on whether or not the results of a subsequent unordered scan would reliably come back in that order without the guarantee of an ORDER BY... so far no-one has shown a case where they [i]don't[/i] (to my knowledge).[/quote]I'll show you two. The first relies on how SQL Server allocates pages to tables. The first 8 pages come from mixed extents; after that all allocation is in uniform extents (i.e. a whole block of 8 pages is allocated to the table at once). I first create a filler table that allocates a mixed extent and some uniform extents. After deleting the table, the extents are free again, but they apparently keep their "mixed/uniform" mark. So than I allocate 6 tables that only use one page (all from the mixed extent), and then the actual table I will test with. The first 2 pages come from the existing mixed extent, and than a new mixed extent is allocated, after the previously allocated (and now empty) uniform extents. The rest of the table uses uniform extents, first the empty ones allocated previously (between the first and second mixed extent), then new ones (after the second mixed extent).[code]use tempdb;gocreate database testit;gouse testit;gocreate table filler (a char(8000));goinsert into filler default values;go 100drop table filler;gocreate table fill1 (a int);create table fill2 (a int);create table fill3 (a int);create table fill4 (a int);create table fill5 (a int);create table fill6 (a int);insert into fill1 default values;insert into fill2 default values;insert into fill3 default values;insert into fill4 default values;insert into fill5 default values;insert into fill6 default values;gocreate table testtable (id int identity primary key, val int, filler char(4000));declare @i int;select @i = 1;while @i < 5000begin; insert into testtable (val, filler) select @i, str(@i); set @i = @i + 1;end;goselect * from testtable with (nolock) option (maxdop 1);gouse tempdb;godrop database testit;go[/code]The second example mimicks concurrent behaviour. While you are filling your table, someone else frees some space. Space that will now be used for your table.[code]use tempdb;gocreate database testit;gouse testit;gocreate table filler (a char(8000));goinsert into filler default values;go 100create table testtable (id int identity primary key, val int, filler char(4000));declare @i int;select @i = 1;while @i < 5000begin; insert into testtable (val, filler) select @i, str(@i); set @i = @i + 1; -- SIMULATE CONCURRENT ACTIVITY if @i = 2000 truncate table filler;end;goselect * from testtable with (nolock) option (maxdop 1);gouse tempdb;godrop database testit;go[/code](Sorry for the all-lowercase by the way, but I just lost my post because I took too long and I can't be bothered to do the nice formatting again)[quote][b]Jacob Luebbers (11/27/2008)[/b][quote][b]Hugo Kornelis (11/27/2008)[/b]...My point is that every undocumented behaviour that you observe, no matter how consistent it appears, should be considered to be a coincidal side effect of how the current version of the software interacts with your current hardware configuration and current data distribution. And that is NOT a safe foundation for building production software....[/quote]Agree with you here - however I'm willing to take that risk in certain cases. The massive performance gain in certain cases using this technique is compelling, and if the process using it can bear this risk (and the maintainers of that process are mindful of potential changes in this behaviour with updates to the engine) I say go for it.Regards,Jacob[/quote]This is where we will have to agree to disagree. I've witnessed this too often. When you warn about the danger, management is always "prepared to take the risk, and they will "of course monitor, and plan followup action". But when the shit hits the fan, it's suddenly your fault...Maybe you have been lucky not to have experienced this yet ;) But I won't take any chances :DThu, 27 Nov 2008 17:12:07 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxHey Hugo,[quote][b]Hugo Kornelis (11/27/2008)[/b]...* The optimizer might choose a non-clustered index to drive the query...[/quote]The technique also uses an index hint specifying the clustered index, with TABLOCKX to ensure that no other modifications could occur to the table during our UPDATE.[quote][b]Hugo Kornelis (11/27/2008)[/b]...* The query engine might perform a merry-go-round scan...[/quote]Jeff's article covers this (following on from comments from Gail on the merry-go-round index behaviour): [url=http://www.sqlservercentral.com/articles/Advanced+Querying/61716/]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url].[quote][b]Hugo Kornelis (11/27/2008)[/b]...* The optimizer might choose to use an unordered scan of the clustered index instead of the current ordered scan...[/quote]The initial insert into the table is ordered explicitly. There was some lively discussion on whether or not the results of a subsequent unordered scan would reliably come back in that order without the guarantee of an ORDER BY... so far no-one has shown a case where they [i]don't[/i] (to my knowledge).[quote][b]Hugo Kornelis (11/27/2008)[/b]...My point is that every undocumented behaviour that you observe, no matter how consistent it appears, should be considered to be a coincidal side effect of how the current version of the software interacts with your current hardware configuration and current data distribution. And that is NOT a safe foundation for building production software....[/quote]Agree with you here - however I'm willing to take that risk in certain cases. The massive performance gain in certain cases using this technique is compelling, and if the process using it can bear this risk (and the maintainers of that process are mindful of potential changes in this behaviour with updates to the engine) I say go for it.Regards,JacobThu, 27 Nov 2008 16:20:22 GMTJacob Luebbershttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxHave you read Jeff's article, Hugo?It actually answers some of the points you brought up.[url=http://www.sqlservercentral.com/articles/Advanced+Querying/61716/] Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/url]Thu, 27 Nov 2008 16:13:08 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Jacob Luebbers (11/27/2008)[/b][hr]Hey Hugo, I think the consensus for those that have taken up the "Jeff-style" quirky update method is to use the MAXDOP=1 option on the UPDATE query to explicitly disable parallelism. At least I do :)[/quote]Hi Jacob,That mitigates one of the potential problems, but not all. There are more risks. For instance:* The optimizer might choose a non-clustered index to drive the query* The optimizer might choose to use an unordered scan of the clustered index instead of the current ordered scan* The query engine might perform a merry-go-round scanThese are just three examples. There might be more risks that I don't think of at the moment.My point is that every undocumented behaviour that you observe, no matter how consistent it appears, should be considered to be a coincidal side effect of how the current version of the software interacts with your current hardware configuration and current data distribution. And that is NOT a safe foundation for building production software.Thu, 27 Nov 2008 16:02:19 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxAlso, if I remember the discussions around Jeff's article on running totals, the only place he has run into difficulties with the technique is with partitioned tables. That is one I'd still like to tackle, but just haven't had the time to work with it.Thu, 27 Nov 2008 15:42:53 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Hugo Kornelis (11/27/2008)[/b][hr...2) The assumption that such an UPDATE will always be processed in order of the clustered index. And I am fairly confident that [i]this[/i] is not documented at all. Maybe the current versions of SQL Server will work like that (I admit not being able to break it in my first three tries, but with only three tries you can also see that I didn't try very hard), but who says they'll continue to do so on the enxt version? or maybe even the next service pack? or maybe even on different hardware?For instance, as far as I know data modifications are [b]currently[/b] never paralellized. But what if the next service pack changes that, to make better use of the increasing numbers of cores per socket and sockets per server. I can assure you that if this update runs in parallel, results will be completely different from what you want.......[/quote]Hey Hugo, I think the consensus for those that have taken up the "Jeff-style" quirky update method is to use the MAXDOP=1 option on the UPDATE query to explicitly disable parallelism. At least I do :)Regards,JacobThu, 27 Nov 2008 15:21:59 GMTJacob Luebbershttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxOkay, can't wait to see Jeff's response. I was using a technique I picked up from him for completing running totals.Regarding storing running totals, maybe it isn't stored in the database but the calculation is completed in a #temp table that is loaded in a stored procedure with a proper clustered index to ensure the order of the data, updated (including using an index hint on the clustered index (per Jeff's article on running totals), then the results returned by a select query on the #tem table.Not stored, and the calculations done much faster than it would be done with the cross-join query.As another aside, I ran both against a table with 32,800 entries and here are the stats for that run:[qoute]-- Cross Join Query --SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.Table 'Accounts'. Scan count 32802, logical reads 104229, physical reads 0, read-ahead reads 46, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 1250 ms, elapsed time = 2138 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.-- Cross Join Query ---- Update Query --SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.Table 'Accounts'. Scan count 1, logical reads 153, physical reads 0, read-ahead reads 39, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 219 ms, elapsed time = 283 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.-- Update Join Query ---- Select After Update Query --SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.Table 'Accounts'. Scan count 1, logical reads 153, physical reads 1, read-ahead reads 177, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 47 ms, elapsed time = 1459 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.-- Select After Update Query --[/quote]Thu, 27 Nov 2008 15:01:24 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Lynn Pettis (11/27/2008)[/b][hr]Actually, the author was using it in his code (which I included in my post for comparision purposes), I was using it in my code exclusively to provide order to the data.[/quote]Hi Lynn,Oops, my bad. Apologies for confusing you with the author. And also for not seeing the scrollbar and the extra code you added.However, now that I did see your code, I must say that I like it even less than the code in the article. Your update relies on at least three assumptions:1) The assumption that this kind of UPDATE statement will always work row by row, using the variables after assignment from row #[i]n[/i] to update row #[i]n+1[/i]. This may or may not be documented (I don't feel like digging through BOL at the moment), but it's definitely against the original idea of set-based updates, where all modifications are done "at once".2) The assumption that such an UPDATE will always be processed in order of the clustered index. And I am fairly confident that [i]this[/i] is not documented at all. Maybe the current versions of SQL Server will work like that (I admit not being able to break it in my first three tries, but with only three tries you can also see that I didn't try very hard), but who says they'll continue to do so on the enxt version? or maybe even the next service pack? or maybe even on different hardware?For instance, as far as I know data modifications are [b]currently[/b] never paralellized. But what if the next service pack changes that, to make better use of the increasing numbers of cores per socket and sockets per server. I can assure you that if this update runs in parallel, results will be completely different from what you want....3) The assumption that people want to store the running totals. In most cases, you DON'T want to store them, since this would necessify recalculation everytime some data changes. Most people will want to calculate running totals on the client. And if it's really necessary to calculate them on the server, you'll want to do it in a view or stored procedure, not persist it. Unless you are in a reporting database that refreshes once or twice a day and is further used to query somewhat stale data for reporting or analysis.Unless there is some source that I don't know of where Microsoft documents the order of processing in an UPDATE statement and commits itself to maintaining that behaviour, I would never allow this code to run in any of my production database - and I'd urge anyone to do the same.Thu, 27 Nov 2008 14:40:06 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Hugo Kornelis (11/27/2008)[/b][hr][quote][b]Lynn Pettis (11/27/2008)[/b][hr]Since the purpose of the identity field was order, if there was a gap it wouldn't have caused an issue.[/quote]Hi Lynn,But you are using the IDENTITY values for more than just imposing an order. You use "WHERE B.ID BETWEEN A.ID - 4 AND A.ID" to find the current and four preceeding rows - but if there are gaps, then less than four preceeding rows will match this clause.[/quote]Actually, the author was using it in his code (which I included in my post for comparision purposes), I was using it in my code exclusively to provide order to the data.Thu, 27 Nov 2008 14:03:44 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Jeff Moden (11/27/2008)[/b][hr][quote][b]Hugo Kornelis (11/27/2008)[/b][hr]What I was refering too is the lack of ROW_NUMBER() in SQL Server 2000. This means you'll either have to take your chance with IDENTITY, at the risk of gaps, as the author of this article did; or you have to use a correlated subquery to calculate the row number on the fly, which can result in dramatic performance as the amount of rows grows. Plus, the queries tend to get long and hard to understand.[/quote]Nope... in SQL Server 2000, just do a SELECT INTO a temp table with the IDENTITY function and the author's code works just fine without any difficulty for length or understanding.[/quote]Hi Jeff,You're right. When using SELECT INTO a temp table with the IDENTITY function (*), then there will not be any gaps and the range of A.ID-4 up to A.ID will always have 5 rows. But unless I overlooked something, this was not in the article. To me, the article appears to imply that any IDENTITY row can be used for this. And since many tables already have an IDENTITY column, often with gaps in the sequence due to deleted data or rolled back inserts, I thought it'd be better to point out this danger.(*) There is another potential problem here. I know that there is only one situation where Microsoft [i]guarantees[/i] that identity values are given out in the expected order when using ORDER BY in an INSERT statement, but I can never recall if this guarantee is for SELECT INTO with the IDENTITY() function, of for INSERT ... SELECT on a table with a predefined IDENTITY column. And I can never find this particular bit of documentation when I need it. I [b]think[/b] that SELECT INTO with the IDENTITY() function is the supported scenario, but if you are going to use this in production you'd probably better doublecheck first, for my memory is known to .... aaahh, what was I going to say again? :)Thu, 27 Nov 2008 13:58:20 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Lynn Pettis (11/27/2008)[/b][hr]Since the purpose of the identity field was order, if there was a gap it wouldn't have caused an issue.[/quote]Hi Lynn,But you are using the IDENTITY values for more than just imposing an order. You use "WHERE B.ID BETWEEN A.ID - 4 AND A.ID" to find the current and four preceeding rows - but if there are gaps, then less than four preceeding rows will match this clause.Thu, 27 Nov 2008 13:51:05 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspxI like both approaches to the problem. They both have a refreshing spark of originality.I'm not entirely sure if I completely agree with all Hugo's objections, though I share his caution. 'Quirky Update' techniques can go wrong if you are not entirely aware of certain 'gotchas', but they were documented, used, and supported even before Microsoft bought the product. For the 'quirky update' approach that Lynne uses to work safely, you have to remember certain rules (e.g. updates are done in the order of the clustered index, all '@var=col=expression' variable assignments are done before any simple column updates-[i]I hope I've remembered that the right way around[/i]!)Thu, 27 Nov 2008 13:06:46 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Jeff Moden (11/27/2008)[/b][hr][quote][b]Lynn Pettis (11/26/2008)[/b][hr]Good article, but let's take another approach and see what we see.[/quote]Heh... you beat me to it, again. ;)[/quote]Heh... I may be old, but I'm also trainable.Thu, 27 Nov 2008 11:11:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Lynn Pettis (11/26/2008)[/b][hr]Good article, but let's take another approach and see what we see.[/quote]Heh... you beat me to it, again. ;)Thu, 27 Nov 2008 11:02:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic609561-1418-1.aspx[quote][b]Hugo Kornelis (11/27/2008)[/b][hr]What I was refering too is the lack of ROW_NUMBER() in SQL Server 2000. This means you'll either have to take your chance with IDENTITY, at the risk of gaps, as the author of this article did; or you have to use a correlated subquery to calculate the row number on the fly, which can result in dramatic performance as the amount of rows grows. Plus, the queries tend to get long and hard to understand.[/quote]Nope... in SQL Server 2000, just do a SELECT INTO a temp table with the IDENTITY function and the author's code works just fine without any difficulty for length or understanding.Thu, 27 Nov 2008 11:00:14 GMTJeff Moden