Posts tagged: CTE

Recursive CTEs

By , April 18, 2015 09:39

For my own reference, here are two CTEs which use recursion and that is their only value. These examples would certainly not find usage in a “normal” environment.

The first is a result set where the returned value is double the previous value:

use Work;
with doubles_CTE (rowNumber, dVal) as (
	select
		1
		, cast(1 as bigint)
	union all
	select
		rowNumber + 1
		,cast(dVal as bigint) * 2
	from doubles_CTE
	where rowNumber < 62 --to aviod recursion issues and prevent arithmetic overflow BIGINT has an upper range of 2^63-1, numeric 10^38-1
	)
select dVal from doubles_CTE
option (maxrecursion 62)
go

The second is the Fibonacci sequence:

use Work;
with Fibonacci_CTE (RowNum, Increment, Fibonacci) as (
	select
		1
		, cast(0 as  numeric(38,0))
		, cast(1 as  numeric(38,0))
	union all
	select
		RowNum + 1
		,Fibonacci
		, Increment + Fibonacci
	from Fibonacci_CTE
	where RowNum < 183 --prevent aritmetic overflow numeric has an upper range of 10^38-1
	)
select Fibonacci from Fibonacci_CTE
option (maxrecursion 183)--to aviod recursion issues

If the sequence has to begin with 0 then use the following select:

select 0
union all
select Fibonacci from Fibonacci_CTE

Conclusion

It is important to note, that recursion can lead to performance issues an in these cases arithmetic overflow. CTEs have a built-in safety net and will only perform the recursion up to 100 times, which is the server default. If you want to override this, do so with caution.

Like I said, just for reference. I would like to revisit these to see if they can be simplified. Next time perhaps.

Checking when Mirroring State Changed

By , May 8, 2012 19:45

This is a script I wrote today to quickly check when a mirrored database changed its status within a particular time frame.

Basically it calls the system stored procedure sp_dbmmonitorresults passing in the variable @dbname. It passes in the paramter @rows_to_return = 9 (basically include everything you’ve got SQL Server) and @update_status = 0 (you don’t need to update your history records SQL Server).
See BOL: sp_dbmmonitorresults

The parameters @start and @end define the timeframe I want to see and the parameter @rowOffset tells the script how many rows I want to see before the status changed.

Now there may be better ways of doing this, but it is a solution that was quickly put togeter and served the purpose adequately.

SET LANGUAGE us_english
GO

IF( OBJECT_ID('tempdb..#MirroringMonitorResults') IS NOT NULL)
	DROP TABLE #MirroringMonitorResults;
GO

DECLARE @dbname sysname = 'DBName',
		@start DATETIME = '2012-05-03 10:00',
		@end DATETIME = '2012-05-03 19:00',
		@rowOffset INT = 10;

CREATE TABLE  #MirroringMonitorResults (
	database_name sysname,
	[ROLE] INT,
	mirroring_state INT,
	witness_status INT,
	log_generation_rate INT,
	unsent_log  INT,
	send_rate  INT,
	unrestored_log  INT,
	recovery_rate  INT,
	transaction_delay  INT,
	transactions_per_sec INT,
	average_delay  INT,
	time_recorded DATETIME,
	time_behind  DATETIME,
	local_time  DATETIME
	);

INSERT INTO #MirroringMonitorResults
	EXECUTE msdb..sp_dbmmonitorresults @dbname, 9, 0;

WITH reduced_data_cte ([ROW], [ROLE], mirroring_state, witness_status, local_time) AS (
	SELECT ROW_NUMBER() OVER (ORDER BY local_time),
	[ROLE],
	mirroring_state,
	witness_status,
	local_time
	FROM #MirroringMonitorResults
	WHERE time_recorded BETWEEN @start AND @end
	),
first_time_disconnected_cte ([ROW]) AS (
	SELECT MIN([ROW])
	FROM reduced_data_cte
	WHERE ( mirroring_state != 4
			OR
			[ROLE] != 1
			)
	)

SELECT	[ROLE] =	CASE [ROLE]
						WHEN 1 THEN 'Principal'
						WHEN 2 THEN 'Mirror'
						ELSE 'Unknown'
					END,
		mirroring_state =	CASE [mirroring_state]
								WHEN 0 THEN 'Suspended'
								WHEN 1 THEN 'Disconnected'
								WHEN 2 THEN 'Synchronizing'
								WHEN 3 THEN 'Pending Failover'
								WHEN 4 THEN 'Synchronized'
								ELSE 'Unknown'
							END,
		witness_status =	CASE witness_status
								WHEN 0 THEN 'Unknown'
								WHEN 1 THEN 'Connected'
								WHEN 2 THEN 'Disconnected'
							END,
		local_time
FROM reduced_data_cte d INNER JOIN first_time_disconnected_cte r
    ON r.[row] - @rowOffset < d.[row]
ORDER BY local_time;

Let me know what you think.

Thanks for reading.

Running Totals

By , February 7, 2011 19:02

This morning I read a blog post where a solution for calculating running totals was presented. I wanted to post a code correction as a comment to the post itself, but after three attempts (three site errors) to submit my comment, I gave up and decided to do a quick post of my own.

I don’t want to drag anybody through the mud on this, so I wont post a link to the blog, but this is how the code was presented (I have edited it so that it can’t be used to search for the original post).

DECLARE @RunningTotal TABLE (Amount INT)

INSERT INTO @RunningTotal VALUES (1);
INSERT INTO @RunningTotal VALUES (2);
INSERT INTO @RunningTotal VALUES (3);
INSERT INTO @RunningTotal VALUES (4);
INSERT INTO @RunningTotal VALUES (5);

SELECT
     a.Amount
     ,(
		SELECT
          Sum(b.Amount)
		FROM @RunningTotal b
		WHERE b.Amount <= a.Amount
	  ) rs
FROM
     @RunningTotal a

I found the idea fairly nifty, but what I wanted to challenge was the fact that this script is entirely dependant on the order of the values stored in Amount and that if a particular amount were to be entered more than once, the results would be incorrect.

Try the above with the following values.

INSERT INTO @RunningTotal VALUES (1);
INSERT INTO @RunningTotal VALUES (2);
INSERT INTO @RunningTotal VALUES (3);
INSERT INTO @RunningTotal VALUES (4);
INSERT INTO @RunningTotal VALUES (5);
INSERT INTO @RunningTotal VALUES (2);

Even if pre-sorted, the results would be partially incorrect.

It did however make me think about how I would get the required results. My first idea was to use ROW_NUMBER() and I ended up having to use a CTE too. But it works no matter what order the rows are in!

;WITH RunningTotal_CTE( RowNo, Amount)
AS
(
	SELECT
	ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as RowNo,
	Amount
	FROM @RunningTotal
)

SELECT	
		a.Amount,
		(
			SELECT SUM (b.Amount)
			FROM RunningTotal_CTE b
			WHERE b.RowNo <= a.RowNo
		) RunningTotal
 FROM RunningTotal_CTE a

Panorama Theme by Themocracy