As we near the end of the database design process, the database is pretty much completed from a design standpoint. We have spent time looking at performance, concurrency and security patterns that you can follow to help implement the database in a manner that will work well under most any typical OLTP style load. In this chapter (and again somewhat in the next two), we are going to look at applying “finishing touches” to the database that can be used to enhance the user experience and assist with the querying and maintaining of the database. We won’t flesh out all of the details for every concept in this chapter, because a lot of the examples include parts of the system that are decidedly more aligned to the DBA than the architect/programmer (or are just far too large to fit in this book). However, the point I will be making make here is that our goal will be end up with a self contained database container with as much of the database coding and maintenance functionality as possible. In Chapter 9, we introduced the concept of a contained database to help maintain a secure and portable database, and here I will present some additional add-in capabilities and expansion point ideas to add to make your database that much more usable.

The reality of database design is that most databases are rarely cookie-cutter affairs. Most companies, even when they buy a third-party package to implement some part of their business, are going to end up making (in many cases substantial) customizations to the database to fit their needs. If you are starting a very large project, you may even want to look at previous models or perhaps pre-built “universal data models,” such as those in Len Silverston’s series of books, the first of which is The Data Model Resource Book: A Library of Universal Data Models for All Enterprises (Wiley, 2001) (perhaps the only book on database design with a larger title that the book you hold in your hands right now.) Karen Lopez (@datachick on Twitter) frequently speaks on the subject of universal models in the SQL PASS universe of presenters that I am generally involved with. Even these universal models may only be useful as starting points to help you map from your “reality” to a common view of a given sort of business.

In this chapter, however, I want to explore the parts of the database that I find to be useful and almost always the same for every database I create. Not every database will contain all of what I will present, but when I need a common feature I will use the exact same code in every database, with the obvious caveat that I am constantly looking for new ways to improve almost everything I use over time (not to mention it gives me something to write about when I run out of Lego sets to build). Hence, sometimes a database may use an older version of a feature until it can be upgraded. I will cover the following topics:

• Numbers table : A table of numbers, usually integers that can be used for a number of interesting uses (not many of the mathematics-related).
• Calendar table : A table where every row represents a day, assisting in grouping data for queries, both for reports and operational usage.
• Utility objects : Every programmer has code that they use to make their job easier. Utilities to monitor usage of the system; extended DDL to support operations that aren’t part of the base DDL in T-SQL.
• Logging objects : Utilities to log the actions of users in the database, generally for system management reasons. A common use is an error log to capture when and where errors are occurring.
• Other possibilities: In this section, I will present a list of additional ideas for ways to extend your databases in ways that will give you independent databases that have common implementation.

Not every database can look alike, even two that do almost the exact same thing will rarely be all that alike unless the designer is the same, but following the patterns of implementation we have discussed all throughout the book thus far and the practices we will discuss in this chapter, we can produce databases that are similar enough such that the people supporting your work will have it easy figuring out what you had in mind.

If you are dealing with a third party system where it is forbidden to add any of your own objects, even in a schema that is separated from the shipped schemas, don’t think that everything I am saying here doesn’t apply to you. All of the example code presented supposes a single database approach. In such cases a common approach is to create a companion database where you locate code you need to access their code from the database tier. Some examples would need to be slightly reworked for that model, but that rework would be minimal.

Numbers Table

A numbers table is a precalculated table of numbers, primarily non-negative integers, which you can use for some purpose. The name “numbers” is pretty open ended, but getting so specific as nonNegativeIntegers is going to get you ridiculed by the other programmers on the playground. In previous editions of the book I have used the name sequence, but with the addition of the sequence object, the name “numbers” was the next best thing. We will use the numbers table when we need to work with data in an ordered manner, particularly a given sequence of numbers. For example, if you needed a list of the top ten products sold and you only sold six, you would have to somehow manufacture four more rows for display. Having a table where you can easily output a sequence of numbers is going to be a very valuable asset at times indeed.

