Making date and time sense from the SQL Server Agent job history table

Sometimes you’ve got to wonder why database developers do things the way the do. Take for example the sysjobhistory table in the SQL Server msdb database. Its got two awful columns namely run_date and run_time. Why are they awful you ask? Well because they are integer representations of the date and time. Just look at the following examples to see the stupidity.

run_date run_time Actual
20091123 230000 2009-11-23 23:00:00
20091123 230001 2009-11-23 23:00:01
20091123 234500 2009-11-23 23:45:00
20091124 0 2009-11-24 00:00:00
20091124 0 2009-11-24 00:00:01
20091124 1000 2009-11-24 00:10:00
20091124 3000 2009-11-24 00:30:00
20091124 10000 2009-11-24 01:00:00
20091124 10636 2009-11-24 01:06:36

Now if you’re wanting to do something meaningful with them you’ll want to represent them as a DATETIME data type most likely. After searching for a quick and easy way I decided to hack a quick bit of TSQL up and put it up here so hopefully others might benefit.

            STUFF(STUFF(CONVERT(VARCHAR, [run_date]), 7, 0, '-'), 5, 0, '-') + 
            ' ' + 
            STUFF(STUFF(RIGHT('00000' + CONVERT(VARCHAR, [run_time]), 6), 5, 0, ':'), 3, 0, ':'), 120)
FROM    [msdb]..[sysjobhistory]

Let me know in the comments if you found it useful or if you have a more elegant way. If you use it a lot, make a user-defined function taking in the two values. I leave that in your capable hands.

SQL Server 2008 and .NET Framework 2.0 SP2 dependency explained

Recently a local SA Developer .NET community member reported an issue installing SQL Server 2008 Express as it required the as yet unreleased .NET Framework 2.0 SP2.

Well yes, SQL Server 2008 does rely on the .NET Framework 2.0 SP2. According to MSDN SQL Server 2008 requires .NET Framework 3.5 SP1 which is included in all editions except for the Express editions which require the following:

SQL Server Setup will not install the following required components for SQL Server Express and SQL Server Express with Advanced Services. You must install these components manually before you run SQL Server Setup:

  • SQL Server Express — ..NET Framework 2.0 SP2 and Windows installer 4.5. On Windows Vista, use .NET Framework 3.5 SP1.
  • SQL Server Express with Advanced Services — .NET Framework 3.5 SP1, Windows Installer 4.5, and Windows PowerShell 1.0.

A little more digging and I found Somasegar’s post about the FX 3.5 SP1 beta and subsequently a comment from Larry Sullivan, Group Manager:

First we absolutely realized that there are a number of customers still on .Net Framework 1.1 and we will look into the possibility of servicing it again.  I can tell you that the servicing requests on 1.1 are very low and the volume of servicing requests is a big part of what plays into the timing and need for an SP.  Which of course leads into your questions of why we are updating the 2.0 bits again which comes down to the fact that to provide a service pack of .Net Framework 3.5 it was necessary to provide fixes in the lower level core parts of the .Net Framework.  Given that we needed to update a number of binaries to deliver the 3.5 SP1 we made the decision to go ahead and allow an additional set of fixes into the 2.0 and 3.0 layers.   This is a byproduct of the fact that the current set of .Net Framework versions are much like a layer cake with .Net Framework 2.0 at the bottom, then .Net Framework 3.0 and finally the .Net Framework 3.5 layered on top.  Each of the upper layers has dependencies into lower layers, but not vice versa.  In other words 3.5 can depend on 3.0 and/or 2.0, but 2.0 has no dependencies on 3.0 or 3.5.  As you can see this means that some fixes or features in the 3.5 can require updates in their dependant layer to function properly.  Also, with the introduction of 3.5 we now allow for the SPs of the .Net Framework to install on both machines with predecessor version or no version at all.  This allows customers to not have to deploy two items, the RTM version and its SP, but only the SP.

So that explains it. Personally I’d like to see a list of changes for each service pack and for each service pack to have a standalone installer, but it seems Microsoft chose to bundle it to possibly hide the deployment issues away from the end user. If you look at the .NET Framework 3.5 Architecture of the .NET Framework you’ll see the following note:

Windows Vista does not support the standalone installation of the .NET Framework version 2.0 SP2 or version 3.0 SP2. Windows 2000 does not support the .NET Framework version 3.5 SP 1, nor the standalone installation of the .NET Framework version 3.0 SP 2.

