THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Using static properties in SQLCLR UDTs

I spoke at the Beantown .NET user group meeting tonight, on the topic of SQLCLR in SQL Server 2005.

One of the questions that came up during the UDT part of the talk was whether static properties are supported.  Unfortunately, I had no answer at the time--it's not something I'd yet thought to try.

The answer, as it turns out, is yes: they are supported.  But they must be defined as readonly, e.g.:
public static readonly int foo;
As it turns out, this means that they can also only be initialized from a static constructor:
static myType()
    foo = 1;
... which means that value will stick around from the time the type is first used, until the AppDomain is reset (for example, if SQL Server is restarted).

In my opinion, this greatly limits many use cases.  One might, I suppose, have some expensive, yet rarely-modified data to initialize the member with, and get that data on the first pass only. However, if the data does chang, I'm not sure that it would be easy to reset the AppDomain.  Do you really want to restart SQL Server in production environments to update static members?

Another use case I can think of is logging.  Perhaps there are situations in which you'd want to log the first time a type is used.  But that doesn't seem incredibly interesting.

If someone else reading has a more compelling use case, I'd be interested in hearing it!
Published Wednesday, July 12, 2006 10:48 PM by Adam Machanic
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Oleg said:


I have function and I must have static member in it.

I do not see another way. Also, i detected interesting effect:

1. I have function in  one db

2. I call it, then I change connectin to another db

3. I call this function  again and I have result! but function exists only in the 1st db :-)

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

using nvg_net2;

public partial class UserDefinedFunctions


   static readonly SynonymFinder sp;

   static UserDefinedFunctions()


       sp = new SynonymFinder();


   [SqlFunction(DataAccess = DataAccessKind.Read)]

   public static SqlString SynonymFinderor(SqlString strInput)


       using (SqlConnection m_connection = new SqlConnection("context connection=true"))


           if (sp != null && !sp.IsInitialized())


               if (m_connection != null && m_connection.State != ConnectionState.Open) m_connection.Open();

               sp.m_connection = m_connection;




       Regex rx = new Regex(@"\w+");

       string result = rx.Replace(strInput.ToString(), new MatchEvaluator(Synonym));

       return result;


   private static string Synonym(Match m)


       string x = m.ToString();

       string str = "";

       sp.correct(x, ref str);

       if (str == "") str = x;

       return str;



November 24, 2008 7:54 AM

Firefight said:

I can't believe they implemented static members in SQLCLR this way. They should have made static variables unique to each session/connection. That's how Oracle implemented their package level variables in PL/SQL and static method in their embedded Java implementation, and it's what you want 99.9% of the time.

Now almost every 3rd party library is going to have to be marked as UNSAFE because of this. You almost never want pragmatic information passing accross connections like this, I don't know what they were thinking.

January 12, 2009 4:41 PM

Adam Machanic said:

I don't really see the point of having a static member scoped to the session, and this implementation certainly does not prevent you from being able to accomplish that without the use of UNSAFE.  All you need to do, in essence, is set up a keyed collection and use a GUID or something similar to uniquely identify the session.  Some kind of expiration logic would also be necessary, in case the session doesn't call back as planned...

January 13, 2009 10:03 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement