The Luhn algorithm using a recursive CTE

The other day I needed to implement the modulus 10 function in SQL.
From Wikipedia

The Luhn algorithm or Luhn formula, also known as the "modulus 10" or "mod 10" algorithm, is a simple checksum formula used to validate a variety of identification numbers, such as credit card numbers and Canadian Social Insurance Numbers. It was created by IBM scientist Hans Peter Luhn and described in U.S. Patent 2,950,048 , filed on January 6, 1954, and granted on August 23, 1960.

I found this rather nice recursive way to do it in SQL Server 2005

CREATE FUNCTION dbo.Modulus10 (@num INT)
RETURNS INT
AS
BEGIN
DECLARE @result INT;
DECLARE @snum VARCHAR(100);
SET @snum = CAST(@num AS VARCHAR (100)); -- converts num to a string value
WITH Partials(n, odd, even)
AS
(
SELECT LEN(@snum)+2 AS n, -- Ignores the first row by starting at offset + 2 to make the code more readable
0 AS odd,
0 AS even
UNION ALL
SELECT n - 2, -- "loop" counter
(CAST(COALESCE(SUBSTRING(@snum, n - 2 , 1),'0') AS INT) -- Extracting the n'th odd digit
* 2 / CAST (10 as INT))
+
(CAST(COALESCE(SUBSTRING(@snum, n - 2, 1),'0') AS INT) -- Extracting the n'th odd digit
* 2 % 10) AS odd,
CAST (COALESCE (SUBSTRING(@snum, n - 3, 1), '0') AS INT) AS even -- Extracting the n'th even digit
FROM Partials
WHERE n >= 1)
SELECT @result =(CASE (sum(odd + even) % 10)
WHEN 0 THEN (sum(odd + even) % 10) ELSE 10 -(sum(odd + even) % 10)
END)
FROM Partials;
RETURN(@result)
END




Listen to Boheme Radio!

Delphi programming RuleZ

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" ...