2008-10-10T00:01:22.261+02:00The other day I needed to implement the modulus 10 function in SQL.From WikipediaThe 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 2005CREATE FUNCTION dbo.Modulus10 (@num INT)RETURNS INTASBEGINDECLARE @result INT;DECLARE @snum VARCHAR(100);SET @snum = CAST(@num AS VARCHAR (100)); -- converts num to a string valueWITH 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 evenUNION ALLSELECT 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 digitFROM PartialsWHERE 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)ENDListen to Boheme Radio!Delphi programming RuleZ[...]
2006-09-04T12:30:02.023+02:00Some 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 ENDThats it, you got the previous verisons of the changed rows stored as XML. [...]
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 ;-)
2006-08-07T19:37:50.956+02:00While 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 F5DECLARE @Delimitedtext varchar(max);DECLARE @Delimiter char(1);SET @Delimitedtext = 'aaa,bbbb,cccc,dddd,';SET @Delimiter = ',';WITH Strings(s, r)AS(SELECTSUBSTRING(@Delimitedtext,1, CHARINDEX(@Delimiter, @Delimitedtext)-1) s,SUBSTRING(@Delimitedtext,CHARINDEX(@Delimiter, @Delimitedtext)+1, len(@Delimitedtext)) rUNION ALLSELECTSUBSTRING(r,1, CHARINDEX(@Delimiter, r)-1) s,SUBSTRING(r,CHARINDEX(@Delimiter, r)+1, len(r)) rFROM StringsWHERECHARINDEX(@Delimiter, r) > 0)SELECT s FROM StringsThe query will yield this result:s-------------------aaabbbbccccdddd(4 row(s) affected)Have fun Querying (SQL is not defined as a programming language, so you really can't have fun programming SQL ;-))[...]
2007-09-18T15:00:57.705+02:00A 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]))GOALTER 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')))GOSET DATEFIRST 1;-- I want my table to contain datedata acording to ISO 8601-- thus first day of a week is mondayWITH Dates(Date)-- A recursive CTE that produce all dates between 1999 and 2020-12-31AS(SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01UNION ALL SELECT (Date + 1) AS DateFROM DatesWHEREDate < 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 dateAS(SELECTDate,CASE DATEPART(weekday,Date)WHEN 1 THEN Date + 3WHEN 2 THEN Date + 2WHEN 3 THEN Date + 1WHEN 4 THEN DateWHEN 5 THEN Date - 1WHEN 6 THEN Date - 2WHEN 7 THEN Date - 3END AS ThursdayFROM Dates),Weeks(Week, Thursday)-- Now we produce the weeknumers for the thursdays-- ROW_NUMBER is new to SQL Server 2005AS(SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, ThursdayFROM DatesAndThursdayInWeekWHERE DATEPART(weekday,Date) = 4)INSERT INTO Auxiliary.CalendarSELECTd.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 sameYEAR(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 hereWHEN (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(YEA[...]
2006-08-02T18:32:09.926+02:00Working 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 DATETIMEASBEGINDECLARE@a INT,@b INT,@c INT,@d INT,@e INT,@f INT,@g INT,@h INT,@i INT,@k INT,@L INT,@m INTSET @a = @Y % 19SET @b = @Y / 100SET @c = @Y % 100SET @d = @b / 4SET @e = @b % 4SET @f = (@b + 8) / 25SET @g = (@b - @f + 1) / 3SET @h = (19 * @a + @b - @d - @g + 15) % 30SET @i = @c / 4SET @k = @c % 4SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7SET @m = (@a + 11 * @h + 22 * @L) / 451RETURN(DATEADD(month, ((@h + @L - 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L - 7 * @m + 114) % 31))ENDI 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" ...[...]
2006-07-22T10:29:10.370+02:00After 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 [...]
2006-07-15T10:10:41.666+02:00With 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).
2006-06-29T18:49:17.696+02:00Really 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 ...