Temporal Data

In the first installment of this series, I mentioned that I unnecessarily complicated the ApparelFactory app by introducing temporal data, thus delving myself into the world of temporal database. A temporal database differs from an ordinary database in that it stores different version of data based on their validity throughout time. Other people simply refers to these versions as “history” of the information. A piece of data is said to be “valid” on a certain time if it is consider to be true at that time.  

“Querying a particular version of a temporal data is not straightforward because you need to consider the time component.”

It’s easy to spot a database which contains temporal data. You usually see two time columns in a table indicating the start and the end during which the data is considered valid. The granularity dictates the data type used. For example, if you don’t consider changes within a day to be relevant, then you can use DATETIME ignoring the time portion. SQL Server 2008 has a DATE data type well-suited for this need. If changes within the year is not relevant, you can use INT for both the start and end years. The granularity is usually dictated by the business but in the case of the ApparelFactory, I simply assumed that changes within the day is not relevant, thus I have a tables like the one below where start_dt and end_dt are the start date and end date respectively.

Figure 1 – A table for storing temporal data

Most of the time, users are only concerned with the current valid version of the data. Past versions are of interest only for analysis and evaluation of trends, say the price fluctuation of certain commodity. They may be viewed only through reports and some other data warehousing tools. Querying a particular version of a temporal data is not straightforward because you need to consider the time component. For example, in the t_allowance table, if I were to write a flexible query to retrieve any version an employee’s allowance by date, the WHERE statement would have looked like this

WHERE employee_id = @employee_id AND (@referenceDate BETWEEN start_dt AND end_dt)

Figure 2 – Flexible WHERE statement for a temporal data

A query involving BETWEEN operator is not the most efficient so coders usually cheat when writing one for retrieving the current version. They use = instead, but this presupposes that there is already an established convention in determining the current version. The most common is to make the end date nullable and any row with a null value is considered current. In the application, I used the another variation which sets the end date to a very unrealistically high value. This has the advantage of not requiring you to convert the null end date to a real value when using BETWEEN. This is because using BETWEEN on a null date always evaluates to false.

Figure 3 – Sample query utilizing equality operator instead of BETWEEN for temporal data

In a true temporal database, every change in the data is recorded. This is not practical since most of the time, only a subset of the columns are worth tracking. Only the columns  which trends are worth analyzing are tracked. These are then separated in a table together with  the start and end time columns. In the apparel app, I made sure that user can always see the history of the rates of every operation as well as their usage in a style (what operation is involved in a style at a certain point). The queries would have been complicated had I not employed some “acceptable cheats” in my table design. I’ll discuss more about these so-called cheats in the future installment of the series.

Figure 4 – Column which need to be tracked are usually taken out and made into a separate table

Temporal data are useless if the coder doesn’t employ an intuitive way of presenting them to the user. All these data should be available to reports of course, but users also appreciate it if they could see them instantly without resorting to reports. This way, they don’t have to be inundated with so many information when all they want is, let’s say the rate history for an operation which rate the company has just decided to change. In this connection, the ability for a user to select a date as a parameter during viewing of the data is very integral when designing the UI. A coder can simply throw in a DateTimePicker or Calendar web control. If the user prefers the actual change dates, then a list is a good choice. This approach involves another query but it always ensures new results during every query. It also presents the user with the exact number of changes; something not available with the date time picker / calendar approach.

In the application, the list approach is used as seen in Figure 5. The number of current operation for the style is 3, one is added sometime on 11/01/2009. The rate of one operation also changed on the same date, thus changing the current rate of the style.

Figure 5 – A UI displaying historical changes on data membership

The addition of another operation and the change in rate of another one can be seen in Figure 6. Since only the rate is tracked for the operation, it makes much more sense to simply display the history right away.

igure 6 – A UI displaying historical changes of a single column

Being able to work on temporal database is really a fulfilling experience for a coder, let alone for a novice. A coder feels that his application adds more value to the business because it ensures all the data are there and easy for the viewing. The flipside is, it might not be needed at all and it’s just another unnecessary complexity to an already delayed project. It wouldn’t hurt if the client is asked first because the last thing a coder needs is a flawless feature that no one even dares to use.

