Subscribe: Code.I.net
http://codeinet.blogspot.com/atom.xml
Language: English
Tags:
auxiliary  calendar  date  int int  int null  int  null  numbers  select  server  set set  set  sql server  sql  year
Rate this Feed

Feed Details and Statistics
Preview: Code.I.net

# Code.I.net

## Codeine is a drug which is used to relieve pain, especially headaches. .Net is a (Micro)soft drug to induce pain, especially headaches. Delphi programming

Updated: 2017-12-11T04:07:12.005+01:00

The Luhn algorithm using a recursive CTE

2008-10-10T00:01:22.261+02:00

The 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[...]

Audit table data changes in SQL Server 2005

2006-09-04T12:30:02.023+02:00

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 ENDThats it, you got the previous verisons of the changed rows stored as XML. [...]

SQLMan

2006-08-29T19:15:58.670+02:00

(image)

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!

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

The dreaded ID or even worse IDENTITY

2006-08-26T02:57:36.496+02:00

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

2006-08-07T19:37:50.956+02:00

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 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 ;-))[...]

The Auxiliary Calendar Table For SQL Server 2005

2007-09-18T15:00:57.705+02:00

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]))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[...]

Computus - Easter Sunday in SQL Server 2005

2006-08-02T18:32:09.926+02:00

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

Auxiliary roundup - The SQL table valued for loop function?

2006-07-22T10:29:10.370+02:00

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 [...]

SQL Numbers table using a Common table expression (CTE)

2006-07-15T10:10:41.666+02:00

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!

2006-06-29T18:49:17.696+02:00

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