posted on Friday, September 02, 2005 12:12 AM by amachanic

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!

Comments

# re: Using static properties in SQLCLR UDTs @ Friday, September 02, 2005 1:00 PM

Dude,
First a nitpick - foo in your example above is not a property, it's a field :-). Howsoever, I totally agree with you that they have very limited use inside SQL Server. But the behavior is as expected when it is a static.
Niels

Niels Berglund

# re: Using static properties in SQLCLR UDTs @ Saturday, September 03, 2005 9:34 AM

Niels,

It's a good nitpick. We were talking about static members in the meeting, but the conversation started with a discussion about properties so it all kind of merged in my mind.

Then again, a readonly public static member is really the same, from an interface standpoint, as a static property that happens to return the value of a readonly private static member. There is probably little to gain, in that case, from making the member private and wrapping access in a property. Or am I again missing something?

amachanic

# re: Using static properties in SQLCLR UDTs @ Saturday, September 17, 2005 10:18 AM

One use case that I can think of is storing the compiled version of a regular expression. That way the regex doesn't have to be recompiled on each invocation.

Andrew Novick

# Using static properties in SQLCLR UDTs @ Thursday, July 13, 2006 12:49 AM

Originally posted here.

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

Anonymous

# Using static properties in SQLCLR UDTs @ Monday, January 08, 2007 2:29 PM

Originally posted here . I spoke at the Beantown .NET user group meeting tonight, on the topic of SQLCLR

Anonymous