Next, I’ll discuss the adjustments, conventions and the cheats I employed.

Linked Server and Synonym

Last Friday, I needed to compare the content of both the DEV and QA databases as a preparation for a QA deployment. My machine just got a fresh image so I didn’t have the database tools that could have made the task a breeze. I had to finish ahead of the deployment so instead of rummaging for the installer of the tools, I decided to improvise. Good thing, they were just few reference tables which usually have few columns.

In order to accomplish my task, I should be able to connect to the QA database in a remote machine and query the equivalent tables together local ones in a single query. But SQL Server has no innate ability to query database from other machine. You connect to that machine using the feature called Linked Server. It’s not going to be a smooth procedure though per my experience but the solution is not too complex to stymie anyone. Once connected, you can query the pertinent tables and here, another feature called Synonym, first introduced in SQL Server 2005, can offer a little convenience.

So the first step is to create a linked server. One way to accomplish this is by right clicking the Linked Servers under the Server Objects node and selecting “Add New Linked Server…”. In my case, I was connecting only to a SQL Server instance so in the General Page all I needed to specify was the name of the instance. Be aware that this is not an arbitrary value when connecting to a SQL Server instance. It has to be the name of the instance.

Next is to configure the security aspect. The objective here is to specify a local login which will be mapped to another login in the remote server so that whenever you use the linked server, you actually use a proxy login in the remote server with the correct privileges. If both servers are located in the same machine, you can simply add any login with correct privileges and check “Impersonate” as shown below. This is a good practice since you’re controlling who gets access to the remote server.

Applying the setting above in a remote server in an Active Directory however, produces this vague error message:

I have no idea where the heck this “NT AUTHORITY\ANONYMOUS” account comes from so I googled the last sentence which, as you can see, is pretty much generic. Not surprisingly, the result was a myriad of situations on almost anything that involves NT authentication. Among them, this one, in my opinion gives the closest semblance to my predicament. Although the KB is applicable only to SQL Server 7.0, I still tried the solution. Mapping the local login didn’t work either so I ultimately settled for SQL Server-authenticated account to establish the security context. I skip the mapping list altogether so the configuration dialog should then contain only values similar to the ones below:

After establishing the linked server, one can proceed to cross database query like this:

If you perform considerable number of queries on the remote server, you can leverage on Synonym which can spare you some keystrokes and make the queries a tad shorter. This is because with Synonym, you can create alias on certain objects of the remote database. You create a synonym in the “Synonym” node of your database. With my linked server, a synonym configuration would look like this:

The only arbitrary field is the name. Everything depends on the properties of the local and remote database. You can also create synonyms for other objects like function, stored procedures and view. Note that the account used by the linked server should have the needed privileges to the concerned object in order to succeed with the creation. With that taken cared of, the previous query then becomes:

Like any other database objects, Synonym is “securable” meaning you can control who gets to do what on it. This can be done in the Permission page of the Create Synonym dialog as shown below. First I thought this can be used to grant access for logins other than the one used in the linked server but based on my investigation, it looks like it’s dependent on the linked server logins. This means you cannot grant access on a synonym to a login unless it is also capable of connecting to the linked server. For safety reasons, I would have loved it if I could let a developer access a synonym but not the linked server.

Obviously this not the most efficient way of comparing data between two identical databases but sometimes when you do things manually, you would be compelled to explore some hidden features in a technology. Who knows, those features might come handy for tasks where tools for achieving them are not there yet.

Taking Care of Time Zones

When creating an application accessible anywhere around the globe, special attention must be given to data involving date and time. A mere DateTime data type is not enough and sometimes, could be a dangerous proposition. The system should also be aware of the time zone. Fortunately, this is very easy today that SQL Server 2008 has already a data type specifically for this requirement – the DateTimeOffset.

Let’s consider a web application that allows viewing of historical data with regard to the status of a task. A designer who tends to skimp on date and time data analysis would come up with the “wrong way” model similar to the left part of this illustration:

