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 (
		, cast(1 as bigint)
	union all
		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)

The second is the Fibonacci sequence:

use Work;
with Fibonacci_CTE (RowNum, Increment, Fibonacci) as (
		, cast(0 as  numeric(38,0))
		, cast(1 as  numeric(38,0))
	union all
		RowNum + 1
		, 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


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.

Results from multiple rows in a single field

By , May 16, 2014 19:12

Excuses, excuses
A short one for the first time in a very long time. After starting work for a new Company, I for one, don’t have much time to do any blogging and for another, I don’t do too much on databases any more (unfortunately), other than performance tuning by means of indexing.

Today, for the first time in quite a while, I wrote a stored procedure! The requirement was to build an electronic invoice ledger from invoice data in an accounting system. Once I understood the data structure of the accounts, it was simple enough to extract the data that was required. One of the trickier parts was, wanting to keep it “set based”,  listing all invoice attachments in a single column in the final result. This meant having to take values from a column in multiple rows and concatenate them into a single entry. That is simple enough (yet very clunky), when working in a loop, but in a single select it is a bit tricky. Now this has been done often enough and there are plenty of solutions out on the net, in fact I am sure that what I did has been blogged about often enough. So, really just for my records and future reference, here is what I did.

One of the requirements was to get all attachments for an invoice and put them into a single field separated by a single space. Each attachment was a single record in a table. I am going to keep it simple here and only work with a pseudo attachment table and not bother with joining in the invoice data too. It will suffice to say, that the common-key between the invoice and its attachments is the barcode field.

Just do it
The DDL und DML for the test data:

use Work;

if (OBJECT_ID('dbo.attachments') is null)

	create table dbo.attachments (
		id int identity(1,1),
		barcode varchar(22),
		atdate date,
		attachment varchar(20)
	truncate table dbo.attachments;

insert into dbo.attachments
	(barcode, atdate, attachment)
	('11111111111111111', GETDATE(), '9900'),
	('11111111111111111', GETDATE(), '9800'),
	('11111111111111111', GETDATE(), '9700'),
	('11111111111111111', GETDATE(), '9600'),
	('11111111111111111', GETDATE(), '9500'),
	('11111111111111111', GETDATE(), '9400'),
	('11111111111111111', GETDATE(), '9300'),
	('11111111111111111', GETDATE(), '9200'),
	('11111111111111111', GETDATE(), '9100'),
	('11111111111111111', GETDATE(), '9000'),
	('22222222222222222', GETDATE(), '8900'),
	('22222222222222222', GETDATE(), '8800'),
	('22222222222222222', GETDATE(), '8700'),
	('22222222222222222', GETDATE(), '8600'),
	('22222222222222222', GETDATE(), '8500'),
	('22222222222222222', GETDATE(), '8400'),
	('33333333333333333', GETDATE(), ''), --intentionally left blank to compensate for XML blank
	('33333333333333333', GETDATE(), '7800'),
	('33333333333333333', GETDATE(), '7700'),
	('33333333333333333', GETDATE(), '7600'),
	('33333333333333333', GETDATE(), '7500'),
	('33333333333333333', GETDATE(), '7400'),
	('33333333333333333', GETDATE(), '7300'),
	('33333333333333333', GETDATE(), '7200'),
	('33333333333333333', GETDATE(), '7100'),
	('33333333333333333', GETDATE(), '7000')

Now the extraction DML:

use Work;

select at1.barcode, attachments =
		select distinct attachment + ' '
		from attachments at2
		where at2.barcode = at1.barcode
		for xml path ('')
from dbo.attachments at1
group by at1.barcode

I used the for xml path clause to get the attachment out of dbo.attachments as a single string. Because I concatenate with a blank (”) the XML node structure is not returned, but rather just all the results for the attachments in a single string of characters. If you don’t concatenate, then the result looks something like this:

Now because we are converting the values to XML the blank value for the attachment (first entry for ) is interpreted as the following:

&#x20;” being an entity reference for the blank space character.

Now this is obviously not desired so that needs to be considered in the sub-select:

use Work;

select at1.barcode, attachments =
				select distinct attachment + ' '
				from attachments at2
				where at2.barcode = at1.barcode
				and isnull(at2.attachment, '') != ''
				for xml path ('')
from dbo.attachments at1
group by at1.barcode

Here is the final result:

A common requirement is to build a comma separated list. This would be done by concatenating ‘ ,’ before the value being selected and then using substring to remove the first comma.

The DML could look like this:

use Work;

select at1.barcode, attachments =
					select distinct ', ' + attachment
					from attachments at2
					where at2.barcode = at1.barcode
					and isnull(at2.attachment, '') != ''
					for xml path ('')
			3, 1000)
from dbo.attachments at1
group by at1.barcode

And finally
I do hope that this may help someone or other out with solving their problem of getting multiple values into a single field.
Thanks for reading.

Index Usage in Dynamic Management Views

By , May 15, 2012 21:37

This is just a short one to post a script for a procedure to return index usage either for all non-Microsoft indexes or just for a single non-Microsoft.

There are loads of variations on this on the net and this is just my version:

CREATE PROCEDURE up_indexusage
	@table_name sysname = null
	OBJECT_NAME(i.[object_id]) AS 'table_name', AS 'index_name',
FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s 
	ON s.object_id = i.object_id
	AND s.index_id = i.index_id
	AND (s.database_id = DB_ID() OR s.database_id IS NULL)
WHERE (OBJECTPROPERTYEX(i.[object_id], 'IsMsShipped') = 0)
AND	(OBJECT_NAME(i.[object_id]) = 
			WHEN @table_name IS NULL THEN OBJECT_NAME(i.[object_id])
			ELSE @table_name
AND (s.database_id = DB_ID() OR s.database_id IS NULL)
ORDER BY OBJECT_NAME(i.[object_id]), i.index_id

One very important thing to note (which I didn’t know and only saw for the first time today): These index usage statistics are reset when a mirrored database fails over.
Books Online state “The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.”
There is no explicit mention of mirroring (failover) there :-|.

So caution should be exercised when using a statement like this to identify unwanted indexes. It would be best to be sure the server has been running and no failover has taken place for an extended period of time.

Thanks for reading.

Displaying Included fields for a NONCLUSTERED Index

By , May 12, 2012 08:58
The standard stored procedure sp_helpindex unfortunately doesn’t display the included fields which can be used with NONCLUSTERED indexes.
I have written a user stored procedure to get around this. I wanted to use a set based solution but couldn’t do so within a reasonable amount of time, so I have reverted to a cursor (something I haven’t written in a log time).
The user stored procedure saves the results of sp_helpindex to a temporary table and the procedes to gather the included fields from the metadata views provided by SQL server.
 @object_name sysname


IF OBJECT_ID('tempdb.dbo.#HelpIndexResults') IS NOT NULL
 DROP TABLE #HelpIndexResults;

CREATE TABLE #HelpIndexResults (
index_name sysname,
index_description VARCHAR(210),
index_keys NVARCHAR(2078),
include_keys NVARCHAR(2078) DEFAULT ''

--why reinvent the wheel? - use standard procedures
INSERT INTO #HelpIndexResults (index_name, index_description, index_keys)
 EXECUTE sp_helpindex @object_name;


DECLARE @index_name sysname = '';
DECLARE @col_order_reversed INT;
DECLARE @column_name  sysname;

--now get the INCLUDE keys
DECLARE include_field_cursor CURSOR FOR
 ROW_NUMBER() OVER (PARTITION BY ic.index_id ORDER BY ic.index_id, ic.index_column_id DESC), AS index_name, AS column_name
FROM sys.indexes i INNER JOIN sys.index_columns ic
 ON ic.object_id = i.object_id
 AND ic.index_id = i.index_id
INNER JOIN sys.columns c
 ON c.object_id = ic.object_id
 AND c.column_id = ic.column_id
WHERE ic.is_included_column = 1
ORDER BY ic.index_column_id;

OPEN include_field_cursor;

FETCH NEXT FROM include_field_cursor
 INTO @col_order_reversed, @index_name, @column_name;

 UPDATE #HelpIndexResults
 SET include_keys += CASE
 WHEN @col_order_reversed &gt; 1 THEN @column_name + ', '
 WHEN @col_order_reversed = 1 THEN @column_name
 WHERE index_name = @index_name;
 FETCH NEXT FROM include_field_cursor
 INTO @col_order_reversed, @index_name, @column_name;

CLOSE include_field_cursor;
DEALLOCATE include_field_cursor;

UPDATE #HelpIndexResults
SET include_keys = ''
WHERE include_keys = '';

SELECT index_name, index_description, index_keys, include_keys
FROM #HelpIndexResults
ORDER BY index_name;

I would be interested to see any comments about getting this done in a set-base fashion.
Thanks for reading.

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

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

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,
	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 (
	FROM #MirroringMonitorResults
	WHERE time_recorded BETWEEN @start AND @end
first_time_disconnected_cte ([ROW]) AS (
	FROM reduced_data_cte
	WHERE ( mirroring_state != 4
			[ROLE] != 1

						WHEN 1 THEN 'Principal'
						WHEN 2 THEN 'Mirror'
						ELSE 'Unknown'
		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'
		witness_status =	CASE witness_status
								WHEN 0 THEN 'Unknown'
								WHEN 1 THEN 'Connected'
								WHEN 2 THEN 'Disconnected'
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.

Missing Toolbox Items in Business Intelligence Development Studio

By , January 10, 2012 19:28

I haven’t blogged for ages as I have found it difficult to set time aside to do so, but I wanted to share this little bit of information all the same.

I wanted to create a SSIS package and found that my toolbox was no longer displaying the controls that I was expecting. The toolbox panel displayed a single group with the title “#13119”.

The problem was that the toolbox cache for Intelligence Development Studio (in my case Visual Studio) was shot – how this happened I don’t know.

The solution was to close VS, delete the toolbox*.tbd items under “C:\Users\USERNAME\AppData\Local\Microsoft\VisualStudio\9.0” and start VS again. The toolbox controls appeared as expected.
NOTE: The path is valid for Windows7 (and presumably Vista). For XP it would be something like this: “C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\VisualStudio\9.0”.

I found the solution on a German website, so this is really just an English translation, which I hope proves to be useful. (Thank you Rainer Hilmer)

BIT Data Type

By , February 23, 2011 20:18

I stumbled upon (or rather over) a little something this morning while I was writing a procedure one of whose parameters was of type bit.

Books Online states that the bit datatype is “An integer data type that can take a value of 1, 0, or NULL”.

Anyway, I wrote the procedure and while testing it, I was expecting to get an error when passing anything other than 1, 0 or NULL the the bit parameter. I was surprised to find that the bit will always have the value 1 when a value other than 0 or NULL is passed to it, even if the value is not an integer, but rather of type numeric (i.e. decimal or float). Even negative values are interpreted as 1.


DECLARE @nullbit BIT
DECLARE @falsebit BIT
DECLARE @truebit BIT
DECLARE @decimalbit BIT

SET @falsebit = 0
SET @truebit = 1
SET @posbit = CAST(POWER(2.0,63 -1)  AS BIGINT)
SET @negbit = CAST(POWER(-2.0,63)  AS BIGINT)
SET @decimalbit = -0.1

SELECT @nullbit AS [NULL], @truebit AS [TRUE], @falsebit AS [FALSE],  @posbit AS positive, @negbit AS negative, @decimalbit AS [DECIMAL]


Discovering that even bigint values were permitted, I decided to push it a little. Try the following and you’ll see that all values, between -2^122 and 2^122 are interpreted as 1 (with, of course, 0 as an exception).

DECLARE @results TABLE (expression FLOAT(53), bitvalue BIT)
DECLARE @i FLOAT(53)  = -122.0

		INSERT INTO @results
			SELECT	@i, 
						WHEN @i < 0 THEN CAST(POWER(-2.0,@i * -1) AS BIT)
						WHEN @i = 0 THEN CAST(@i AS BIT)
						ELSE CAST(POWER(2.0,@i) AS BIT)
		SET @i += 1
		SELECT * FROM @results

I was expecting my procedure to report some sort of error when a "false" value was passed to it. I think the thing to learn here is: always test to see if things work the way you expect them to.

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);

		FROM @RunningTotal b
		WHERE b.Amount <= a.Amount
	  ) rs
     @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)
	FROM @RunningTotal

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

Enabling and Disabling Triggers

By , August 18, 2010 20:47

Yesterday, a colleague of mine proudly told me about a workaround that he had put together in the form of a trigger. “The work around”, he said “can simply be switched on and off by creating or dropping the trigger.” He had a glint in his eye until I asked him if he was aware that triggers can be disabled while still leaving the code in place. His solution would have meant keeping the T-SQL for the trigger on the server’s file system in case it had been “switched off” and needed to be “switched on” again, which is not really conform with best practices.

So I provided him with the code below, which I wrote for a project I dealt with a little while ago. I am also, for the sake of testing, including a little bit of DDL in the post. Also note that the test-trigger, that I include, deals with all rows affected by the triggering statement and not just one. This is a common mistake that is made (I have made it myself) when triggers are written.

use ThinkTank

if OBJECT_ID('dbo.TriggerTest') is not null
	drop table dbo.TriggerTest

create table dbo.TriggerTest (
	id int identity(1,1) primary key,
	guid_field uniqueidentifier ,
	calculated_field bigint

Now the code for the trigger. Note the inner join between the logical table “inserted” and the table dbo.TriggerTest. This ensures that the trigger fires for all rows affected by the triggering statement.

create trigger dbo.tr_GenerateCalculatedFieldValue on dbo.TriggerTest
after insert

update tt
set calculated_field = + 111 --an arbitrary data manipulation for testing
	from dbo.TriggerTest tt
	inner join inserted i
	on =

Ok, so that is the DDL required to test what this post is actually about. Now for the code snippet which I have packed into a user stored procedure:

use ThinkTank

create procedure dbo.up_ReverseTriggerStatus
	@TriggerName sysname,
	@TableName sysname,
	@SchemaName sysname = 'dbo'

declare @IsDisabled bit
declare @ChangeStatusCommand nvarchar(500)
declare @QualifiedTable nvarchar(260)
declare @QualifiedTrigger nvarchar(260)
declare @UserMessageVar varchar(8)

--validate values passed in to the procedure and prevent sql injection
--First check to see that we are dealing with a valid schema
if schema_id(@SchemaName) != 1 or schema_id(@SchemaName) is null
	raiserror ('"%s" is not a valid schema!', 16, 1, @SchemaName)

--build the qualified names
set @QualifiedTable = quotename(@SchemaName) + '.' + quotename(@TableName)
set @QualifiedTrigger = quotename(@SchemaName) + '.' + quotename(@TriggerName)

-- Now check to see that we have a trigger with this schema and name
if objectpropertyex(object_id(@QualifiedTrigger), 'IsTrigger') != 1
or objectpropertyex(object_id(@QualifiedTrigger), 'IsTrigger') is null
	raiserror ('%s is not a known trigger!', 16, 1, @QualifiedTrigger)

--Then check to see that the table exists in the database
if objectpropertyex(object_id(@QualifiedTable), 'IsTable') != 1
or objectpropertyex(object_id(@QualifiedTable), 'IsTable')  is null
	raiserror('%s is not a valid table object!', 16, 1, @QualifiedTable)
else if objectpropertyex(object_id(@QualifiedTable), 'IsTable') = 1
--OK, we've gotten this far, so the parameters aren't injected.
--It is also necessary to check that the trigger is actually installed on the table
	select @IsDisabled = is_disabled
		from sys.triggers
			where name = @TriggerName --unqualified
			and parent_id = object_id(@QualifiedTable)

if @IsDisabled is null
	raiserror('The trigger %s is not installed on %s', 16, 1, @QualifiedTrigger, @QualifiedTable)

--now we can get started with the actual work
--this would normally be done in one step, but for the sake of keeping in on the monitor...
set @ChangeStatusCommand = N'&command trigger &trigger on &table'
set @ChangeStatusCommand = replace(@ChangeStatusCommand,  '&table', @QualifiedTable)
set @ChangeStatusCommand = replace(@ChangeStatusCommand, '&trigger', @QualifiedTrigger)

if @IsDisabled = 1
--here we want to re-enable the trigger
	set @ChangeStatusCommand = replace(@ChangeStatusCommand, '&command', 'enable')
	set @UserMessageVar = 'enabled'
else if @IsDisabled = 0
--here we want to disable the trigger
	set @ChangeStatusCommand = replace(@ChangeStatusCommand, '&command', 'disable')
	set @UserMessageVar = 'disabled'

execute sp_executesql @ChangeStatusCommand
raiserror('The trigger %s was %s on %s.', 10, 1, @QualifiedTrigger, @UserMessageVar, @QualifiedTable)


Of course we need to test this solution. The standard status for a trigger is enabled, so the following code fires the trigger and we get the expected results.

use ThinkTank

insert into dbo.TriggerTest (guid_field)
select top 10 NEWID() from sys.columns a cross join sys.columns b

If we disable the trigger using the procedure “up_ReverseTriggerStatus”, the field “calculated_field” is not filled when the above statement is next run. Try it out and see for yourself.

In Conclusion

I haven’t included any error handling or the like, as I would really like to leave that up to each user to do for his- or herself and I didn’t want to bloat this post too much. It already has more code than I intended when I originally started writing the post (I really just wanted to blog about the fact that enable/disable trigger actually exists). It is, however, interesting to note that the bulk of the code is dedicated to parameter validation and that the core functionality comprises less than half of the code. It might actually be worthwhile looking into reducing the code for the validation. Perhaps I’ll do that at a later date.

Insert, Update or Delete Trigger

By , May 17, 2010 18:44

This one is a bit of a no-brainer and is really just for me, but if it does help anyone out there then I suppose it was worth posting.

I had to write a trigger today to monitor changes to data in a table. The client application does DML for the login language of the current user and the trigger has to do the same for the other languages for which the system was configured, so that clients logging on using another language also see the current data. For example: if a user in England inserts a new part number (along with description etc…), the same information has to be available for users in Romania and Germany. The same applied to updated and deleted data.

I generally don’t write many triggers, as I have had bad (really bad) experiences in the past, with undocumented triggers. But, the requirement was that a trigger be implemented.

Because the code that has to be run varies depending on which DML event was executing the trigger, I had to establish which event was occurring. I could have written three triggers, once each for insert, update and delete but I didn’t want to have three db-objects essentially linked by functionality and purpose, but acting as separate entities. This can get rather confusing when someone else has to maintain and or support the database and application using the database. (I have yet to meet the support-technician that reads system documentation at 3am). So I figured: Just give them one trigger to deal with.

This is how I went about finding out which DML event was firing the trigger:

create trigger TR_TriggerName
on Table
after insert, update, delete

declare @Update bit = 0
declare @Insert bit = 0
declare @Delete bit = 0

if exists (select * from inserted) --could be update or insert, but not delete
	if exists (select * from deleted) --this is an update statement
		select	@Update = 1,
				@Insert = 0,
				@Delete = 0;
	else --this is an insert statement
		select	@Insert = 1,
				@Update = 0,
				@Delete = 0;
else --can only be a delete because nothing was in inserted
	select	@Delete = 1,
			@Insert = 0,
			@Update = 0;

if (@Insert = 1)
--insert code for insert procedure
else if (@Update = 1)
--insert code for update procedure
else if (@Delete= 1)
--insert code for delete procedure

Panorama Theme by Themocracy