in 2005 Microsoft introduced Dot Net programming capabilities to SQL Server. You could now define your own data types, functions, procedures and more using a Dot Net programming logic, while fulfilling the appropriate interface for the object type you were implementing.
Since that time, many of the things users defined have become first class clients in SQL Server itself. We see data types like time, and date that only track one or the other. Geo Spatial data types emerged after CLR implementations. I’m sure many of them are implemented in the SQL Server code base itsef. It may be that others are implemented in CLR code, much as our own may be. Regardless, it has opened a whole new horizon for SQL Server.
I’m curious how our readers have taken advantage of this capability? I have limited implementations of dot net code using the CLR for SQL Server. But there are some that I have found significant, and have added to my Model database.
I have an aggregate function to calculate Median. I’m not sure why that isn’t a standard aggregate funtion built into SQL Server, like Min, Max, SUM, Average, Floor, Celiling, and Standard Deviation.
I like some string functions, like converting a set to a CSV string, or the opposite of deserializin a CSV string into a table. There are plenty examples of that on the web.
I have some functions for converting a hex string to an integer, or integer to hex string.
I liked the TimeSpan data type implementation I found i the SQL Server 2005 CLR programming book from Apress, when the CLR was first released. Its a really cool data type when doing complex date and time calculations, because they are built into the timespan class.
Do you have some favorite CLR implementations you use? How about how you have used the CLR to host stored procedures? Share some of your favorites in our comments. I think we’ll find it really interesting to see what some folks have done.
Cheers,
Ben