MS SQL Time Zone Conversion Functions - revisited

A while back I wrote an article for code project regarding an implementation of some code that I wrote to replicate a time zone conversion process that mimics built-in processes in Oracle.  We had recently started migrating some of our databases from Oracle to MS SQL and needed to have this functionality to replicate some of the processes (I also created a blog entry for this).

I have come to use this function process a lot though I have come to realize that there are some performance hits associated to it that impacts large queries.  First off NEVER use this function in a where clause that includes a large number of records.  If this is needed it is better to create variables and use the function to create the appropriate value to match the time zone that the data is stored in and then use the variable(s) in the where clause.  This will drastically improve performance of the query.

Also in an effort to find other ways to improve performance I looked into alternate ways to implement the same features.  I delved into the area of .Net CLR functions for MS SQL to see if that improves performance.  I will be creating a how-to for three different types of CLR functions; CLR function where timezone info is stored in a SQL table, CLR function where the time zone info is maintained in a XML file, and a CLR function where the time zone info is hard coded in the code.

One thing to note is that I did not use the built-in c# timezone conversion functions because in order to do so we would have to mark the CLR functions as "Unsafe" in the database which is not something I am willing to do.  While researching I discovered the reason for this.  The reason is that those functions actually pull their timezone info from the Windows registry and as such is not safe to use since you do not want to expose registry access to normal functions.

Finally I will be providing some performance test information for each of the different functions to show the performance of each approach to know which would be best for you.

Timezone Conversion function using only Transact SQL
Timezone Conversion function using C# CLR function with configuration in SQL table
Timezone Conversion function using C# CLR function with configuration in XML file
Timezone Conversion function using C# CLR function with configuration hard coded in API
Performance evaluations of each approach