Use of .net Assemblies in SQL Server 2005

Question

I've recently discovered that it's possible to place .net assemblies on SQL Server >=2005 servers so that .net functions can be called in T/SQL statements.

I wondered what uses people found for these and how they perform?

Answer 1

I found it to be very useful.

I used this possibility to extend MSSQL2005 XML related functions.

If I remember correctly you can even introduce your own data types.

Answer 2

This is normally used if you need to interact with the operating system in some way, for example, to place a message in MSMQ or write to a file. It is also useful if you have some complex mathematical or financial calculations that are already implemented in .NET, and you don't want to re-write them in T-SQL.

Answer 3

The first general purpose use for the CLR in SQL 2005 I created was a SQL 2005 assembly that has a variety of functions that perform string operations and pattern matches using regular expressions. The native string functions in SQL 2005 can be augmented so that you can validate common formats like phone numbers or credit card numbers or perform ad-hoc regular expressions within stored procedures.

For deterministic user defined functions, I have found the SQL CLR support to be very performant.

Answer 4

CLR integration with SQL 2005 is particularly useful for user-defined functions/stored procs written in .NET and also user-defined data types. e.g. You could write a heavy duty data type that allows SQL to define objects and reference properties. You could write some super-duper datetime variation, for instance.

Answer 5

Oppositional, I agree with you. Assemblies are wonderful for all of those things that not at all attainable or barely attainable in T-SQL (via in hackish methods).

Simplicity of use is the key. Please be aware of the following implications...

1. The use of an assembly introduces trust/security/permission configuration issues.

An assembly will have to be defined into the database. If the assembly is not signed, the database will have to allow UNTRUSTED code. This database setting is reset when a database is detached/reattached.

1. The use of an assembly affects the SQL Server configuration (not just a database).

To run an assembly, you must first insure that SQL Server lightweight pooling is disabled.

1. For the Assemblies functions, remember to be conservative with data manipulations; especially, on busy tables.

If you can, avoid the use of an Assembly method that attempts to affect too many rows at a time. Take care for those used within table triggers..as some of these will degrade performance sharply.