Query for historical data usually involves passing the date time when the query is requested and then qualifying this date time BETWEEN the start date and end date columns. To signify that a record is the latest version, the end date is set to null or unrealistic high value like ‘12/30/9999’. This guarantees a match if the current date is passed. Applying this technique in our query for the wrong model, the following scenario would fail:

A user from Manila, Philippines updates the status of Task 1 on 06/26/2009 0900 local time. Two hours later, a user from Tampa, FL tries to view the latest status updates.

Here is the content of our database after the update in Manila

The scenario fails because in Florida, it’s still 06/25/2009 11:00PM and as far as the system is concern, that is earlier than the update date:

The DateTimeOffset data type of SQL Server 2008 makes it easy to avoids this pitfall. Prior to SQL Server 2008, the solutions would usually involve saving the hour offset in the database and use this to manipulate the timezone-agnostic date time in the UI. We applied this new data type in the right side of the model - the “Right Way”. For this model, I opted for LINQ using LinqPad. I find LinqPad to be a very effective prototyping tool and maybe you should give it a try. So back to our discussion, the codes below first simulate the transaction from different time zones. After that, we use the generated timezone-aware dates for the query. As you can see, our parameter qualifies for the condition, as expected, even though the date parts are very far apart. Because of the offset value in the dates, SQL Server correctly computes and determines that Tampa time is actually just 2-hour later.

The DateTimeOffset is really a very valuable addition to SQL Server. It addresses a small but significant shortcoming SQL Server in an era of outsourcing, e-commerce and cloud-computing. I myself is already wary on using DateTime because I just don’t know when a pathetic application of mine would go global. Oh yeah! ;)

Using Table Data Type for Variable-length Parameterized Search

The table data type is introduced in SQL Server 2008 to address the clamor of the community for an efficient means of inserting and updating multiple rows in the database. What used to be a task involving multiple calls to the database can now be achieved in one swoop. True to this objective, it’s no surprise that almost all discussion of table data type usage deals about this topic. Another one which is rarely discussed is on how table data type addresses one of the most volatile code construct in data-driven application – the variable-length parameterized search construct. This is the code construct for a search wherein you allow the user to specify 1 to n number of values for filtering a certain column. The user interface usually resembles the one below:

The interface above is obviously simplified. Most of the time, it involves a filter for almost every column in the result. The assumptions here are that the application does not cached any fetched data and the team has been living too long inside the cave that they haven’t heard about LINQ yet. Before SQL Server 2008 and LINQ, there are two approaches to achieving this, each has it’s own pros and cons.

The first approach is maintenance-free but opens your database to a possible SQL-injection attack. It involves a dynamic query which is composed of multiple strings. One of these strings is passed from the frontend application because it contains the values for the filter. The structure of the stored procedure may look like this:

CREATE PROCEDURE FilterDynaQuery @filterValues  VARCHAR(8000) AS
DECLARE @select VARCHAR(2000)
,@sort VARCHAR(500)
SET @select = ‘SELECT col1, col2, col3, … FROM Table1’
SET @sort = ‘ ORDER BY col1, col2, col3,…’
EXEC sp_executesql (@select + filterValues + @sort)

The @filterValues must contain all he hardcoded values from the frontend. The coder must make sure that regardless of the intention of the frontend, the concatenation of the all the components of the query should be valid. The argument passed should conform to the construct “ WHERE <column> IN (val1, val2,…valn)” like the one shown below

‘ WHERE col1 IN (1,5,8,4)’

The second approach ensures the safety of the database but couples the codes to the number of possible values in every filterable column. If the business adds or removes value allowable for that column, then the signature of the store procedure and the front-end codes should also change. For example, if there are 3 allowable values for that column, you need 3 parameters on for that column. Here’s how the procedure would then look like:

CREATE PROCEDURE FilterExplicitParams @value1 INT, @value2 INT, @value3 INT AS
SELECT col1, col2, col3 FROM Table1 WHERE col1 IN (@value1, @value2, @value3) ORDER BY col1, col2, col3

