Audit table data changes in SQL Server 2005

Some time ago I happened to comment on the post, "How do I track data changes in a database" ...

My comment didn't come out as understandable, so I wrote this little piece to explain how to audit changes in a table.

First I create a table to record the changed data:

CREATE TABLE [dbo].[Audits] (
  [rowguid] uniqueidentifier NOT NULL,
  [timestamp] datetime DEFAULT GETDATE() NOT NULL,
  [table_name] VARCHAR(255) NOT NULL,
  [user] VARCHAR(255) DEFAULT SUSER_SNAME() NOT NULL,
  [data] xml NOT NULL,
  CONSTRAINT [Audits_pk] PRIMARY KEY CLUSTERED ([rowguid], [timestamp])
)
ON [PRIMARY]

Then I create a trigger for the tables I wish to audit, if you wish you can automate this for all your tables. 

I use the contact table from the Adventureworks sample database to illustrate this example. It is important to add an UNIQUEIDENTIFIER column to your tables to make this work. The adventureworks database is full of such columns for replication use ...

CREATE TRIGGER [Person].[Contact_Audit_tr] ON [Person].[Contact]
WITH EXECUTE AS CALLER
FOR UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @deletedrows TABLE (id INT IDENTITY(1,1), rowguid UNIQUEIDENTIFIER)
  DECLARE @rowcount INT
  DECLARE @i INT
  DECLARE @table_name VARCHAR(255)
  DECLARE @data XML
  DECLARE @rowguid UNIQUEIDENTIFIER

  -- Obtain the name of the table the trigger belongs to
  SELECT @table_name = OBJECT_NAME(parent_obj)
  FROM
    sys.sysobjects
  WHERE
    id = @@PROCID

  -- Using a while loop instead of a cursor because cursors are evil :-)
  -- I wish I could specify that a trigger was guaranteed to fire for a single row
  -- How can I test a case where I get multiple rows in the deleted or insert table??

  INSERT @deletedrows (rowguid)
  SELECT rowguid FROM DELETED
  SET @rowcount = @@ROWCOUNT
  SET @i = 1
  WHILE @i <= @rowcount
  BEGIN
    SELECT @rowguid = rowguid
    FROM @deletedrows
    WHERE
      id = @i
    SET @data = (SELECT * FROM DELETED WHERE rowguid = @rowguid FOR XML AUTO, ELEMENTS)
    INSERT INTO dbo.Audits (rowguid, table_name, data)
      VALUES (@rowguid, @table_name, @data)
    SET @i = @i+1
  END
END

Thats it, you got the previous verisons of the changed rows stored as XML.

SQLMan

Is it a plane? Is it a ... No it's SQLMan!! Forged in the laboratory of Dr. Codd, he got the powers of SQL:2003, ISO-11179 and ISO-8601. He is here to rescue us from Web 2.0 XP-Agile-cowboy coders, who have just discovered the incredible powers of the relational database. But in theire feeble quest to take over the world, they misuse their new found powers using surrogate keys, map tables to classes, don't abstract from the underlaying architecture, thinking colums are fields and reading "Teach yourself SQL in 24 hours" rader than "SQL programming Style". The powers of SQL takes decenniums to master, in the wrong unexperienced hands it may be a totally disaster. Luckily SQLman is here now!
Read about he's ongoing fight against the unlighted here!

This post was inspired by this post by Ken Henderson ...

The dreaded ID or even worse IDENTITY

Ruby on Rails, the convention over configuration framework, wants us to have an ID column named ID as a primary key in every table. Now THAT is poor SQL! If we look at the Microsoft Adventureworks database they use singular form object for table names and use the singular form+ID for every table as in Vendor and VendorID. Poor SQL! So what should you use?

I use my own convention.:

Say I have a table Vendors, then the Vendor ID column I call Vendor. For foreign keys to Vendors I call the column Vendor, rather than as Rails would like me to Vendor_ID.

For me this works very well. I find it very clear and consistent. When I talk to customers they always refer to stuff like "the Project" meaning the Project ID for a given Project. So in my Projects table I have the primary key column Project. Rails is very good at distinguishing plural forms and singular forms of words so why didn't they come up with this convention?

And why do Microsoft make a sample database full of horrible SQL? Beats me! Or I may have read to much Joe Celko.

"Joe Celko the SQL apprentice" blog can be found here. I'm pretty sure it's not maintained by Mr. Celko, but the answers are very Celkoish (Would Celko ever reference SQL Server 2005 as SQL-2005?? NO!!!)

Now I have to hack Rails to follow my convention ;-)

Happy codein(g,e)!

Split a String using recursive SQL