While you can make the numbers table contain any type of numbers you may need, usually it is just a simple table of non-negative integers from 0 to some reasonable limit, where reasonable is more or less how many you find you need. I generally load mine by default up to 99999 (99999 gives you full five digits (and is a very convenient number for the query I will use to load the table.) With the algorithm I will present, you can easily expand to create a sequence of numbers that is larger than you can store in SQL Server.

There are two really beautiful things behind this concept. First, the table of non-negative integers has some great uses dealing with text data, as well as doing all sorts of math with. Second, you can create additional attributes or even other sequence tables that you can use to represent other sets of numbers that you find useful or interesting. For example:

• Even or odd, prime, squares, cubes, and so on
• Other ranges or even other grains of values, for example, (-1, -.5, 0, .5, 1)
• Letters of the alphabet

In the examples in this section, we will look at several techniques you may find useful, and possibly quite often. The code to generate a simple numbers table of integers is pretty simple; though it looks a bit daunting the first time you see it. It is quite fast to execute in this form, but no matter how fast it may seem, it is not going to be faster than querying from a table that has the sequence of numbers precalculated and stored ahead of time.

 ;WITH digits (I) AS

     (--set up a set of numbers from 0-9

      SELECT I

      FROM (VALUES (0),(1),(2),(3),(4),

      (5),(6),(7),(8),(9)) AS digits (I))

 ,integers (I) AS (

    SELECT D1.I + (10*D2.I) + (100*D3.I) + (1000*D4.I)

      -- + (10000*D5.I) + (100000*D6.I)

    FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3

    CROSS JOIN digits AS D4

      --CROSS JOIN digits AS D5 CROSS JOIN digits AS D6

 )

 SELECT I

 FROM integers

 ORDER BY I;

This code will return a set of 10,000 rows, as follows:

Uncommenting the code for the D5 and D6 tables will give you an order of magnitude increase for each, up to 999,999 rows. The code itself is pretty interesting (this isn’t a coding book, but it is a really useful technique). Breaking the code down, you get the following:

 ;WITH digits (I) AS

    (--set up a set of numbers from 0-9

      SELECT I

      (VALUES (0),(1),(2),(3),(4),

      (5),(6),(7),(8),(9)) AS digits (I))

This is just simply a set of ten rows from 0 to 9. The next bit is where the true brilliance begins. (No, I am not claiming I came up with this. I first saw it on Erland Sommarskog’s web site a long time ago, using a technique I will show you in a few pages to split a comma-delimited string.) You cross-join the first set over and over, multiplying each level by a greater power of 10. The result is that you get one permutation for each number. For example, since 0 is in each set, you get one permutation that results in 0. You can see this better in the following smallish set:

 ;WITH digits (I) AS (--set up a set of numbers from 0-9

     SELECT i

     FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS digits (I))

 SELECT D1.I AS D1I, 10*D2.I AS D2I, D1.I + (10*D2.I) AS [Sum]

 FROM digits AS D1 CROSS JOIN digits AS D2

 ORDER BY [Sum];

This returns the following, and you can see that by multiplying the D2.I value by 10, you get the ten’s place repeated, giving you a very powerful mechanism for building a large set. In the full query, each of the additional digit table references have another power of ten in the SELECT clause multiplier , allowing you to create a very large set (rows removed and replaced with . . . for clarity and to save a tree):

This kind of combination of sets is a very useful technique in relational coding. As I said earlier, this isn’t a query book, but I feel it necessary to show you the basics of why this code works, because it is a very good mental exercise. Using the full query, you can create a sequence of numbers that you can use in a query.

So, initially create a simple table named Number with a single column I (because it is a typical value used in math to denote an index in a sequence, such as x_,, where the I denotes a sequence of values of x. The primary purpose of the Numbers is to introduce an ordering to a set to assist in an operation.). I will create this table in a schema named Tools to contain the types of tool objects, functions, and procedures we will build in this chapter. In all likelihood, this is a schema you would grant EXECUTE and SELECT to public and make the tools available to any user you have given ad hoc query access to.

 USE AdventureWorks2012;

 GO

 CREATE SCHEMA Tools;

 GO

 CREATE TABLE Tools.Number

 (

    I int NOT NULL CONSTRAINT PKTools_Number PRIMARY KEY

 );

Then I will load it with integers from 0 to 99999.

 ;WITH digits (I) AS (--set up a set of numbers from 0-9

    SELECT I

    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS digits (I))

 --builds a table from 0 to 99999

 ,Integers (I) AS (

    SELECT D1.I + (10*D2.I) + (100*D3.I) + (1000*D4.I) + (10000*D5.I)

    --+ (100000*D6.I)

      FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3

     CROSS JOIN digits AS D4 CROSS JOIN digits AS D5

     /* CROSS JOIN digits AS D6 */)

 INSERT INTO Tools.Number(I)

 SELECT I

 FROM   Integers;

So if you wanted to count the integers between 1 and 1000 (inclusive), it is as simple as:

 SELECT COUNT(*)

 FROM   Tools.Number

 WHERE  I between 1 and 1000;

Of course, that would be a bit simplistic, and there had better be 1000 values between (inclusive) 1 and 1000, but what if you wanted the number of integers between 1 and 1000 that are divisible by 9 or 7?

 SELECT COUNT(*)

 FROM   Tools.Number

 WHERE  I BETWEEN 1 AND 1000

     AND (I % 9 = 0 OR I % 7 = 0);

This returns the obvious answer: 238. Sure, a math nerd could sit down and write a formula to do this, but why? And if you find you need these values quite often, you could create a table called Tools.DivisibleByNineAndSevenNumber , or add columns to the number table called DivisibleByNineFlag and DivisibleBySevenFlag if it were needed in context of integers that were not divisible by 9 or 7. The simple numbers table is the most typical need, but you can make a table of any sorts of numbers that you need (prime numbers? squares? cubes?). The last example of this chapter is an esoteric example of what you can do with a table of numbers to do some pretty (nerdy) fun stuff with a table of numbers, but for OLTP use, the goal will be (as we discussed in Chapter 5 on normalization) to pre-calculate values only when they are used often and can never change. Numbers-type tables are an excellent candidate for storing pre-calculated values because the set of integer numbers and prime numbers are the same now as back in 300 BC when Euclid was working with them.

In this section, I will present the following uses of the numbers table to get you going:

• Determining the contents of a string : Looking through a string without looping by using the ordered nature of the numbers table to manage the iteration using relational code.
• Determining gaps in a sequence : Having a set of data that contains all of the values allows you to use relational subtraction to find missing values.
• Separating comma-delimited items : Sometimes data is not broken down into scalar values like you desire.
• Stupid mathematic tricks: I take the numbers table to abstract levels, solving a fairly complex math problem that, while not terribly applicable in a practical manner, serves as an experiment to build upon if you have similar, complex problem-solving needs.

Calendar Table

It is a common task for a person to want to know how to do groupings and calculations with date values. For example, you might want sales grouped by month, week, year, or any other grouping. You can usually do this using the SQL Server date functions, but often it is costly in performance, and it is always a fairly non-obvious operation. What can truly help with this process is to use a table filled with date values, commonly called a calendar table. Using a calendar table is commonplace in Business Intelligence/OLAP implementations (something that is covered more in Chapter 14), but it certainly can be useful in OLTP databases when you get stuck doing a confusing date range query.

Using the same form of precalculated logic that we applied to the numbers table, we can create a table that contains one row per date. I will set the date as the primary key and then have data related to the date as columns. The following is the basic date table that I currently use. You can extend it as you want to include working days, holidays, special events, and so on, to filter/group by in the same manner as you do with these columns, along with the others I will add later in the section (again, I am working in a copy of AdventureWorks2012, bolting on my tools to make life easier).

 CREATE TABLE Tools.Calendar

 (

    DateValue date NOT NULL CONSTRAINT PKtools_calendar PRIMARY KEY,

    DayName varchar(10) NOT NULL,

    MonthName varchar(10) NOT NULL,

    Year varchar(60) NOT NULL,

    Day tinyint NOT NULL,

    DayOfTheYear smallint NOT NULL,

    Month smallint NOT NULL,

    Quarter tinyint NOT NULL

 );

The question of normalization might come up again at this point, and it is a valid question. Since these values we have created can be calculated (and quite easily in most case), why do this? Isn’t this denormalization? There are a few ways to look at it, but I would generally say that it isn’t. Each row represents a day in time, and each of the columns is functionally dependent on the date value. The fact is, the functions have to look up or calculate the date attributes in some manner, so you are actually saving that lookup cost, however minimal it might be.) When you have to filter on date criteria, the benefit of the normalized design can be quite obvious. Later in the section we will extend the table to add more interesting, company-based values, which certainly are normalized in the context of a date.

