Bucket DateTimes in T-SQL

2010-06-10T19:45:00.000-07:00

Today at work I was working on a SQL Server Reporting Services report which shows us the number of rows being created in a certain table in each 10-minute interval. While working on this, I tried several different ways in which I could "bucket" the datetime column into the same ten-minute period. At the end, I finally decided to extract this logic into a User-Defined Function which can bucket any datetime into any size interval.

Here's the function:


/* BucketDateTime: 
 * Buckets any datetime into an interval of the given size. 
 * Returns the minimum datetime for the interval chosen, or NULL if @MinutesPerBucket < 1
 * Example: bucket times 7:29pm and 7:31pm to the nearest 30 minute interval
 * SELECT dbo.BucketDateTime('2010-04-01 19:29:00', 30) => '2010-04-01 19:00:00.000'
 * SELECT dbo.BucketDateTime('2010-04-01 19:31:00', 30) => '2010-04-01 19:30:00.000'
 */
 CREATE FUNCTION [dbo].[BucketDateTime]
(
    @SourceDateTime DATETIME, -- The DateTime to bucket
    @MinutesPerBucket INT     -- The number of minutes per bucket.
)
RETURNS DATETIME
AS
BEGIN

    IF (@MinutesPerBucket < 1) RETURN NULL 

    -- The earliest possible datetime
    DECLARE @Epoch DATETIME                    
    
    -- # of minutes passed between the epoch and source datetime
    DECLARE @MinutesSinceEpoch INT            
    
    -- The value to bucket @SourceDateTime into, based on @MinutesPerBucket
    DECLARE @BucketDateTime DATETIME        
    
    -- # of minutes passed between @BucketDateTime
    DECLARE @BucketMinutesSinceEpoch INT    
    
    -- This value is from Books online: http://msdn.microsoft.com/en-us/library/ms186724.aspx
    SET @Epoch = CAST('1753-01-01' AS DATETIME)     
    
    SET @MinutesSinceEpoch = DATEDIFF(mi, @Epoch, @SourceDateTime)
    
    SET @BucketMinutesSinceEpoch = @MinutesSinceEpoch - (@MinutesSinceEpoch % @MinutesPerBucket)
    
    SET @BucketDateTime = DATEADD(mi, @BucketMinutesSinceEpoch, @Epoch)

    RETURN @BucketDateTime
    
END

GO

Execute LINQ queries without locking any tables

2010-06-08T16:43:00.000-07:00

At work I'm building an application which queries a SQL Server backend with LINQ to SQL, and I recently found that some of my queries were slow due to table locking. I found this excellent blog post referencing Scott Hanselman's blog post which describes how to execute LINQ queries without locking the source tables:

Deciding to go with option 1, I came up with an elegant solution for easily adding this to any LINQ queries. Let's say you are using a LINQ query like this:


var query = from f in Foo
    select new { Foo = f };

foreach (var item in query)
{
    //Do something
}

In this case, the query is actually executed in the foreach loop. By implementing an extension method, you can easily change this to use a transaction with the "Read Uncommitted" Isolation Level like so:


var query = from f in Foo
    select new { Foo = f };

foreach (var item in query.ToListNoLock())
{
    //Do something
}

The extension method is implemented like so:


public static class IEnumerableExtension
{
   public static TransactionScope CreateNoLockTransaction()
   {
       var options = new TransactionOptions
       {
           IsolationLevel = IsolationLevel.ReadUncommitted
       };
       return new TransactionScope(TransactionScopeOption.Required, options);
   }


   public static List<T> ToListNoLock<T>(this IEnumerable<T> query)
   {
       using (TransactionScope ts = CreateNoLockTransaction())
       {
           return query.ToList();
       }
   }
}

And then of course you can implement new versions of various other extension methods on IEnumerable/IQueryable, such as SingleOrDefault, FirstOrDefault, etc.

Note that this is the same as adding (NOLOCK) hints to EVERY table in your query. I do not currently know of a way to add nolock hints to individual tables.

Ryan's coding blog

Bucket DateTimes in T-SQL

Execute LINQ queries without locking any tables