facility9

Let’s say that we have an application that tracks and reports customer bills. Customers are able to view reports based on their bill. As time goes on, the customers have a need to create adjustments to their bill. It should be simple enough to add some identifying data to the bill to determine which bill is an adjustment, right?

Well, not quite. It turns out that the Bill table is being loaded automatically on a regular (hourly) basis by an SSIS package. If a modification comes through the SSIS package, the existing records are removed from the Bill table and a new record is inserted. Changing this process to account for adjustments from the web would require additional development time and slow down the load process.

So, if we can’t add new records to the Bill table, how should we store the adjusted records? We determine that the best method is to put them in a second table that will mirror the columns in the Bill table. This table will be on a separate filegroup to allow for frequent back ups.

A new table is created with the exact same columns and the word ‘Web’ is appended to the table name (these are web adjustments). Additionally, a cross-reference table is created between the two with a unique constraint on the BillId and WebBillId columns, thus ensuring that only one adjustment can be associated with one bill.

So, what happens when we need to add an additional column, index, or constraint to the Bill table? The exact same changes need to be applied to the BillWeb table. If they aren’t, there may be performance differences, data integrity problems, or an important column might not get added and then our reports won’t be accurate or else won’t run at all. This presents a problem. Customers want to be able to modify all of the fields in the billing tables and we need to make sure that they can. Short of being very careful every time we make a change, what other option is available to us?

DDL triggers!

DDL (data definition language) triggers are triggers that fire when database level events occur - a table is added, removed, columns are added, an index is altered, etc. In SQL Server 2005 it’s possible to trap and respond to these events.

I went ahead and scripted this out and tested it on my local machine. Everything worked. Special thanks to Brent Ozar for reviewing this code.

So, without further ado, here is the DDL trigger to modify a mirrored table when changes are made to the source table. Feel free to hit me up in the comments with any questions.

-- Create pvt and pvtWeb as test tables
CREATE TABLE [dbo].[pvt](
   [VendorID] [int] NULL,
   [Emp1] [int] NULL,
   [Emp2] [int] NULL,
   [Emp3] [int] NULL,
   [Emp4] [int] NULL,
   [Emp5] [int] NULL
) ON [PRIMARY];
GO


CREATE TABLE [dbo].[pvtWeb](
   [VendorID] [int] NULL,
   [Emp1] [int] NULL,
   [Emp2] [int] NULL,
   [Emp3] [int] NULL,
   [Emp4] [int] NULL,
   [Emp5] [int] NULL
) ON [PRIMARY];
GO


IF EXISTS(SELECT * FROM sys.triggers WHERE name = ‘ddl_trigger_pvt_alter’)
   DROP TRIGGER ddl_trigger_pvt_alter ON DATABASE;
GO

-- Create a trigger that will trap ALTER TABLE events
CREATE TRIGGER ddl_trigger_pvt_alter
ON DATABASE
FOR ALTER_TABLE
AS
   DECLARE @data XML;
   DECLARE @tableName NVARCHAR(255);
   DECLARE @newTableName NVARCHAR(255);
   DECLARE @sql NVARCHAR(MAX);

   SET @sql = ”;
   -- Store the event in an XML variable
   SET @data = EVENTDATA();

   -- Get the name of the table that is being modified
   SELECT @tableName = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]‘, ‘NVARCHAR(255)’);
   -- Get the actual SQL that was executed
   SELECT @sql = @data.value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘, ‘NVARCHAR(MAX)’);

   -- Figure out the name of the new table
   SET @newTableName = @tableName + ‘Web’;

   -- Replace the original table name with the new table name
   -- str_replace is from Robyn Page and Phil Factor’s delighful post on
   -- string arrays in SQL. The other posts on string functions are indispensible
   -- to handling string input
   --
   -- http://www.simple-talk.com/sql/t-sql-programming/tsql-string-array-workbench/
   -- http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-1/
   --http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-2/
   SET @sql = dbo.str_replace(@tableName, @newTableName, @sql);

   -- Debug the SQL if needed.
   --PRINT @sql;

   IF OBJECT_ID(@newTableName, N’U’) IS NOT NULL
   BEGIN
       BEGIN TRY
           -- Now that the table name has been changed, execute the new SQL
           EXEC sp_executesql @sql;
       END TRY
       BEGIN CATCH
           -- Rollback any existing transactions and report the full nasty
           -- error back to the user.
           IF @@TRANCOUNT > 0
               ROLLBACK TRANSACTION;
          
           DECLARE
               @ERROR_SEVERITY INT,
               @ERROR_STATE    INT,
               @ERROR_NUMBER   INT,
               @ERROR_LINE     INT,
               @ERROR_MESSAGE  NVARCHAR(4000);

           SELECT
               @ERROR_SEVERITY = ERROR_SEVERITY(),
               @ERROR_STATE    = ERROR_STATE(),
               @ERROR_NUMBER   = ERROR_NUMBER(),
               @ERROR_LINE     = ERROR_LINE(),
               @ERROR_MESSAGE  = ERROR_MESSAGE();

           RAISERROR(‘Msg %d, Line %d, :%s’,
               @ERROR_SEVERITY,
               @ERROR_STATE,
               @ERROR_NUMBER,
               @ERROR_LINE,
               @ERROR_MESSAGE);
       END CATCH
   END
