Hidden features/Best uses of SQLCLR (MS SQL Server 2005 and 2008)

Question

Continuing on the hidden features theme, I havent found one for SQLCLR (Microsoft 2005 / 2008). For example, undocumented features, tricks to do things which are very useful but not documented enough? Also features that are very useful in general.

Answer 1

• Wrapping the .NET Regular Expression library for use in SQLCLR
• A table valued function that splits a string and returns a table with the tokens.
• Embedding a script engine, so that I can pass a script to a procedure that describes some aspects of what the store procedure should to with the data

Answer 2

These ones come to mind first. Perhaps they aren't even "tricks" anymore, but at one time they were revelatory:

• UPDATE w/ variables to compute a running total. eg UPDATE table1 SET @var = col1 += @var
• "Aggregate" string concatenation, eg. SELECT @var += ','+col1 FROM table1

Another one is fn_dblog, an undocumented system function that lets you read from the log file. I used it recently to compare two equivalent DML statements, to see if one generated more log activity than the other.

In SQL2K, you could define a linked server that pointing back at the local server. Then, within a scalar function, you could insert a row through the linked server into a local table, grab the new id, and return the value, effectively giving you a sequence generator. Functions aren't supposed to have side effects, so MS issued a patch and plugged the hole.

Answer 3

Decorate a SQL CLR class with [CompilerGeneratedAttribute]. One of the less known effects is this:

This attribute allows SQL server to reference compiler-generated static values.

This means that assemblies classes that have static values can still be loaded under a SAFE context if the said classes have this attribute. Normally static values are prohibitted in SAFE assemblies in CLR Hosted Environment:

Given these considerations, we discourage the use of static variables and static data members of classes used in SQL Server. For SAFE and EXTERNAL_ACCESS assemblies, SQL Server examines the metadata of the assembly at CREATE ASSEMBLY time and fails the creation of such assemblies if it finds the use of static data members and variables.

However there are certain cases when is safe to use a static field. One such example is the use of XmlSerializer. Because the constructor of this class uses a critical section, having to construct an instance on each CLR function call is a serious scalability restriction. Declaring the XmlSerializer instance static solves the scalability problem, since the serializer methods are thread safe.