## Recursive CTEs Comments (0)
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 Comments (0)
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.

## Running Totals Comments (0)
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```