GO




ALTER TABLE pvt
ADD test INT NULL;
GO

EXEC sp_help pvt;
GO

ALTER TABLE pvt
DROP COLUMN test;
GO

EXEC sp_help pvt;
GO



A co-worker approached me with the problem of sorting an ArrayList of ArrayLists. Normally you might handle this in a DataTable or entity collection in your particular ORM. In this case, this was the data structure that was available to sort:

[[sortorder][type][message]
 [sortorder][type][message]
 [sortorder][type][message]]

Knowing a little bit about how ORM tools work, I made the guess that they implement a custom comparer to perform this functionality, so I took a look at what it would take for this instance.

This turned out to be a lot easier than I thought, you need to create a class that implements IComparer and pass an instance of that class to the Sort method of the ArrayList.

public class CustomArrayListComparer : IComparer {
    int IComparer.Compare(object x, object y) {
        return ((int)((ArrayList)x)[0]).CompareTo((int)(ArrayList)y))[0]);
    }
}

// example:
myArrayList.Sort(new CustomArrayListComparer());

I know that the above code looks somewhat unreadable, but for the sake of the efficiency of the sort operation, all casting is done in place. It’s necessary to cast objects x and y to an ArrayList in order to access their collection of objects. Since we know that, in this case, the 0th item in the ArrayList is an int, that is cast to an int and the Int32.CompareTo() method is used.

Normally, I hate meetings. Meetings are poison, for the most part. They kill productivity. Over the course of my career, I have been in very few meetings that couldn’t have been accomplished via a phone call or a 5 minute discussion in someone’s office.

Today I spent two and a half hours in a meeting with the client.

Why do I mention it? Because this was the kind of meeting that matters. This was a design meeting.

In my current role I am the database developer. I am responsible for designing every aspect of the database that was not in place before I showed up two weeks ago.

Today’s meeting was spent reviewing the changes I made last week, settling on some naming conventions, and going over security. There were times when I was scribbling on my notepad as quickly as I could. There were other times when I was asking questions and getting clarification.

The important thing to take away from all of this is ask questions.

This is why design meetings are important. A design meeting is often the best place to ask about an entire system. When meeting with an entire project team, the collective knowledge of the entire application is in one room and it’s possible to unearth nearly ever facet of how a system should operate a behave given nearly any set of conditions. But, the only way it’s possible to learn and understand how an entire system operates from end-to-end is to ask questions. Ask questions until you’re blue in the face. Ask questions until the other person is sick of hearing your voice. But make sure to ask intelligent questions (this pretty much guarantees that people won’t be sick of hearing your voice, by the way).

The most important question to ask is “Why?” Don’t ask it as if you meant to say “Dear lord, why would you do something so blatantly stupid?!?” The right why to ask is the why brings about a deep understanding of the situation at hand: “Why did you design XYZ to operate this way?” or “What was the rationale for including a flanging skrill in class ABC?”

Understanding why something should work a certain way is better than understanding how something works. With a thorough understanding of the why it’s possible to create a better how.