If you have many possible values for the column to be filtered, using this approach would surely bloat the codes. We all know that the more codes you write, the greater the chance for bugs and delays. Most coders - me included - would rather compromise security a bit, in exchange for easily maintainable codes. This is why the dynamic query is much preferred.

The New Approach

The table data type provides you the benefits of both approach without the compromises. However, be aware that RAD tools have no full support for queries with table-valued parameter, at least for now. In ASP.NET, you cannot use the SqlDataSource directly because you will not be able to fetch the schema of the result. ASP.NET requires the schema to configure the data control to which the data source is bound. This means executing the procedure using the dialog shown below. The problem is that it cannot accept the .NET types mapped to a SQL Server table type.

With ObjectDataSource this is not an issue because the tool doesn’t have to query the database for the schema. The quickest way to create an object for the result of our procedure is to make use of typed DataSet. Yes, this somewhat misunderstood and easily abused component is a boon for this kind of situation in a data-centric application. The stored procedure which will be discussed momentarily would become the source for the select command of the TableAdapter. If you’re new to typed DataSet, an excellent walkthrough can be found here.

For the sample application, we retrieve all the current employees from the AdventureWorks database. The tables involved are shown below.

Since the data is temporal, I decided to create a view which would contain only the current set of employees.

CREATE VIEW HumanResources.CurrentEmployee AS
SELECT e.NationalIDNumber, c.LastName, c.FirstName, c.MiddleName,
d.DepartmentID, d.Name AS Department, e.EmployeeID
FROM HumanResources.EmployeeDepartmentHistory h
JOIN HumanResources.Department d ON h.DepartmentID = d.DepartmentID
JOIN HumanResources.Employee e ON h.EmployeeID = e.EmployeeID
JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE (h.EndDate IS NULL)

The stored procedure accepts a table-valued parameter of type IntId. This is just a one-column table type that can be used to filter a integer column, just like the Employee.DepartmentId. Filtering a column is achieved by a join to the table or view to be filtered; in our case the CurrentEmployee

CREATE TYPE IntId AS TABLE
(
 [Id] INT NOT NULL PRIMARY KEY
)
GO
CREATE PROC FilterEmployeeByDepartment
@DepartmentIds IntId READONLY
AS
SET NOCOUNT ON
SELECT e.EmployeeID, e.NationalIDNumber, e.LastName
, e.FirstName, e.MiddleName, e.Department
FROM HumanResources.CurrentEmployee e
JOIN @DepartmentIds ids ON e.DepartmentID = ids.Id
GO

Examining the parameter of the Select command in the TableDataAdapter would reveal that the mapped .NET object for the SQL table type is System.Object.

Don’t be dissuaded by what the editor seems to suggest. The only types you can use according to the documentation are DataTable, DbDataReader and IEnumerable. But even without having read the documentation, you would have probably used DataTable anyways; it’s just too obvious. Among the 3, IEnumerable still eludes me and I’m still struggling to get my first shot with it. I could care less; for now DataTable is perfectly fine for the job.

Setting up ObjectDataSource with typed DataSet is fairly easy. You can use the TableAdapter directly as shown below. In the next step, Visual Studio lets you specify which method is mapped to which CRUD method. With the sample application, the Get method of the adapter is used for the SELECT and the rest of the CRUD are just left blanks. The argument for the parameter is set to get its value from the Session.

The rows from the HumanResources.Department table should be fetched too. In the sample application, a SqlDataSource is used for this. A list box is bound to this source while a grid view is bound to the object data source discussed a while ago. There is no code necessary to populate the list box. As for the grid view, a dummy table is supplied during the loading because null is not yet supported by the ObjectDataSource with table-valued parameter. In the Find button click, the table is populated accordingly prior to passing it to the data source.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
  {
      DataTable tbl = CreateDeptFilterTabl();                        
      Session["DepartmentIds"] = tbl;        
  }
}

protected void btnFind_Click(object sender, EventArgs e)
{
    DataTable tbl = CreateDeptFilterTable();
  GridView1.Caption = "Employee(s) from ";
    foreach (ListItem item in ListBox1.Items)
  {        
      if (item.Selected)
      {
           tbl.Rows.Add(item.Value);
           GridView1.Caption += (item.Text + " ");
      }        
  }
  Session["DepartmentIds"] = tbl;
  DataBind();
}