Note that the name calendar is not truly consistent with our names representing a single row, at least not in a natural way, but the other names that we might use (date, dateValue, etc.) all sound either forced or hokey. Since the table represents a calendar and a calendar item generally would logically represent a day, I went with it. A stretch perhaps but naming is pretty difficult at times, especially when working in a namespace that the real world and SQL Server have so many established names.

The next step is to load the table with values, which is pretty much a straightforward task using the Numbers table that we just finished creating in the previous section. Using the datename and datepart functions and a few simple case expressions, you load the different values. I will make use of many of the functions in the examples, but most are very easy to understand.

 WITH dates (newDateValue) AS (

    SELECT DATEADD(day,I,'17530101') AS newDateValue

    FROM Tools.Number

 )

 INSERT Tools.Calendar

    (DateValue ,DayName

    ,MonthName ,Year ,Day

    ,DayOfTheYear ,Month ,Quarter

 )

 SELECT

    dates.newDateValue as DateValue,

    DATENAME (dw,dates.newDateValue) As DayName,

    DATENAME (mm,dates.newDateValue) AS MonthName,

    DATENAME (yy,dates.newDateValue) AS Year,

    DATEPART(day,dates.newDateValue) AS Day,

    DATEPART(dy,dates.newDateValue) AS DayOfTheYear,

    DATEPART(m,dates.newDateValue) AS Month,

    DATEPART(qq,dates.newDateValue) AS Quarter

 FROM dates

 WHERE dates.newDateValue BETWEEN '20000101' AND '20130101' --set the date range

 ORDER BY DateValue;

Just like the numbers table, there are several commonly useful ways to use the calendar table. The first I will demonstrate is general grouping types of queries, and the second is calculating ranges. As an example of grouping, say you want to know how many sales had been made during each year in Sales.SalesOrderHeader in the AdventureWorks2012 database. This is why there is a year column in the table:

 SELECT Calendar.Year, COUNT(*) as OrderCount

 FROM /*Adventureworks2012.*/ Sales.SalesOrderHeader

    JOIN Tools.Calendar

    --note, the cast here could be a real performance killer

    --consider using date columns where possible

    ON CAST(SalesOrderHeader.OrderDate as date) = Calendar.DateValue

 GROUP BY Calendar.Year

 ORDER BY Calendar.Year;

This returns the following:

The beauty of the calendar table is that you can easily group values that are not that simple to compute in a really obvious manner them, all while using natural relational coding style/technique. For example: to count the sales on Tuesdays and Thursdays?

 SELECT Calendar.DayName, COUNT(*) as OrderCount

 FROM /*Adventureworks2012.*/ Sales.SalesOrderHeader

    JOIN Tools.Calendar

          --note, the cast here could be a real performance killer

          --consider using date columns where

    ON CAST(SalesOrderHeader.OrderDate as date) = Calendar.DateValue

 WHERE Calendar.DayName in ('Tuesday','Thursday')

 GROUP BY Calendar.DayName

 ORDER BY Calendar.DayName;

This returns the following:

OK, I see you are possibly still skeptical. What if I throw in the first Tuesday after the second Wednesday? How? Well, it is really a simple matter of building the set step by step using CTEs to build the set that you need.

 ;WITH onlyWednesdays AS --get all Wednesdays

 (

    SELECT *,

      ROW_NUMBER() OVER (PARTITION BY Calendar.Year, Calendar.Month

              ORDER BY Calendar.Day) AS wedRowNbr

    FROM Tools.Calendar

    WHERE DayName = 'Wednesday'

 ),

 secondWednesdays AS --limit to second Wednesdays of the month

 (

    SELECT *

    FROM onlyWednesdays

    WHERE wedRowNbr = 2

 )

 ,finallyTuesdays AS --finally limit to the Tuesdays after the second wed

 (

    SELECT Calendar.*,

    ROW_NUMBER() OVER (PARTITION BY Calendar.Year, Calendar.Month

           ORDER by Calendar.Day) AS rowNbr

    FROM secondWednesdays

        JOIN Tools.Calendar

        ON secondWednesdays.Year = Calendar.Year

        AND secondWednesdays.Month = Calendar.Month

    WHERE Calendar.DayName = 'Tuesday'

    AND Calendar.Day > secondWednesdays.Day

 )

 --and in the final query, just get the one month

 SELECT Year, MonthName, Day

 FROM finallyTuesdays

 WHERE Year = 2012

     AND rowNbr = 1;

This returns the following set of values that appear to be the result of some advanced calculations, but rather are the results of a fairly simple query (to anyone in Chapter 12 of this book, for sure!):