Back to today’s meeting. A great deal of time was spent discussing security. (By security, I don’t just mean user name and password validation, I include permission checking too.) I’m sure a lot of time in the next few days will still be spent discussing security. Security is complicated. Security is always different. In this case, all of the security will exist in the database — stored procedures will do all the heavy lifting and the application will call the stored procedures.

By taking the time to sit down today and understand the way that security should work, from the initial log in all the way down to individual page level permissions, I was able to get a solid understand of how it should act and why it should work that way.

Now that I understand the why of the security layer, I understand where I made incorrect assumptions in the work that I did last week, I know why I made those assumptions, and, most important of all, I understand what it is going to take to correct those assumptions and build a better, more robust, security layer.

Sometimes you just have to be able to dynamic search criteria in a stored procedure. What’s going to be the best way to accomplish this?

You could use dynamic SQL and run the risk that you won’t have compiled execution plans, syntax highlighting, or readable SQL code. That’s not really an elegant solution.

Let’s get started with a User table:


CREATE TABLE [dbo].[User](
   [UserId] [int] IDENTITY(1,1) NOT NULL,
   [FirstName] [varchar](50) NOT NULL,
   [LastName] [varchar](50) NOT NULL,
   [EmailAddress] [varchar](255) NOT NULL,
   [Username] [varchar](50) NOT NULL,
   [Password] [varchar](256) NOT NULL,
   [PasswordHint1] [int] NOT NULL,
   [PasswordHint1Question] [varchar](255) NOT NULL,
   [PasswordHint1Answer] [varchar](50) NOT NULL,
   [PasswordHint2] [int] NOT NULL,
   [PasswordHint2Question] [varchar](255) NOT NULL,
   [PasswordHint2Answer] [varchar](50) NOT NULL,
   [FailedLoginAttempts] [smallint] NOT NULL CONSTRAINT [DF_User_FailedLoginAttempts]  DEFAULT ((0)),
   [AccessLevelId] [int] NOT NULL,
   [ClientID] [int] NOT NULL,
   [SiteID] [int] NOT NULL,
   CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
       (
       [UserId] ASC
   )
);


The goal here is to be able to SELECT a user by username for a combination of ClientId and SiteId, but at least one must be present. So, we create a stored procedure:

CREATE PROCEDURE Select_UserByUsername
   @Username VARCHAR,
   @ClientId INT = NULL,
   @SiteId    INT = NULL
WITH EXECUTE AS OWNER AS
/******************************************************************************
* Author:      Jeremiah Peschka
* Create Date: 2008/10/06
* Description: Retrieve user information by Username
*****************************************************************************/
   SET NOCOUNT ON;

   IF @ClientId IS NULL AND @SiteId IS NULL
       RAISERROR(‘At least ClientId or SiteId must be supplied as parameters.’, 16, 1);

    –– Only return moderately useful information.  Security
   –– related information is kept far away from the caller
   –– unless requested specifically.
   SELECT [UserId]
       ,[FirstName]
       ,[LastName]
       ,[EmailAddress]
       ,[Username]
       ,[AccessLevelId]
   FROM [UtilityWeb].[dbo].[User]
   WHERE [Username] = @Username
     AND [ClientId] = COALESCE(@ClientID, ClientId)
     AND [SiteId]    = COALESCE(@SiteId, SiteId);
GO

You can even test it with the following SQL:
EXEC Select_UserByUsername ”
EXEC Select_UserByUsername ”, 3
EXEC Select_UserByUsername ”, NULL, 7

Now, what’s going on here?

This SQL will retrieve relevant user information by Username. Since Username is not 100% unique, it’s necessary to specify that it be associated with either a client or a site. How do we do this? By specifying that the ClientId either be equal to the parameter provided or to itself through the use of COALESCE.

For those that don’t know, COALESCE returns the first non-null expression from its arguments. If you supply SiteId but don’t supply ClientId, the WHERE clause will effectively read:

WHERE [Username] = @Username
  AND [ClientId] = ClientId
  AND [SiteId]   = @SiteId


Since ClientId is always going to equal ClientId, the search is only performed on Username and SiteId.

N.B. It’s important to note that this technique will only work when the columns you are searching on are NOT NULL columns. If the columns are NULLable, you will not return any rows when you attempt to compare col1 = col1.