DataTable CreateDeptFilterTable()
{
    DataTable tbl = new DataTable();
   tbl.Columns.Add("id", typeof(int));
    return tbl;
}

Codes

You may download the codes here. If you don’t have AdventureWorks database, you can download it here. Be sure to change the connection string before you run the application.

SqlUserDefinedAggregate.IsNullIfEmpty: Does it work?

No. At least for me. I haven’t made this one work at all and I always have to resort to a dirty workaround. According to MSDN documentation, setting this property to true should direct your aggregate to return SQL-null when applied to an empty table which is exactly what you wanted. However, this is not the case. Let’s take a look at this naive example of a UAG which simply concatenates the string value of a column:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined, MaxByteSize=8000, IsNullIfEmpty=true)]
public struct ConcatStr : IBinarySerialize 
{
   SqlString _concatenatedStrs;

   public void Init()
   {
       _concatenatedStrs = null;
   }

   public void Accumulate(SqlString value)
   {
       if (value == SqlString.Null)
           _concatenatedStrs += value;
   }

   public void Merge(ConcatStr group)
   {
      _concatenatedStrs += group.Terminate();
   }

   public SqlString Terminate()
   {
       return _concatenatedStrs;
   }
   #region IBinarySerialize Members

   void IBinarySerialize.Read(System.IO.BinaryReader r)
   {
       _concatenatedStrs = new SqlString(r.ReadString());
   }

   void IBinarySerialize.Write(System.IO.BinaryWriter w)
   {
       if (_concatenatedStrs != SqlString.Null)
           w.Write(_concatenatedStrs.Value);
   }

   #endregion
}

There’s nothing fancy about the codes and its implementation is pretty much straightforward. The quirk is, you don’t get a “NULL” if you apply this to an empty table as you see here

The workaround is simple. You need a flag that you can check if the Accumulate() method has been called or not. Accumulate() is called for every row in a table so no-call on this means the table is empty. Here’s the modified codes for our string aggregator

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined
   ,MaxByteSize=8000, IsNullIfEmpty=true)]
public struct ConcatStr : IBinarySerialize
{
   SqlString _concatenatedStrs;
   bool _isEmpty;

   public void Init()
   {
       _concatenatedStrs = null;
       _isEmpty = true;
   }

   public void Accumulate(SqlString value)
   {
       if (value != SqlString.Null)
       {
           _concatenatedStrs += value;
          if (_isEmpty==true)
               _isEmpty = false;
       }
   }

   public void Merge(ConcatStr group)
   {
      _concatenatedStrs += group.Terminate();
   }

   public SqlString Terminate()
   {
       if (!_isEmpty)
           return _concatenatedStrs;
       else 
           return SqlString.Null;
   }
   #region IBinarySerialize Members

   void IBinarySerialize.Read(System.IO.BinaryReader r)
   {
      _isEmpty = r.ReadBoolean();
       if (!_isEmpty)
           _concatenatedStrs = new SqlString(r.ReadString());
   }

   void IBinarySerialize.Write(System.IO.BinaryWriter w)
   {
      w.Write(_isEmpty);
       if (!_isEmpty)
       {
           if (_concatenatedStrs != SqlString.Null)
               w.Write(_concatenatedStrs.Value);
      }
   }

   #endregion
}

You shouldn’t forget to serialize the flag. Failure to do so wouldn’t preserve its value and it would always have the value in the initialization. If you put a breakpoint, in the terminate event, you could clearly say this one works.

And the result is what you expected:

The same workaround can be applied to any data type of your return value, be it a value-type or UDT. For example, if you don’t apply a workaround like the one below to an integer UAG, you get zero instead of null for an empty table!

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native
   ,IsNullIfEmpty=true)]
public struct SumInts
{
   int _accumulator;
   bool _isEmpty;
   public void Init()
   {
       _isEmpty = true;
   }

   public void Accumulate(SqlInt32 value)
   {
       if (value != SqlInt32.Null)
       {
           _accumulator += value.Value;
           if (_isEmpty)
               _isEmpty = false;
       }
   }