Now, utilizing another CTE, you could use this to join to the Sales.SalesOrderHeader table and find out sales on the first Tuesday after the second Wednesday. And that is exactly what I would do if I was being fed requirements by a madman (or perhaps they prefer the term is marketing analyst?) and would be done in no time. However, if this date became a corporate standard desired date, , I would add a column to the calendar table (maybe called FirstTuesdayAfterSecondWednesdayFlag) and set it to 1 for every date that it matches (and in the one month where that day was a holiday and they wanted it to be the Thursday after the second Wednesday after…well, the change would be a simple update.

The realistic application of this is a company sale that they routinely start on a given day every month, so the report needs to know how sales were for four days after. So, perhaps the column would be BigSaleDaysFlag , or whatever works. The goal is the same as back in Chapter 7 when we talked about data driven design. Store data in columns; rules in code. A new type of day is just a new column and calculation that every user now has access to and doesn’t have to know the calculation that was used to create the value.

In most standard calendar table utilizations, it will be common to have, at a minimum, the following generic events/time ranges:

    --Saturday or Sunday set to 1, else 0

    WeekendFlag bit NOT NULL,

    FiscalYear smallint NOT NULL,

    FiscalMonth tinyint NULL,

    FiscalQuarter tinyint NOT NULL

Almost every company has a fiscal year that it uses for its business calendar. This technique allows you to treat the fiscal time periods more or less like regular calendar dates in your code, with no modification at all. I will demonstrate this after we load the data in just a few paragraphs.

This covers the basics; now I’ll discuss one last thing you can add to the calendar table to solve the problem that really shifts this into “must-have” territory: floating windows of time.

The goal for the floating windows of time was to add a relative positioning value to the table so that you could easily get N time periods from a point in time. Years are already contiguous, increasing numbers, so it is easy to do math with them. But it is not particularly comfortable to do math with months. I found myself often having to get the past 12 months of activity, sometimes including the current month and sometimes not. Doing math that wraps around a 12-month calendar was a pain, so to the calendar, I add the following columns that I will load with increasing values with no gaps:

 RelativeDayCount int NOT NULL,

 RelativeWeekCount int NOT NULL,

 RelativeMonthCount int NOT NULL

Using these columns I will store sequence numbers that start at an arbitrary point in time. I will use '20000101' here, but it is really unimportant, and negative values are not a problem either. You should never refer to the value itself, just the value’s relative position to some point in time you choose. And days are numbered from that point (negative before, positive before), months, and again weeks. This returns the following “final” calendar table:

 DROP TABLE Tools.Calendar

 GO

 CREATE TABLE Tools.Calendar

 (

      DateValue date NOT NULL CONSTRAINT PKtools_calendar PRIMARY KEY,

      DayName varchar(10) NOT NULL,

      MonthName varchar(10) NOT NULL,

      Year varchar(60) NOT NULL,

      Day tinyint NOT NULL,

      DayOfTheYear smallint NOT NULL,

      Month smallint NOT NULL,

      Quarter tinyint NOT NULL,

      WeekendFlag bit NOT NULL,

      --start of fiscal year configurable in the load process, currently

      --only supports fiscal months that match the calendar months.

      FiscalYear smallint NOT NULL,

      FiscalMonth tinyint NULL,

      FiscalQuarter tinyint NOT NULL,

      --used to give relative positioning, such as the previous 10 months

      --which can be annoying due to month boundaries

      RelativeDayCount int NOT NULL,

      RelativeWeekCount int NOT NULL,

      RelativeMonthCount int NOT NULL

 )

Last, I will reload the table with the following code:

 ;WITH dates (newDateValue) AS (

      SELECT DATEADD(day,I,'17530101') AS newDateValue

      FROM Tools.Number

 )

 INSERT Tools.Calendar

      (DateValue ,DayName

      ,MonthName ,Year ,Day

      ,DayOfTheYear ,Month ,Quarter

      ,WeekendFlag ,FiscalYear ,FiscalMonth

      ,FiscalQuarter ,RelativeDayCount,RelativeWeekCount

      ,RelativeMonthCount)

 SELECT

      dates.newDateValue AS DateValue,

      DATENAME (dw,dates.newDateValue) AS DayName,

      DATENAME (mm,dates.newDateValue) AS MonthName,

      DATENAME (yy,dates.newDateValue) AS Year,

      DATEPART(day,dates.newDateValue) AS Day,

      DATEPART(dy,dates.newDateValue) AS DayOfTheYear,

      DATEPART(m,dates.newDateValue) AS Month,

      CASE

        WHEN MONTH( dates.newDateValue) <= 3 THEN 1

        WHEN MONTH( dates.newDateValue) <= 6 THEN 2

        When MONTH( dates.newDateValue) <= 9 THEN 3

      ELSE 4 END AS quarter,

      CASE WHEN DATENAME (dw,dates.newDateValue) IN ('Saturday','Sunday')

        THEN 1

        ELSE 0

      END AS weekendFlag,

      ------------------------------------------------

      --the next three blocks assume a fiscal year starting in July.

      --change if your fiscal periods are different

      ------------------------------------------------

      CASE

        WHEN MONTH(dates.newDateValue) <= 6

        THEN YEAR(dates.newDateValue)

        ELSE YEAR (dates.newDateValue) + 1

      END AS fiscalYear,

      CASE

        WHEN MONTH(dates.newDateValue) <= 6

        THEN MONTH(dates.newDateValue) + 6

        ELSE MONTH(dates.newDateValue) - 6

      END AS fiscalMonth,

      CASE

        WHEN MONTH(dates.newDateValue) <= 3 then 3

        WHEN MONTH(dates.newDateValue) <= 6 then 4

        WHEN MONTH(dates.newDateValue) <= 9 then 1

      ELSE 2 END AS fiscalQuarter,

      ------------------------------------------------

      --end of fiscal quarter = july

      ------------------------------------------------

      --these values can be anything, as long as they

      --provide contiguous values on year, month, and week boundaries

      DATEDIFF(day,'20000101',dates.newDateValue) AS RelativeDayCount,

      DATEDIFF(week,'20000101',dates.newDateValue) AS RelativeWeekCount,

      DATEDIFF(month,'20000101',dates.newDateValue) AS RelativeMonthCount

 FROM dates

 WHERE dates.newDateValue BETWEEN '20000101' AND '20130101'; --set the date range

Now we can build a query to get only weekends, grouped by fiscalYear as follows:

 SELECT Calendar.FiscalYear, COUNT(*) AS OrderCount

 FROM /*Adventureworks2012.*/ Sales.SalesOrderHeader

      JOIN Tools.Calendar

        --note, the cast here could be a real performance killer

        --consider using a persisted calculated column here

        ON CAST(SalesOrderHeader.OrderDate as date) = Calendar.DateValue

 WHERE WeekendFlag = 1

 GROUP BY Calendar.FiscalYear

 ORDER BY Calendar.FiscalYear;

This returns the following:

To demonstrate the floating windows of time using the Relative_____Count columns, consider that you want to count the sales for the previous two weeks. It’s not impossible to do this using the date functions perhaps, but it’s simple to do with a calendar table:

 DECLARE @interestingDate date = '20120509';

 SELECT Calendar.DateValue as PreviousTwoWeeks, CurrentDate.DateValue AS Today,

      Calendar.RelativeWeekCount

 FROM Tools.Calendar

      JOIN (SELECT *

          FROM Tools.Calendar

          WHERE DateValue = @interestingDate) AS CurrentDate

      ON Calendar.RelativeWeekCount < (CurrentDate.RelativeWeekCount)

        and Calendar.RelativeWeekCount >=

          (CurrentDate.RelativeWeekCount -2);

This returns the following:

From this, we can see that the previous two weeks start on Sunday (04/22/2012 and 04/29/2012) and end on Saturday (04/28/2012 and 05/05/2012). The dates 05/06/2012 to 05/09/2012 are not included because that is this week (relative to the variable value). The basics of the query is simply to take a derived table that fetches the calendar row for the “interesting” date and then join that to the full calendar table using a range of dates in the join. In the previous week’s example, I used the following:

 Calendar.RelativeWeekCount < (CurrentDate.RelativeWeekCount)

      AND Calendar.RelativeWeekCount >= (CurrentDate.RelativeWeekCount -2)

This was because I wanted the weeks that are previous to the current week and weeks two weeks back. Weeks aren’t the sweet spot of this technique exactly, because weeks are of fixed length (but they are easier to get a full result set in print, since I don’t get paid by the page). Now, join the values to your sales table, and you can see sales for the past two weeks. Want to see it broken down by week? Use relative week count. Use the following if you wanted the previous 12 months:

 DECLARE @interestingDate date = '20120509'

 SELECT MIN(Calendar.DateValue) AS MinDate, MAX(Calendar.DateValue) AS MaxDate

 FROM Tools.Calendar

     JOIN (SELECT *

       FROM Tools.Calendar

       WHERE DateValue = @interestingDate) as CurrentDate

   ON Calendar.RelativeMonthCount < (CurrentDate.RelativeMonthCount)

     AND Calendar.RelativeMonthCount >=

       (CurrentDate.RelativeMonthCount -12);

This returns the following:

This query includes all earlier months, but not the one loaded into @interestingDate. Notice that the results did not include the current month. Change the < to <=, and it will include the current month. If you want to get a 24-month window, get the 12 months plus or minus the current month, as follows:

 ON Calendar.RelativeMonthCount < (CurrentDate.RelativeMonthCount + 12)

   AND Calendar.RelativeMonthCount >=

     (CurrentDate.RelativeMonthCount -12)

Using that ON clause results in the following:

Now you can use these dates in other criteria, either by assigning these values to a variable or (if you are one of the cool kids) by using the tables in a join to other tables. As a real example, let’s hop in the Wabac machine and look at sales in the Adventureworks2012 database around September 27, 2008. (Keep in mind that the sample databases could change in the future to get more up-to-date data, but the 2012 RTM version had dates that were the same as the 2008 and the 2008 R2 editions of AdventureWorks.)

 DECLARE @interestingDate date = '20080927';

 SELECT Calendar.Year, Calendar.Month, COUNT(*) AS OrderCount

 FROM /*Adventureworks2012.*/ Sales.SalesOrderHeader

     JOIN Tools.Calendar

      JOIN (SELECT *

        FROM Tools.Calendar

        WHERE DateValue = @interestingDate) as CurrentDate

          ON Calendar.RelativeMonthCount <=

          (CurrentDate.RelativeMonthCount )

        AND Calendar.RelativeMonthCount >=

          (CurrentDate.RelativeMonthCount -10)

     ON cast(SalesOrderHeader.ShipDate as date)= Calendar.DateValue

 GROUP BY Calendar.Year, Calendar.Month

 ORDER BY Calendar.Year, Calendar.Month;

This query will give you items that shipped in the previous ten months and in August, and group them by month, as follows:

I included the current month by changing the first condition to <=. So, you should be able to see that the calendar table and sequence table are two excellent tables to add to almost any database. They give you the ability to take a functional problem like getting the last day of the month or the previous time periods and turn it into a relational question that SQL Server can chew up and spit out using the relational techniques it is built for.

Keep in mind too that creating more fiscal calendars, reporting calendars, corporate holiday calendars, sales calendars, and so forth, is easily done by adding more columns to this very same calendar table. For instance, you may have multiple business units, each with their own fiscal calendar. With the one normal calendar, you can add any variation of the fiscal calendar multiple times. It’s easy to just add those columns, since they all relate back to a normal calendar date.

One last thing to note is that the calendar table needs to be maintained. The insert statement we wrote earlier can be changed slightly so that it can be run to extend the number of rows as time passes. Perhaps an Agent job ran on the first day of the year to add another set of rows, or I have also used a process to keep the calendar just a few weeks in the future for a payroll system. The rules changed on occasion, so the new rows were added just a few weeks in advance based on whatever rules were then in effect.

Utility Objects

As you acquire more and more experience over the years (which is loosely synonymous with the many failures you will encounter that teach you lessons), you will undoubtedly end up with a toolbox full of various tools that you find you use quite frequently. Some of these tools will be used in an ad-hoc manner when you are dealing with the same problems over and over again. I personally like to simply keep a set of files/projects stored on my dropbox/skydrive folders for easy accessibility when I am working, writing, or just poking around at SQL Server to see what I can see at the office, home, or even at a client site.

Beyond the type of utilities that are used infrequently, the toolbox will contain a rather large set of tools that become part of the regular processing of the system. For these types of tools the logistics of their use can pose a problem. How do you make sure that they are usable by every database, as needed, and allow for the differences that will occur in every system (and keep your databases portable so that you can move them to a new server with the least amount of pain? In the old days of the twentieth century, we would make a system object in the master database that was prefixed with sp_ (and marked it as a system object) and diligently maintain all servers as equal as possible While this is still possible, we found that too often if we wanted to include a change to the object, we couldn’t do it because with N systems using an object, we usually found N different ways an object was used. Then you would have abnormal procedures spread around the enterprise with names that included a database that might be on a single server (and over time, didn’t even exist anymore). As time passed, we stopped using master and started adding a utility database which was managed on every server. Ideally they were identical, but over time, the DBA and or Developers used the database for “temp” usages that again becomes unwieldy and decidedly not temporary.

What seems to be the best answer is to create a schema on each database that contains the tools that that database actually uses. By putting the objects in each database individually, it is easier to decide whether additions to an object should be a new object that is specific to the single database or if it was simply a new version of an existing object. If it is just a new version, the systems that were using an object would take the new change as an upgrade after due testing. In either case, each database becomes more stand-alone so that moving from server A to server B means a lot less preparation on server B trying to meet the needs of server B. A few SQL Agent jobs may need to be created to execute.

For examples, I chose a few of the types of utilities that I find useful in my databases I design. I will present one example in each, though this is only just a start. We will look at solutions in:

• Monitoring tools : Tools to watch what is going on with the database such as row counts, file sizes, and so forth.
• Extended DDL utilities : Tools used to make changes to the structure of the database, usually to remove keys or indexes for load processes, usually to do multiple DDL calls where SQL Server’s DDL would require multiple calls.

Logging Objects

In many systems, you will find you need to watch the activities that are occurring in the database. Back in Chapter 7 we implemented an audit trail using triggers, and we chose between using the source table schema or some form of utility schema. In this section, the types of logging we will want to do are a more generic form of logging that is more for the DBAs to see what errors have been occurring.

As an example, one thing that we often may want to log is errors. A common goal these days is to make sure that no errors occur at the database level. Logging any errors that occur to a table can help to see where you have recurring issues. Probably the most interesting way this has ever helped me in my systems was once when a programmer had simply ignored all return values from SQL calls. So a large number of calls to the system were failing, but the client never realized it. (In Appendix B, I will employ this functionality in the trigger templates that I provide).

The Utility.ErrorLog$insert procedure is used to log the errors that occur in a table, to give you a history of errors that have occurred. I do this because, in almost every case, an error that occurs in a trigger is a bad thing. The fact that the client sends data that might cause the trigger to fail should be fixed and treated as a bug. In stored procedures, this may or may not be the case, as stored procedures can be written to do things that may work, or may fail in some situations. This is a very broad statement, and in some cases may not be true, so you can adjust the code as fits your desires.

The DML for the table is as follows:

 CREATE TABLE Utility.ErrorLog(

   ErrorLogId int NOT NULL IDENTITY CONSTRAINT PKErrorLog PRIMARY KEY,

       Number int NOT NULL,

   Location sysname NOT NULL,

   Message varchar(4000) NOT NULL,

   LogTime datetime2(3) NULL

     CONSTRAINT DFLTErrorLog_error_date DEFAULT (SYSDATETIME()),

   ServerPrincipal sysname NOT NULL

     --use original_login to capture the user name of the actual user

     --not a user they have impersonated

       CONSTRAINT DFLTErrorLog_error_user_name DEFAULT (ORIGINAL_LOGIN())

 );

Then we create the following procedure, which can be coded into other procedures and trigger whenever you need to log that an error occurred:

 CREATE PROCEDURE Utility.ErrorLog$Insert

 (

   @ERROR_NUMBER int,

   @ERROR_LOCATION sysname,

   @ERROR_MESSAGE varchar(4000)

 ) AS

 -- ----------------------------------------------------------------

 -- Writes a row to the error log. If an error occurs in the call (such as a NULL value)

 -- It writes a row to the error table. If that call fails an error will be returned

 --

 -- 2012 Louis Davidson – [email protected] &#x2013; drsql.org

 -- ----------------------------------------------------------------

   BEGIN

   SET NOCOUNT ON;

   BEGIN TRY

     INSERT INTO Utility.ErrorLog(Number, Location, Message)

     SELECT @ERROR_NUMBER,COALESCE(@ERROR_LOCATION,'No Object'),@ERROR_MESSAGE;

   END TRY

   BEGIN CATCH

     INSERT INTO Utility.ErrorLog(Number, Location, Message)

     VALUES (-100, 'Utility.ErrorLog$insert',

     'An invalid call was made to the error log procedure ' +

       ERROR_MESSAGE());

   END CATCH

 END;

Then we test the error handler with a simple test case, as follows:

 --test the error block we will use

 BEGIN TRY

   THROW 50000,'Test error',16;

 END TRY

 BEGIN CATCH

   IF @@trancount > 0

     ROLLBACK TRANSACTION;

   --[Error logging section]

     DECLARE @ERROR_NUMBER int = ERROR_NUMBER(),

       @ERROR_PROCEDURE sysname = ERROR_PROCEDURE(),

       @ERROR_MESSAGE varchar(4000) = ERROR_MESSAGE();

   EXEC Utility.ErrorLog$Insert @ERROR_NUMBER,@ERROR_PROCEDURE,@ERROR_MESSAGE;

   THROW; --will halt the batch or be caught by the caller's catch block

 END CATCH

This returns the error we threw.

And checking the ErrorLog, you can see that the error is logged. (Say that three times fast. I dare you.)

 SELECT *

 FROM Utility.ErrorLog;

This returns the following:

This basic error logging procedure can make it much easier to understand what has gone wrong when a user has an error. Expand your own system to meet your organization’s needs, but having an audit trail will prove invaluable when you find out that certain types of errors have been going on for weeks and your users “assumed” you knew about it!

The only real downside to logging in this manner is transactions. You can log all you want, but if the log procedure is called in a transaction, and that transaction is rolled back, the log row will also be rolled back. To write to a log that isn’t affected by transactions, you can use the xp_logevent extended stored procedure in the error handler to write to the Windows Event Log. Using this method can be handy if you have deeply nested errors, in which all the dbo.ErrorLog rows get rolled back due to external transactions.

Other Possibilities…

In the end, it is really going to be up to your database’s particular needs to determine exactly what you may need to put into every (or certainly greater than one) database in your organization. In practice, I tend to see some utilities positioned in a database that are used on the server, for backups, generic index maintenance, and other sorts of generic maintenance.

In my practice, anytime the application and/or any database code references code or data from within the database, everything gets created in the database with the other code. Some other types of concepts that I have seen added to the database to enhance the application are:

• Functions that aren’t implemented in SQL Server: User-defined functions have plenty of pros and cons when used in T-SQL statements (particularly in a WHERE clause) but they definitely have their place to wrap common, especially complex, functionality into a neat package. For example, consider the functionality we covered in the earlier section entitled: “Separating Comma Delimited Items.” We might create a function Tools.String$Split (@ValueToSplit) that encapsulates the string splitting function for reuse.
• Security : Microsoft provides a lot of security oriented system functions for you to determine who the user is, but sometimes it isn’t enough. For some systems, I have employed a function in a schema named security to allow for overriding the system context information when needed. The goal being that whenever you need to know what user is making changes to your system, you can call Security.userName$get() function and know that it is doing the heavy lifting, either looking to original_login() system function or using a custom built security system.
• Reference/demographic information : It is very common for systems to need the city, state, zip/postal code, phone number, and/or country information of customers/clients. Several companies (including the United States Postal Service) publish the domain data that defines the regions and format of this data, but in every case I have seen, they provide the data in arcane formats that are difficult to work with. So we create a demographics database with a schema (we use reference for ours) that we can duplicate in any system that needs this sort of data so we have to load it once from the foreign format, and in the expected format everywhere else it is needed making it easier to share around the enterprise.

As we said, the sky is the limit; use your imagination as to how you can build up your code base in a way that is reusable and also will enhance your application’s needs.

Summary

In most chapters we end with a section covering best practices, but in this chapter there really is only one primary best practice: keep your code encapsulated within the bounds of each database. Even if you use the same code in every database and it is the same now, it may not remain that way forever. The biggest lessons we should have learned from our procedural programmer cousins is that trying to manage > 1 user of the same code leads to a excessively warm condition where the gatekeeper wears red pajamas and carries a pitchfork since every user needs to be updated simultaneously, or you end up with bunches of slightly different copies of code and no real idea who is using what.

The main thrust of the chapter was to demonstrate a number of interesting tools you can use to improve your databases for the convenience of the programmers and DBAs, as well as a number of ideas of how you might start to expand your own database implementations, including:

• Numbers table: A table of numbers that you can use to perform actions where a sequence of values might be useful. For example, finding gaps in another sequence of values (like identity values).
• Calendar table: A table that can help you use to implement methods of grouping date data using typical relational methods, both for normal date groupings like year, month, etc, but also for custom times like corporate fiscal calendars, sales, holiday, etc. While the most common usage might be for data warehouse/reporting systems, having the calendar table available in your OLTP database can be very useful/
• Utilities: Every programmer has code that they use to make their job easier. I presented conceptual utilities to monitor usage of the system and extended DDL to support operations that aren’t part of the base DDL in T-SQL. Having them accessible in the database means that you can always be sure that the version that the programmer expected to be there will be.
• Logging actions: Utilities to log the actions of users in the database, generally for system management reasons. A common use is an error log to capture when and where errors are occurring.
• Any other use that you can dream up to make it easier for you, the programmers, and the DBAs to work with the database on any server, from development, to QA, and production. The more add-ins you can create that can make the experience across database implementations more consistent the better off you will be once your database is out in the world being used and, of course, maintained for years (likely decades) to come.

The goal is no and should always be to make the database its own universe. With the new contained database features in SQL Server 2012, and SQL Azure gaining traction, the database container is going to be closing up tighter than your grandmother’s Tupperware casserole keeper. And even if it weren’t, the more of the functionality that ends up in the database, the easier it will be to test that everything works, and that a change in one system will have no effect on the other. And that should mean fewer questions at 3:00 a.m. about why something failed because another database wasn’t available when another needed it. That can’t be a bad thing, can it?

¹ Ivars Peterson, “Taxicab Numbers,” www.sciencenews.org/view/generic/id/2948/title/Math_Trek__Taxicab_Numbers .