DateTime can be a tricky data type to deal with. Not only is there daylight saving time and different time zones to keep in mind but also the range and precision can vary in different systems. You are probably already aware of some differences between .NET framework’s DateTime structure in Transact-SQL’s datetime data type.

Most notable is certainly a different minimum value that can be expressed:

• In .NET framework all values between January 1st 1 and December 31st 9999 are supported.
• In Transact-SQL only values between January 1st 1753 and December 31st 9999 are supported.

This usually shouldn’t be a problem unless you are working with historic data. Setting proper minimum value in the user interface is not be a bad idea nevertheless.

You might not be aware of the difference in precision between these two data types, though. In some edge cases this can cause strange and unpredictable behavior. Let’s take a look at the following example (based on an issue that had me mystified for some time):

using (var context = new TestEntities())
{
    var task = context.Tasks.Single(t => t.Id == taskId);
    task.CompletedAt = DateTime.Now;
    timestamp = task.CompletedAt.Value;
    context.SaveChanges();
}

using (var context = new TestEntities())
{
    var task = context.Tasks.Single(t => t.Id == taskId);
    Assert.GreaterOrEqual(timestamp, task.CompletedAt);
}

The assertion in the above code will occasionally fail with a message similar to this one:

SampleTest has failed:
  Expected: greater than or equal to 2011-12-26 19:04:28.313
  But was:  2011-12-26 19:04:28.312

If you’re not familiar with entity framework, here’s what’s going on:

• In the first code block a DateTime value is stored to the database.
• In the second code block that same value is reloaded from the database.
• The value from the database is compared with the one stored in a local variable the whole time.

The strange behavior is caused by the fact that milliseconds get rounded in the database: to increments of .000, .003, or .007 seconds, as it is stated in the documentation. The DateTime value can therefore be different when reloaded from the database, hence the occasional failed assertion. While the problem might be fairly obvious in the simplified example above, it can be much more difficult to pinpoint in a larger chunk of code where there could be other reasons for the values to differ. Even more so if you’re not even aware of the rounding.

Knowing this might save you a couple of hours once.

In a technical document I was working on today I hade to include some details about database table definitions, including the column names, data types, primary and foreign key information, and column descriptions stored in MS_Description extended property. Using information schema views and the fn_listextendedproperty function I wrote a table valued function which returns information about all the columns in the given table. I’m posting it here in case someone else finds it useful.

CREATE FUNCTION [dbo].[GetColumnDetails] 
(    
    @tableName sysname
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        ColumnName = C.COLUMN_NAME, 
        DataType = UPPER(C.DATA_TYPE) + CASE
                WHEN C.CHARACTER_MAXIMUM_LENGTH = -1 THEN '(MAX)'
                WHEN C.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN
                    '(' + CONVERT(nvarchar(10), C.CHARACTER_MAXIMUM_LENGTH) + ')'
                WHEN C.DATA_TYPE IN ('decimal', 'numeric') THEN
                    '(' + CONVERT(nvarchar(2), C.NUMERIC_PRECISION) + '; '    
                    + CONVERT(nvarchar(2), C.NUMERIC_SCALE) + ')'
                ELSE ''
            END
            + ', ' + CASE C.IS_NULLABLE 
                WHEN 'NO' THEN 'NOT NULL'
                ELSE 'NULL'
            END
            + CASE
                WHEN PK.CONSTRAINT_NAME IS NOT NULL THEN ', PK'
                ELSE ''
            END
            + CASE
                WHEN FK.CONSTRAINT_NAME IS NOT NULL THEN ', FK'
                ELSE ''
            END,
        Description = D.value
    FROM INFORMATION_SCHEMA.COLUMNS C
        LEFT OUTER JOIN
            (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
                ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY') AS FK
            ON C.TABLE_NAME = FK.TABLE_NAME AND C.COLUMN_NAME = FK.COLUMN_NAME
        LEFT OUTER JOIN
            (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
                ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY') AS PK
            ON C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME
        LEFT OUTER JOIN
            fn_listextendedproperty('MS_Description', 'schema', 'dbo', 
                'table', @tableName, 'column', default) AS D 
            ON D.objname COLLATE database_default = C.COLUMN_NAME COLLATE database_default
    WHERE C.TABLE_NAME = @tableName
)

One would have thought this to be a well known fact but since one of our developers stumbled upon it yesterday I thought it might be useful to someone else.

As soon as a pooled connection with enabled application role gets reused an exception gets thrown and its description is not really helpful if you’re not aware of the problem: General Network Error. This happens because the security context of the connection doesn’t get properly reset when it is closed. To work around the problem you can either avoid using application roles or disable connection pooling (by adding Pooling=False to the connection string). You can find more details in this knowledge base article.

It might be worth mentioning that the above is completely accurate only for SQL Server 2000. SQL Server 2005 comes with a new stored procedure sp_unsetapprole which can be used to reset the security context. I haven’t tried it but by calling it before closing the connection you should be able to make application roles work together with connection pooling.

Today I’ve been evaluating database designs made by this year’s candidates for work at our company. One of the issues I’ve been encountering all the time was the handling of date and time values. The SQL Server’s common data type datetime for both values has been the source of quite some confusion, especially since the values required to be separate at some occasions and joined at others.

I suppose the decision on whether to store them separate or joined depends on how the values are going to be used. In most cases the extra storage space required by using separate columns for both values isn’t worth it but in some usage scenarios the resulting reduced query complexity and improved indexing usage can justify it.

Either way sooner or later the need for separating or joining the date and time parts will arise. The following query demonstrates both operations in what I believe is the most efficient way:

SELECT
   dateOnly = CONVERT(datetime, FLOOR(CONVERT(float, dateTimeColumn))),
   timeOnly = dateTimeColumn - CONVERT(datetime, FLOOR(CONVERT(float, dateTimeColumn))),
   dateAndTime = dateOnlyColumn + timeOnlyColumn 
FROM DateTable

The following query is a good starting point if you want to export the SQL Server Agent job history to a file and you’re still using Enterprise Manager from SQL Server 2000. In SQL Server Management Studio from SQL Server 2005 there’s already a command available to do this in the Log File Viewer window accessible from the View History command on the selected job.

USE msdb
SELECT
   J.name, 
   S.step_id,
   S.step_name, 
   H.message, 
   run_status = CASE H.run_status
      WHEN 0 THEN 'Failed'
      WHEN 1 THEN 'Succeeded'
      WHEN 2 THEN 'Retry'
      WHEN 3 THEN 'Canceled'
      WHEN 4 THEN 'In progress'
   END, 
   H.run_date, 
   H.run_time, 
   H.run_duration
FROM sysjobhistory H
   INNER JOIN sysjobsteps S ON H.step_id = S.step_id AND H.job_id = S.job_id
   INNER JOIN sysjobs J ON J.job_id = H.job_id
ORDER BY H.job_id, H.run_date, H.run_time, S.step_id