   public void Merge(SumInts group)
   {
       _accumulator += group.Terminate().Value;     
   }

   public SqlInt32 Terminate()
   {
       if (!_isEmpty)
           return new SqlInt32(_accumulator);
       else
           return SqlInt32.Null;
   }
}

I still hope I would be proven wrong with my findings. But until then, I just have to content with this dirty codes. What’s important is that it works.

Simple Database Change Log in SQL Server 2005

As promised in my last post, I’m going to tackle DDL triggers in SQL Server 2005. The only way to do this is through the feature SQL-CLR which was first introduced in the said version of SQL Server. Basically, with this feature you can create database objects using your favorite .NET language. I wrote an article a few months back about one of the objects you can create with SQL-CLR. DDL trigger is not as complicated as the one discussed there as you see momentarily.

The .NET Side

SQL Server 2005 DDL trigger is nothing more than a static void function adorned with Microsoft.SqlServer.Server.SqlTriggerAttribute as shown below

If you’re using Visual Studio Express or above, probably the only assembly you need to import is System.Xml. Everything else comes from the common set of assembly referenced by default. Notice that we left out namespace from our codes.  I suggest you do this because namespaces have some weird effects to SQL Server when you catalogue your SQL-CLR objects. You may try otherwise but you’ve been warned.

The next step is optional. It merely checks whether or not your function is executed by SQL Server. It checks if the SQL Server context is present. If not, we should exit or else subsequent calls to context-sensitive objects would fail. Context-sensitive objects have values only when the function is running inside SQL Server. They can produce exception or behave unexpectedly when called outside SQL Server. If your 100% sure your functions will not be executed by consumers other than SQL Server,then you can skip this line.

Passing beyond this line guarantees that we are indeed inside SQL Server. Since we’re going to insert a record, we need a connection. Instead of creating a new one, we can use the connection that executes the function. We do this by supplying the argument “context connection = true” in one of the SqlConnection constructor overloads. To guarantee automatic closing and disposal, we use the using construct

 

Inside this block, we insert the records using a SqlCommand. It’s fine to use inline statement in this case because the codes are never run outside SQL Server and therefore SQL injection is not possible.

The schema of the ChangeLog table can be found in the last post.

The to insert comes from another context object SqlTriggerContext. This object is  member of SqlContext and is only meaningful inside a trigger. It has a member EventData which is a SqlXml containing additional info about the event and object that fires the trigger. This acts as your EventArgs. This is exactly the same EventData I discussed in the last post so there’s no need to elaborate.

The final task in the .NET side is to execute the command. The values for the parameters are taken from the elements of the XML document. Navigating to those elements require XPath which like navigating to a folder. You can optionally raise an error for no insertion although this is very rare.

Build your project and copy your assembly to a shallow location. You may close your Visual Studio now; we’re done with the .NET side.

The SQL Server Side

The first step in the SQL Server side is to catalogue the assembly. This can be achieve by the CREATE ASSEMBLY command as shown:

You can catalogue the assembly with a different name but doing so might create confusion later on.

The next step is to catalogue the trigger. The syntax is very similar to that of the T-SQ version illustrated in the last post except for the body which follows after AS.

 

If you change something in the function other than renaming it, you can use ALTER ASSEMBLY command. This already takes care of the cataloguing of the trigger.

You’re trigger is now ready for reporting. You may issue a statement similar to the following and should get similar results:

 

You may catalogue your trigger in the model databases if you want this simple logging mechanism to be present in all your future databases.

Simple Database Change Log in SQL Server 2008

Another pesky bug came out just recently. After scrutinizing the logs I discovered that it was caused by a parameter mismatch between the stored procedure and our our service. Someone modified the procedure, probably for testing purposes. There's no way for us to find out who actually did it because the database objects are not under any source control. If it were a SQL Server database, one thing that the DBA could have done was implement a basic change logging mechanism using Data Definition Language (DDL) Trigger.