While sitting under my peartree, having a smoke, looking at the lawn and deciding that I had to mow it. Thinking I just had to catch up on my feed and watch the news before I started up the lawnmower, it suddenly occured to me that rather than using a numberstable (CTE) to split a string you could use a recursive SQL. Why I came to think of it? All the time I read blogs about Lisp, functional languages and how everything is so much cooler if you just start to think recursively ... :-)
Here is the SQL server code, just cut and paste into a querywindow in Managment Studio and hit F5

DECLARE @Delimitedtext varchar(max);
DECLARE @Delimiter char(1);
SET @Delimitedtext = 'aaa,bbbb,cccc,dddd,';
SET @Delimiter = ',';

WITH Strings(s, r)
AS
(
SELECT
SUBSTRING(@Delimitedtext,1, CHARINDEX(@Delimiter, @Delimitedtext)-1) s,
SUBSTRING(@Delimitedtext,CHARINDEX(@Delimiter, @Delimitedtext)+1, len(@Delimitedtext)) r
UNION ALL
SELECT
SUBSTRING(r,1, CHARINDEX(@Delimiter, r)-1) s,
SUBSTRING(r,CHARINDEX(@Delimiter, r)+1, len(r)) r
FROM Strings
WHERE
CHARINDEX(@Delimiter, r) > 0
)
SELECT s FROM Strings

The query will yield this result:

s
-------------------
aaa
bbbb
cccc
dddd

(4 row(s) affected)

Have fun Querying (SQL is not defined as a programming language, so you really can't have fun programming SQL ;-))

The Auxiliary Calendar Table For SQL Server 2005

A common auxiliary table is the calendar table. A calendar table is a table with a row for each and every date within a period. The table contains a column for the date and columns for additional data about the date, most useful in SQL Server would be ISO week as the datepart function is totally off.
But as we are at it we might as well split the date into year, month, day, quarter, add a holiday flag, a workday flag and a description column to indicate special days.
The calendar table is of course also useful in outer joins where you want to retrieve a row for every date even if there are no data for that date.
With the advent of CTE's creating a table just for the join is no longer necessary as you can use a recursive CTE for it, much as shown in my earlier post.
Continuing on the standard auixiliary schema:
Here comes the commented script for creating and populating the table:

CREATE TABLE [Auxiliary].[Calendar] (
-- This is the calendar table
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE [Auxiliary].[Calendar]
-- In Celkoish style I'm manic about constraints (Never use em ;-))


ADD CONSTRAINT [Calendar_ck] CHECK ( ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week] BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO

SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601

-- thus first day of a week is monday

WITH Dates(Date)
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' AS DateTime) -1
),

DatesAndThursdayInWeek(Date, Thursday)
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),

Weeks(Week, Thursday)
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

-- Fiscal year may be different to the actual year, in Norway the are the same

YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- I wrote about the computus function here

WHEN (DATEPART(DayOfYear, d.Date) = 1) -- New Year's Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))-7) -- Palm Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-3) -- Maundy Thursday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-2) -- Good Friday
OR (d.Date = Auxiliary.Computus(YEAR(Date))) -- Easter Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- Ascension Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- Pentecost
OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) -- Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) -- Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) -- Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) -- Boxing day
THEN 'HOLIDAY'
WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
ELSE 'BANKDAY'
END KindOfDay,
CASE
-- Description of holidays in Norway
WHEN (DATEPART(DayOfYear, d.Date) = 1) THEN 'New Year''s Day'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7) THEN 'Palm Sunday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3) THEN 'Maundy Thursday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2) THEN 'Good Friday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))) THEN 'Easter Sunday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN 'Ascension Day'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN 'Pentecost'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN 'Whitmonday'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) THEN 'Labour day'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) THEN 'Constitution day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) THEN 'Cristmas day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) THEN 'Boxing day'
END Description

FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
inner join Weeks w
on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)

You can download the entire script here.
While I write som nice examples you can find additional resources and examples on using calendar tables here:
Why should I consider using an auxiliary calendar table? by Aaron Bertrand
Auxiliary Angst by Joe Celko
Getting previous month/years/days from your calendar table
by Louis Davidson

Computus - Easter Sunday in SQL Server 2005

Working on an article about an auxiliary calendar table I needed to find a routine for calculating Easter Sunday. Many years ago I made my Delphi routine for this, and haven't really thought much about it since. Reading an article about calendar tables I found a function doing the trick. I tested the routine and it happened to fail so I needed to find another one. Luckily today we have wikipedia, something we didn't 10 years ago when I found my last Easter Sunday function.

