Persitant Logging through Aborted Transactions

Sometimes you want to log that a transaction failed when the transaction is aborted. This can be a problem when the logging is part of the rolled-back transaction.

CREATE TABLE TranLogging (id int)
CREATE TABLE Test (id int)
CREATE TABLE #Test (id int)
DECLARE @TranLogging table (id int)

BEGIN TRAN
INSERT INTO @TranLogging VALUES(1)
INSERT INTO Test VALUES(1)
INSERT INTO #Test VALUES(1)
ROLLBACK TRAN

SELECT '@TranLogging',COUNT(*) FROM @TranLogging
SELECT ' test',COUNT(*) FROM Test
SELECT '#test',COUNT(*) FROM #Test

insert into TranLogging
select * from @TranLogging

select * from TranLogging

DROP TABLE Test,#Test,TranLogging

You'll notice that the row inserted into the table variable was maintained after the rollback, you can use this after a rolled-back transaction to copy failures/abortions into a more persistent logging table.

Capital IQ Screening Team mentioned on TheStreet.com

As you probably know I work for Capital IQ on the Screening & Analytics Team, I'm always interested to see how clients use the tools I help develop. Today we were mentioned on TheStreet.com as a tool used by one of their writers, although we do have the majority of wall street as clients, its always cool to see a mention in the media.

These Gene-Drug Stocks Are Getting Hot

Calculating Medians in TSQL using 2005 Window Functions

Calculating Medians can be a common and expensive operation in many applications, inefficiently

The hard part about medians is that you need to take the middle value for an odd number of elements and the 2 middle values for an even number of elements.

The cool thing here is that when you can take these two sequences sorted in opposite directions the absolute difference between the two is smaller than or equal to 1 only for elements that are required for the median calculation.

In this case you’re using ‘memberid’ (or some other unique value) as the tiebreaker to guarantee determinism of the row number calculations. This is required for using this trick to figure out the median.

Once you grab only the values you need for the median calculation you can isolate them by grouping them by groupid and calculate the average for each group.

WITH RN AS
(
SELECT groupid, val,
ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY val, memberid) AS rna,
ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY val DESC, memberid DESC) AS rnd
FROM dbo.Groups
)

SELECT groupid, AVG(1.*val) AS median
FROM RN
WHERE ABS(rna - rnd) <= 1
GROUP BY groupid;

Here’s the temp table I made to test this out

USE tempdb;
GO
IF OBJECT_ID('dbo.Groups') IS NOT NULL
DROP TABLE dbo.Groups;
GO

CREATE TABLE dbo.Groups
(
groupid VARCHAR(10) NOT NULL,
memberid INT NOT NULL,
string VARCHAR(10) NOT NULL,
val INT NOT NULL,
PRIMARY KEY (groupid, memberid)
);

INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('a', 3, 'stra1', 6);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('a', 9, 'stra2', 7);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 2, 'strb1', 3);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 4, 'strb2', 7);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 5, 'strb3', 3);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 9, 'strb4', 11);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 3, 'strc1', 8);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 7, 'strc2', 10);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 9, 'strc3', 12);
GO