Data Definition Language (DDL) Trigger is a code block that is executed by SQL Server after a certain event concerning some specific objects. The complete list of events is found here. DDL trigger was was first introduced in SQL Server 2005 as part of the SQL-CLR feature but I wasn't able to delve much deeper into it because I was pretty much concentrated with UDT then. With 2008, it was ported to SQL Server and that means it can now be created using T-SQL. The fact that I can create DDL trigger without compiling from CLR assembly made the new future very enticing. The benefit I ultimately get from DDL trigger made me hate myself for not learning it when it first came out 4 years ago. It provided me with a simple way of tracking changes in my database. If you are part of team where database security is very lenient (everyone is an admin), then this tool is indispensable.

My database change log is nothing more than a table populated by a generic DDL trigger. I say generic because it takes care of all the events from the most volatile database objects like table, procedure, view, trigger and function. Change among these could surely wreck havoc to the objects in the upper tier. The change log table for storing the changes information is shown below:

The columns are mirrored from the elements found in the schama returned by EventData(). If SQL trigger is to CLR event handler, then EventData() is the EventArgs. The schema of EventData varies depending on the object and the event. You can check the complete schema here. The columns are the ones common to all the events of the volatile object I just mentioned. Take note that EventData() is a context-sensitive function. It can only be utilized inside the body of a DDL trigger.

The trigger tracks all the pertinent events and inserts the data from EventData(). The look might be a novelty to anyone who haven't worked with XML data type.

 

The value() is a SQL XML data type method for navigating the nodes. It uses XPath syntax for the first argument. The second argument is the data type to which the result is converted to. Don't concern yourself too much with the syntax. This is all you really need to know about XML data type as far as DDL trigger is concerned.

After you catalogue the trigger, it appears in the Database Triggers node under your database as shown below:

That's it! Try creating, updating and dropping the objects monitored by the trigger and see the rows inserted. Oh, did I mention that the entire command is also available to you? Just check TSQLCommand column for that.

Be aware though that there are temporary objects that SQL Server generates in some instances like when you alter your table. These temporary objects can also fire the trigger. It's easy to spot them because they have a consistent name format Tmp_<objectName>; for example Tmp_Employee.

One thing you probably want to happen is for your future databases to have this simple change logging mechanism. If that's the case, then do yourself a favor and create the two objects in the model database.

To the SQL Server 2005 folks, hey I'm not leaving you out. We'll tackle 2005 next time. ;)

Removing Duplicate Rows The Lazy Way

There is an easy way to remove duplicate rows in a table albeit not so space-efficient (but hey, space is cheap). Removing duplicate rows, now matter how crazy it is, is a very common task in database maintenance and data warehousing. I've once worked on an SSIS project before which involved one big and dirty database. Unique key constraints were non-existent making the tables very susceptible to duplicates. I was faced with similar situation today when verifying the bugs claimed by the developers on the business objects. It turned out to be a data-related issue. There are duplicates rows because the database developers failed to create unique indices on some of the business tables. Hunting for those rows can be done using complicated queries involving DISTINCT and temp tables but they're too tedious. I always opt for the lazy approach instead. I call it such because it does not require that much thinking. Here are the steps:

1) Create a duplicate table to contain the pristine data. This can be done easily in SQL Server Management Studio through the Script Table as context menu item as shown below:

2) Run the script but make sure you rename the table.

3) Create a unique index with the option to ignore duplicate key. The column that defines this index is the one duplicated in the dirty table. Unfortunately, there is no click-click way to creating an index with such an option. You need to execute this command:

CREATE UNIQUE INDEX IX_NoDuplicates (Code) WITH IGNORE_DUP_KEY

where the name of the index is IX_NoDuplicates and the column defining it is Code.

4) The last step is to execute the INSERT-SELECT statement to copy the rows

INSERT NoDuplicates (Code) SELECT Code FROM WithDuplicates

Voila! No more duplicates!

This particular option is not created in SQL Server solely for this purpose. There are instances when reporting a duplicate error does not contribute any value to the business. It merely lengthen development time. I'm talking, for example, of a simple reference table like EmploymentType. Does it really matter to the company if an HR personnel happened to enter value "Contractual" the second time?