At the end of the day Microsoft hasn’t concealed the FX 2.0 SP2 release, they just didn’t advertise it clearly. I’d appreciate it if they were a little more clear in their release notes.

A practical use for filtered indexes

Have you ever wanted to create a table in SQL Server with a nullable column that should be unique as well? I have often needed this and resorted to defining a new table that links to the master table. But SQL Server 2008 has the solution to this, see my example below.

CREATE TABLE [dbo].[Person] (
      [FirstName]       NVARCHAR(50) NOT NULL,
      [LastName]        NVARCHAR(50) NOT NULL,
      [EmailAddress]    VARCHAR(256) NULL

ALTER TABLE [dbo].[Person]
      ADD CONSTRAINT [AK_Person_0] UNIQUE NONCLUSTERED ([LastName], [FirstName]);

Next consider adding a unique constraint or unique index on the EmailAddress column to make sure its unique.

ALTER TABLE [dbo].[Person]
      ADD CONSTRAINT [AK_Person_1] UNIQUE NONCLUSTERED ([EmailAddress]);

So far so good, we can now add some test data.

INSERT INTO [dbo].[Person] VALUES (N’Bob’, N’Smith’, ‘’);
INSERT INTO [dbo].[Person] VALUES (N’Bob’, N’Jones’, ‘’);
INSERT INTO [dbo].[Person] VALUES (N’Mary’, N’Smith’, NULL);

Next try insert another person with no email address.

INSERT INTO [dbo].[Person] VALUES (N’Frank’, N’Smith’, NULL);

Whoops, looks like our unique constraint is not going to let us have more than one NULL in the table. Which is correct. So how can we fix this, well this is where the new SQL Server 2008 filtered indexes come in. We simply create an index with a WHERE clause as follows:

      ON [dbo].[Person] ([EmailAddress])
      WHERE [EmailAddress] IS NOT NULL;

And then we can insert our new record without having the problem as we did with the unique constraint.

Unfortunately there is a limitation to how this can be used. A unique index cannot be used in a foreign key constraint. I just wish the SQL Server team would add the WHERE clause, and while they are at it the INCLUDE clause as well, to unique constraints.

Thanks to Gail for the suggestion.

Visual Studio 2008 Service Pack 1 released

image If you are a developer using Visual Studio 2008 and have tried to install SQL Server 2008 on top of it, you might have noticed that it doesn’t want to install. According to KB956139 Visual Studio 2008 Service Pack 1 is a required if you intend to use certain features like the the SQL Server Management Studio.

The good news is that Visual Studio 2008 Service Pack 1 has been released to MSDN. It weighs in at a hefty 831MB so hopefully it will have lots of cool stuff and performance improvements not to mention support for the .NET Framework 3.5 Service Pack 1 release that has already shipped as part of SQL Server 2008.

SQL Server Spatial Tools now available on CodePlex

Isaac Kunen has posted details about the new SQL Server Spatial Tools project on CodePlex containing functions that can be used when working with the new SQL Server 2008 spatial data types.

The core idea is that instead of pushing up more samples showing how to extend our spatial functionality in text, we can put them in a more usable form.  Beyond just showing off some samples, though, we’d like to assemble useful code into a toolkit that provides some real functionality over what we bake into the server.

For a list of the goodies that the package contains take a look here and go and download the source code.

SQL Server 2008 ships

Microsoft SQL Server 2008 The other day during the Tech-Ed South Africa 2008 closing keynote it was announced that SQL Server 2008, formerly code-named Katmai, had gone gold and would release onto MSDN the same day. This is awesome news and the team has worked hard on this release.

The most important new features in my opinion are:

  • Spatial support in the form of the new GEOMETRY and GEOGRAPHIC data types with support for the Open Geospatial Consortium (OGC) standards.
  • New support for storing date and times, namely the DATE, TIME, DATETIME2 and DATETIMEOFFSET data types.
  • Sparse columns that reduce the overhead of NULL data on the disk.
  • Page compression to reduce the I/O costs of storing and retrieving data at the slight cost of processor performance.
  • Filtered indexes to provide optimised query plans for commonly used and under-performing queries.
  • Change Data Capture (CDC) to record changes in data across tables, seamlessly.
  • Intellisense in the SQL query editor when working against SQL Server 2008. Hopefully support for older versions is added soon.


You can see all the new features over here and the download is currently available on MSDN and TechNet.

Over the next few weeks I’ll be testing the new features and I’ll post my findings as I go. I’m eagerly waiting for Map Server for Windows to add support for SQL Server 2008 as I’m sure it will kick the collective butt of PostgreSQL and PostGIS.

Microsoft SQL Server 2008 CTP, February 2008

The latest and greatest CTP of SQL Server 2008 (code-named Katmai) is now available for download and testing. This is commonly referred to as CTP6 and is the first update since CTP5 in November 2007. You can download the CTP6 bits over here.

To find out what is new and changed in SQL Server 2008 I suggest you take a look at the February 2008 version of Books Online (BOL). I’m really keen to try out the new DATETIME2, DATETIMEOFFSET, DATE, TIME, GEOGRAPHY, GEOMETRY and VARBINARY(MAX) FILESTREAM data types. Not to mention the ability to pass tables as parameters to stored procedures and the addition of Virtual Earth support directly in the database. This isn’t to be confused with the new spatial data types and spatial index support though.

Remember that CTPs should preferably be installed on test machines or virtual machines. If however you want to use it with Visual Studio there are updates necessary for both Visual Studio 2005 Support for SQL Server 2008 CTP and Visual Studio 2008 Support for SQL Server 2008 CTP.

[tags]SQL Server 2008,Katmai,CTP[/tags]

The pain of SQL Server and working with dates

How many times have you tried to design an efficient database table design in SQL Server only to stumped with the problem of storing pure date values. Yes the currently released versions of SQL Server (up to 2005) don’t support a pure date only data type. Note that SQL Server 2008 add the new DATE data type which should make this post semi-redundant. This can prove painful when you wanting to store values for a specific day. So you end up getting creative. My normal solution is to put a check constraint on the column to enforce that the date value represents the start of the day or 00h00. Oh and I generally make sure I store all my date and time values in UTC so I can render to any time-zone that my application desires.

Here is an example of how I’d do a check constraint on column UtcDate. Note how I effectively truncate the floating-point representation of the date and time value and check that it equals the column value. Also I store it as a SMALLDATETIME as I’m not needing more than 1 minute accuracy. Oh and I’ve tried to reformat the examples to be easier to read on the web, I hope it works. 🙂 

CREATE TABLE [dbo].[Foo]
            CONSTRAINT [CK_Foo_UtcDate]
             CHECK (FLOOR(CONVERT(FLOAT,[UtcDate]))=CONVERT(FLOAT,[UtcDate]))

This method works great for me but in searching for a way to truncate the minutes of a DATETIME to a specific interval I found another approach that not only looks more elegant but most likely performs a lot better. I haven’t run any proper performance tests so I’d appreciate some feedback from those that have done some testing.

CREATE TABLE [dbo].[Foo]
            CONSTRAINT [CK_Foo_UtcDate]
             CHECK (DATEADD(DAY,DATEDIFF(DAY,0,[UtcDate]),0)=[UtcDate])

It looks a lot more efficient than my previous approach, hopefully it is. Oh and take a look here for more very useful tips and tricks when working with dates and times in SQL Server.

[tags]SQL,SQL Server[/tags]

SQL Server 2008 November CTP

Its about time that the long awaited CTP5 of SQL Server 2008 (code-named Katmai) was released. I’ve been seeing blog posts about MVP only builds for the last couple of days and now finally I got an e-mail notification of the November CTP that is available to download from here.

I can’t wait for my download to complete so that I can try out the new GEOMETRY and GEOGRAPHIC data types.

[tags]Katmai,CTP,SQL Server[/tags]

SQL Server 2008 Compression

Jonathan Allen and Chad Boyd have interest blog posts on the new SQL Server 2008 compression features.

Row level compression drastically reduces the meta-data needed for variable length columns. Instead of storing the length of each field in 2 bytes, it now takes a mere 3 bits. Fields themselves are also smaller. Storing a 1 in an int field now only takes a single byte, though of course larger values may use up to 4 bytes.

In my opinion the new feature shouldn’t really be called compression but rather removal of wasted space. Although the concept of sharing common data from multiple rows in the same page is a form of compression and I suppose where the true value lies. It should make for some really interesting indexing strategies and could even motivate some edge use cases for denormalized data.

I really like the built-in backup compression and the idea that pages will only be compressed when its nearly full. I think the decreased physical data storage I/O will have a significant performance benefit over the CPU performance penalty introduced by the compression and decompression of data.

[tags]SQL Server 2008[/tags]