From Wikipedia: Computus (Latin for computation) is the calculation of the date of Easter in the Christian calendar.
In the article I found the Meeus/Jones/Butcher Gregorian algorithm that is supposed to work for any Easter ever in the Gregorian calendar.

CREATE FUNCTION [Auxiliary].[Computus]
(
@Y INT -- The year we are calculating Easter Sunday for
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@a INT,
@b INT,
@c INT,
@d INT,
@e INT,
@f INT,
@g INT,
@h INT,
@i INT,
@k INT,
@L INT,
@m INT

SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b - @f + 1) / 3
SET @h = (19 * @a + @b - @d - @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L - 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L - 7 * @m + 114) % 31))
END

I got no clue about the intricacy of this math. But it works and that's fine for me :-)

Next: "The Auxiliary Calendar Table For SQL Server 2005" ...

Auxiliary roundup - The SQL table valued for loop function?

After creating the numbers CTE in my last post I wanted to go on building my standard auxiliary schema. First of all lets create the schema. Quite simple:

CREATE SCHEMA [Auxiliary]

A schema in SQL server 2005 is a collection of database entities that form a single namespace.
I now got a place to put auxiliary entities.

First off, I decided to put my Numbers CTE into a table valued function. I wanted to make it a bit flexible by giving the startnumber, endnumber and increment as parameters.
Incidentally this suddenly looked a little bit like the For(i=1; i <=1000; i++) construct in C and C#

CREATE FUNCTION Auxiliary.Numbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS @RetNumbers TABLE
(
[Number] int PRIMARY KEY NOT NULL
)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END;

To use this to retrieve a list of numbers between 10 and 1000 incremented by 10:

SELECT Number FROM Auxiliary.Numbers(10,1000,10)

This will return:

Number
-----------
10
20
30
...
980
990
1000

(100 row(s) affected)

For a large number of numbers this function is quite slow. An inlined version of this performs much better, but you need to specify the MAXRECURSION hint in the statement using the function:

CREATE FUNCTION Auxiliary.iNumbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS TABLE
AS
RETURN(
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
SELECT n AS Number from Numbers
)

To use this to retrieve a list of numbers between 10 and 1000 incremented by 10:

SELECT Number FROM Auxiliary.iNumbers(10,1000,10)

If your "table" will return more than 101 numbers (100 recursions):

SELECT Number FROM Auxiliary.iNumbers(1,10000,1)
OPTION(MAXRECURSION 10000)

If you need really many numbers then you definitely should build a permanent numberstable.
Louis Davidson wrote a nice post a about it here.

SQL Numbers table using a Common table expression (CTE)

With SQL server 2005 Microsoft finally implemented recursive SQLs as described in the ISO-ANSI SQL3 standard (Implemented in DB2, Sybase and partly Oracle a "loooong" time ago).
This new and exciting feature dubbed CTE (Common table expression) is supposed to be particular useful for traversing trees and hierarchies using SQL.
Reading Joe Celcos SQL Programming style (Highly recommended) I came acorss a concept called "numbers table".
This is a table with a sequence of integer numbers. Such a table is quite useful for parsing strings and generating testdata.
Thinking of uses for CTE's I figured they could be used to create an ad-hoc numbers table. Such a construct will also serve as an easy to understand example of a recursive query.

WITH Numbers(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < 1000
)
SELECT n from Numbers
OPTION(MAXRECURSION 1000) -- defaults 100

What happens in a recursive query is that the first select statement initializes the query and returns the first row(s),
the second query then executes for every row added to the result (Selecting from itself)
Thus selecting n incremented by one and adding the row(s) to the result.

To parse a string you could do it like this:


DECLARE @Delimitedtext varchar(max);
DECLARE @Delimiter char(1);
SET @Delimitedtext = ',aaa,bbbb,cccc,';
SET @Delimiter = ',';
WITH Numbers(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < 1000 )
SELECT
SUBSTRING(@DelimitedText, Numbers.n + 1, CHARINDEX(@Delimiter, @DelimitedText,Numbers.n + 1) - Numbers.n - 1)
FROM Numbers
WHERE
Numbers.n <= LEN(@DelimitedText) - 1 AND
SUBSTRING(@DelimitedText, Numbers.n, 1) = @Delimiter
AND
LEN(SUBSTRING(@DelimitedText, Numbers.n + 1,CHARINDEX(@Delimiter, @DelimitedText, Numbers.n + 1) - Numbers.n - 1)) > 0
OPTION (MAXRECURSION 1000)

More fun with number tables can be found here

The first post!

Really this is just a test post. The intent of this blog is to actually log some of the stuff I do. That is for later reference and to give some structure to gained knowledge and experiences . I will probably mostly post stuff about SQL Server and Borland Delphi ...