December 29, 2010 at 6:56 pm
You've outdone yourself with this one Jeff. This article is fantastic. I guess the thing for me to remember, and the reason why some call it an unreliable hack, is because it isn't "sanctioned" by any authoritative body (such as Microsoft, ANSI, etc). Therefore, as changes are made to future versions of SQL (such as partitioning tables) the "Quirky Update" and Pseudo-Cursor methods can be violated. However, the way to address that is to keep up with those new changes and adjust the methods as appropriate (such as copying the data to a temp table from a partitioned table).
Again, thanks for the great article. Your dedication to this craft is greatly appreciated beyond words.
December 31, 2010 at 7:10 am
Langston Montgomery (12/29/2010)
You've outdone yourself with this one Jeff. This article is fantastic. I guess the thing for me to remember, and the reason why some call it an unreliable hack, is because it isn't "sanctioned" by any authoritative body (such as Microsoft, ANSI, etc). Therefore, as changes are made to future versions of SQL (such as partitioning tables) the "Quirky Update" and Pseudo-Cursor methods can be violated. However, the way to address that is to keep up with those new changes and adjust the methods as appropriate (such as copying the data to a temp table from a partitioned table).Again, thanks for the great article. Your dedication to this craft is greatly appreciated beyond words.
Thanks for the awesome feedback, Langston. The article is scheduled to be republished on the 4th of March in 2011. I'm not going to rewrite the whole thing this time as I did last time but there will be some additions that came up during the discussions on this one including the "safeties" that Paul White came up with and the enhancement to those safeties that Tom Thompson came up with.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2010 at 8:59 am
Jeff Moden (12/31/2010)
Langston Montgomery (12/29/2010)
You've outdone yourself with this one Jeff. This article is fantastic. I guess the thing for me to remember, and the reason why some call it an unreliable hack, is because it isn't "sanctioned" by any authoritative body (such as Microsoft, ANSI, etc). Therefore, as changes are made to future versions of SQL (such as partitioning tables) the "Quirky Update" and Pseudo-Cursor methods can be violated. However, the way to address that is to keep up with those new changes and adjust the methods as appropriate (such as copying the data to a temp table from a partitioned table).Again, thanks for the great article. Your dedication to this craft is greatly appreciated beyond words.
Thanks for the awesome feedback, Langston. The article is scheduled to be republished on the 4th of March in 2011. I'm not going to rewrite the whole thing this time as I did last time but there will be some additions that came up during the discussions on this one including the "safeties" that Paul White came up with and the enhancement to those safeties that Tom Thompson came up with.
Definately looking forward to the updated version of your article. I should look at a full rewrite of mine, once I find some time with everything else going on around me. Be interesting to see what changes need to be made.
January 21, 2011 at 9:53 am
Wow, this discussion is almost as long as the article :hehe:.
Great job Jeff :w00t:.
February 2, 2011 at 1:15 pm
Hello Jeff & Co. Really great article and awesome discussion. I wrote an article about the topic five years ago, but my investigation lacked depth of the Jeff's article and arguments and examples from the discussion.
However, I just want to rise a point that was my conclusion five years ago and IMO this discussion just confirmed it.
The point is that displaying running totals should be implemented on the client side, not in SQL.
February 3, 2011 at 2:46 am
mmarovic (2/2/2011)
Hello Jeff & Co. Really great article and awesome discussion. I wrote an article about the topic five years ago, but my investigation lacked depth of the Jeff's article and arguments and examples from the discussion.However, I just want to rise a point that was my conclusion five years ago and IMO this discussion just confirmed it.
The point is that displaying running totals should be implemented on the client side, not in SQL.
I would agree with that in many situations, like for reporting, but not always. As was mentioned before if running totals are part of enforced buisiness logic then you need to have them on the database side too. Other times, small non-reporting quieries might depend on intermediate totals at given date points, say last day of month for example.
So as usual, it depends 😉
February 3, 2011 at 3:19 am
I agree that it depends and there are cases for doing implementation in SQL.
However, to clarify my position: One should consider sql implementation of running totals only if he can't find a way to do it on the client/middle tier. In most cases sql implementation is not not necessary with proper database and application design.
February 3, 2011 at 6:32 am
mmarovic (2/2/2011)
Hello Jeff & Co. Really great article and awesome discussion. I wrote an article about the topic five years ago, but my investigation lacked depth of the Jeff's article and arguments and examples from the discussion.However, I just want to rise a point that was my conclusion five years ago and IMO this discussion just confirmed it.
The point is that displaying running totals should be implemented on the client side, not in SQL.
Thanks for the great feedback, mmarovic. Depending on what is being done and whether there's even a client side to run it on, I agree... the client side is the likely place to do this especially since many systems have the running total functionality built in. There are also other things that can be done with the method and those things also fit into the "It Depends" category. With Paul and Tom's method (appears in the discussion for this article), which provides not only a safety check but apparently drives the correct answer into the code, where it's done has become less of a concern computationally speaking and it all becomes similiar to the age old problem of where to format data. The correct answer is usually "at the client". Of course, a nightly run of several million rows might be a good reason to do it at the data layer. In those cases, a properly written CLR would probably be the way to go unless there's someone who forbids that bit of useful technology.
Thanks again for stopping by and for the constructive comments. I'd also love to see your article (as I'm sure the others on this discussion would) on the subject. There aren't too many people that have written about the subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2011 at 11:30 am
Thanks again for stopping by and for the constructive comments. I'd also love to see your article (as I'm sure the others on this discussion would) on the subject. There aren't too many people that have written about the subject.
Well, after reading this one I consider mine obsolete and a bit naive, except for recommendation about calculating running totals on the client.
April 7, 2011 at 9:22 am
It may be my own mistakes (and I don't have time to delve into it now), but may I suggest further testing on SQL 2008 R2 RTM? While the 3 part set statements appears to function properly on assignment to the table columns, 2 part set statements do not (though they do properly assign to variables).
April 7, 2011 at 2:46 pm
Nadrek (4/7/2011)
It may be my own mistakes (and I don't have time to delve into it now), but may I suggest further testing on SQL 2008 R2 RTM? While the 3 part set statements appears to function properly on assignment to the table columns, 2 part set statements do not (though they do properly assign to variables).
Care to post the code you used?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2011 at 8:18 am
Thank you for your response; I'll see if I get the chance to generate sample data and schemas, as well as the two-part SET statement variant.
Setup:
USE tempdb
CREATE TABLE [dbo].[tmp_conf_server](
[RecordedDateTime] [datetime2](3) NOT NULL,
[ServerName] [varchar](260) NOT NULL,
[LastRestarted] [datetime2](3) NOT NULL,
[TotalBatchRequests] [bigint] NOT NULL,
[TotalDeadlocks] [bigint] NOT NULL,
[TotalCompilations] [bigint] NOT NULL,
[TotalReCompilations] [bigint] NOT NULL,
CONSTRAINT [PK_cls_RecordedDateTime_Servername__tmp_conf_server] PRIMARY KEY CLUSTERED
(
[RecordedDateTime] ASC,
[ServerName] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY]
truncate table tempdb.dbo.[tmp_conf_server]
INSERT INTO tempdb.dbo.[tmp_conf_server] ([RecordedDateTime],[ServerName],[LastRestarted],[TotalBatchRequests],[TotalDeadlocks],[TotalCompilations],[TotalReCompilations])VALUES('2011-04-05 16:46:46.630','server1','2011-02-25 13:29:00.000',500,12,3050,5555)
INSERT INTO tempdb.dbo.[tmp_conf_server] ([RecordedDateTime],[ServerName],[LastRestarted],[TotalBatchRequests],[TotalDeadlocks],[TotalCompilations],[TotalReCompilations])VALUES('2011-04-05 17:49:22.350','server1','2011-02-25 13:29:00.000',750,12,4000,6666)
INSERT INTO tempdb.dbo.[tmp_conf_server] ([RecordedDateTime],[ServerName],[LastRestarted],[TotalBatchRequests],[TotalDeadlocks],[TotalCompilations],[TotalReCompilations])VALUES('2011-04-06 17:34:22.473','server1','2011-02-25 13:29:00.000',810,12,4075,7777)
INSERT INTO tempdb.dbo.[tmp_conf_server] ([RecordedDateTime],[ServerName],[LastRestarted],[TotalBatchRequests],[TotalDeadlocks],[TotalCompilations],[TotalReCompilations])VALUES('2011-04-05 16:46:47.630','server2','2011-02-27 11:29:00.000',888,5,30,3333)
INSERT INTO tempdb.dbo.[tmp_conf_server] ([RecordedDateTime],[ServerName],[LastRestarted],[TotalBatchRequests],[TotalDeadlocks],[TotalCompilations],[TotalReCompilations])VALUES('2011-04-05 17:49:23.350','server2','2011-02-27 11:29:00.000',891,6,40,5900)
INSERT INTO tempdb.dbo.[tmp_conf_server] ([RecordedDateTime],[ServerName],[LastRestarted],[TotalBatchRequests],[TotalDeadlocks],[TotalCompilations],[TotalReCompilations])VALUES('2011-04-06 17:34:23.473','server2','2011-02-27 11:29:00.000',950,8,7000,5950)
The core, apparently working 3 part SET, code is:
-- Quirky update: you have to follow all the rules; if you fail even one rule, you may, or may not, receive invalid results
IF EXISTS (SELECT * FROM tempdb.sys.objects so WHERE so.object_id = object_id(N'[tempdb].[dbo].[#tmp_conf_server_Inc]') AND so.type = 'U' AND so.is_ms_shipped = 0)
DROP TABLE [dbo].[#tmp_conf_server_Inc]
CREATE TABLE #tmp_conf_server_Inc(
[Anchor] BIGINT IDENTITY(1,1) NOT NULL, -- quirky update anchor column
[ServerName] [varchar](260) NOT NULL,
[RecordedDateTime] [datetime2](3) NOT NULL,
[LastRestarted] [datetime2](3) NOT NULL,
[TotBatchRequests] [bigint] NOT NULL,
[TotDeadlocks] [bigint] NOT NULL,
[TotCompilations] [bigint] NOT NULL,
[TotReCompilations] [bigint] NOT NULL,
[IncSeconds] BIGINT NULL,
[IncBatchRequests] [bigint] NULL,
[IncDeadlocks] [bigint] NULL,
[IncCompilations] [bigint] NULL,
[IncReCompilations] [bigint] NULL,
CONSTRAINT [PK_cls_RecordedDateTime_Servername__#tmp_conf_server_Inc] PRIMARY KEY CLUSTERED
(
[Anchor],
[ServerName] ASC,
[RecordedDateTime] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO #tmp_conf_server_Inc
(
[ServerName],
[RecordedDateTime],
[LastRestarted],
[TotBatchRequests],
[TotDeadlocks],
[TotCompilations],
[TotReCompilations]
)
SELECT
[ServerName],
[RecordedDateTime],
[LastRestarted],
[TotalBatchRequests],
[TotalDeadlocks],
[TotalCompilations],
[TotalReCompilations]
FROM tempdb.dbo.tmp_conf_server
ORDER BY ServerName, RecordedDateTime
-- Quirky update: you have to follow all the rules; if you fail even one rule, you may, or may not, receive invalid results
-- BEFORE USING, read http://www.sqlservercentral.com/articles/T-SQL/68467/
DECLARE @PrevTotBatchRequests BIGINT
DECLARE @PrevTotDeadlocks BIGINT
DECLARE @PrevTotCompilations BIGINT
DECLARE @PrevTotReCompilations BIGINT
DECLARE @PrevRecordedDateTime DATETIME2(3)
DECLARE @PrevServername VARCHAR(260)
DECLARE @IncSeconds BIGINT
DECLARE @Anchor BIGINT
SET @PrevTotBatchRequests = -999999999999999
SET @IncSeconds = -999999999999999
SET @PrevServername = ''
SET @PrevRecordedDateTime = '20100101'
UPDATE tmpGCSI
SET
/* */
-- I don't know why I need the three part technique, but the two part fails to operate
@IncSeconds = tmpGCSI.IncSeconds = CASE
WHEN @PrevRecordedDateTime <> '20100101' AND @PrevServername = tmpGCSI.ServerName AND (tmpGCSI.RecordedDateTime >= tmpGCSI.LastRestarted) THEN (DATEDIFF(ss,@PrevRecordedDateTime,tmpGCSI.RecordedDateTime))
WHEN @PrevRecordedDateTime <> '20100101' AND @PrevServername = tmpGCSI.ServerName AND (tmpGCSI.RecordedDateTime < tmpGCSI.LastRestarted) THEN (DATEDIFF(ss,tmpGCSI.LastRestarted,tmpGCSI.RecordedDateTime))
ELSE NULL
END
,@PrevRecordedDateTime = tmpGCSI.RecordedDateTime
,@PrevTotBatchRequests = tmpGCSI.IncBatchRequests = CASE
WHEN @PrevTotBatchRequests <> -999999999999999 AND @PrevServername = tmpGCSI.ServerName THEN (tmpGCSI.TotBatchRequests - @PrevTotBatchRequests)
ELSE NULL
END
,@PrevTotBatchRequests = tmpGCSI.TotBatchRequests
,@PrevTotDeadlocks = tmpGCSI.IncDeadlocks = CASE
WHEN @PrevTotBatchRequests <> -999999999999999 AND @PrevServername = tmpGCSI.ServerName THEN (tmpGCSI.TotDeadlocks - @PrevTotDeadlocks)
ELSE NULL
END
,@PrevTotDeadlocks = tmpGCSI.TotDeadlocks
,@PrevTotCompilations = tmpGCSI.IncCompilations = CASE
WHEN @PrevTotBatchRequests <> -999999999999999 AND @PrevServername = tmpGCSI.ServerName THEN (tmpGCSI.TotCompilations - @PrevTotCompilations)
ELSE NULL
END
,@PrevTotCompilations = tmpGCSI.TotCompilations
,@PrevTotReCompilations = tmpGCSI.IncReCompilations = CASE
WHEN @PrevTotBatchRequests <> -999999999999999 AND @PrevServername = tmpGCSI.ServerName THEN (tmpGCSI.TotReCompilations - @PrevTotReCompilations)
ELSE NULL
END
,@PrevTotReCompilations = tmpGCSI.TotReCompilations
,@PrevServername = tmpGCSI.ServerName
,@Anchor = tmpGCSI.Anchor
FROM #tmp_conf_server_Inc tmpGCSI WITH (TABLOCKX)
OPTION (MAXDOP 1)
SELECT * FROM #tmp_conf_server_Inc
ORDER BY ServerName, RecordedDateTime
And (in its entirety, though the _only_ change is to remove the first section of each three part SET) the non-working two part SET version, which hopefully I'm simply missing something trivial with:
-- Quirky update: you have to follow all the rules; if you fail even one rule, you may, or may not, receive invalid results
IF EXISTS (SELECT * FROM tempdb.sys.objects so WHERE so.object_id = object_id(N'[tempdb].[dbo].[#tmp_conf_server_Inc]') AND so.type = 'U' AND so.is_ms_shipped = 0)
DROP TABLE [dbo].[#tmp_conf_server_Inc]
CREATE TABLE #tmp_conf_server_Inc(
[Anchor] BIGINT IDENTITY(1,1) NOT NULL, -- quirky update anchor column
[ServerName] [varchar](260) NOT NULL,
[RecordedDateTime] [datetime2](3) NOT NULL,
[LastRestarted] [datetime2](3) NOT NULL,
[TotBatchRequests] [bigint] NOT NULL,
[TotDeadlocks] [bigint] NOT NULL,
[TotCompilations] [bigint] NOT NULL,
[TotReCompilations] [bigint] NOT NULL,
[IncSeconds] BIGINT NULL,
[IncBatchRequests] [bigint] NULL,
[IncDeadlocks] [bigint] NULL,
[IncCompilations] [bigint] NULL,
[IncReCompilations] [bigint] NULL,
CONSTRAINT [PK_cls_RecordedDateTime_Servername__#tmp_conf_server_Inc] PRIMARY KEY CLUSTERED
(
[Anchor],
[ServerName] ASC,
[RecordedDateTime] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO #tmp_conf_server_Inc
(
[ServerName],
[RecordedDateTime],
[LastRestarted],
[TotBatchRequests],
[TotDeadlocks],
[TotCompilations],
[TotReCompilations]
)
SELECT
[ServerName],
[RecordedDateTime],
[LastRestarted],
[TotalBatchRequests],
[TotalDeadlocks],
[TotalCompilations],
[TotalReCompilations]
FROM tempdb.dbo.tmp_conf_server
ORDER BY ServerName, RecordedDateTime
-- Quirky update: you have to follow all the rules; if you fail even one rule, you may, or may not, receive invalid results
-- BEFORE USING, read http://www.sqlservercentral.com/articles/T-SQL/68467/
DECLARE @PrevTotBatchRequests BIGINT
DECLARE @PrevTotDeadlocks BIGINT
DECLARE @PrevTotCompilations BIGINT
DECLARE @PrevTotReCompilations BIGINT
DECLARE @PrevRecordedDateTime DATETIME2(3)
DECLARE @PrevServername VARCHAR(260)
DECLARE @IncSeconds BIGINT
DECLARE @Anchor BIGINT
SET @PrevTotBatchRequests = -999999999999999
SET @IncSeconds = -999999999999999
SET @PrevServername = ''
SET @PrevRecordedDateTime = '20100101'
UPDATE tmpGCSI
SET
/* */
-- I don't know why I need the three part technique, but the two part fails to operate
@IncSeconds = tmpGCSI.IncSeconds = CASE
WHEN @PrevRecordedDateTime <> '20100101' AND @PrevServername = tmpGCSI.ServerName AND (tmpGCSI.RecordedDateTime >= tmpGCSI.LastRestarted) THEN (DATEDIFF(ss,@PrevRecordedDateTime,tmpGCSI.RecordedDateTime))
WHEN @PrevRecordedDateTime <> '20100101' AND @PrevServername = tmpGCSI.ServerName AND (tmpGCSI.RecordedDateTime < tmpGCSI.LastRestarted) THEN (DATEDIFF(ss,tmpGCSI.LastRestarted,tmpGCSI.RecordedDateTime))
ELSE NULL
END
,@PrevRecordedDateTime = tmpGCSI.RecordedDateTime
,tmpGCSI.IncBatchRequests = CASE
WHEN @PrevTotBatchRequests <> -999999999999999 AND @PrevServername = tmpGCSI.ServerName THEN (tmpGCSI.TotBatchRequests - @PrevTotBatchRequests)
ELSE NULL
END
,@PrevTotBatchRequests = tmpGCSI.TotBatchRequests
,tmpGCSI.IncDeadlocks = CASE
WHEN @PrevTotBatchRequests <> -999999999999999 AND @PrevServername = tmpGCSI.ServerName THEN (tmpGCSI.TotDeadlocks - @PrevTotDeadlocks)
ELSE NULL
END
,@PrevTotDeadlocks = tmpGCSI.TotDeadlocks
,tmpGCSI.IncCompilations = CASE
WHEN @PrevTotBatchRequests <> -999999999999999 AND @PrevServername = tmpGCSI.ServerName THEN (tmpGCSI.TotCompilations - @PrevTotCompilations)
ELSE NULL
END
,@PrevTotCompilations = tmpGCSI.TotCompilations
,tmpGCSI.IncReCompilations = CASE
WHEN @PrevTotBatchRequests <> -999999999999999 AND @PrevServername = tmpGCSI.ServerName THEN (tmpGCSI.TotReCompilations - @PrevTotReCompilations)
ELSE NULL
END
,@PrevTotReCompilations = tmpGCSI.TotReCompilations
,@PrevServername = tmpGCSI.ServerName
,@Anchor = tmpGCSI.Anchor
FROM #tmp_conf_server_Inc tmpGCSI WITH (TABLOCKX)
OPTION (MAXDOP 1)
SELECT * FROM #tmp_conf_server_Inc
ORDER BY ServerName, RecordedDateTime
Table cleanup code:
DROP TABLE #tmp_conf_server_Inc
DROP TABLE tempdb.[dbo].[tmp_conf_server]
April 8, 2011 at 6:54 pm
BWAA-HAA!!! I guess I should be really insulted by some of the comments you have in the code.
-- This is an UNRELIABLE METHOD of doing business (Quirky Update)
-- Quirky update!!! BEWARE!!! Do not use this for critical things!
Do you really want help with the method or are you just trolling? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2011 at 8:20 pm
Nadrek (4/8/2011)
Thank you for your response; I'll see if I get the chance to generate sample data and schemas, as well as the two-part SET statement variant.
Nadrek,
Considering that you're only calculating the differences between two rows and not actually doing a running calculation, you don't need to use the "Quirky Update".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2011 at 8:27 pm
Also, be advised. You didn't add the required clustered index to the #Temp table and that may be why your 2 part code didn't work the way you expected. Still and like I said before, a "Quirky Update" isn't need for the simple chore of diffing two adjacent rows. I'll have some code for you for that soon.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 226 through 240 (of 307 total)
You must be logged in to reply to this topic. Login to reply