Perhaps you’ve heard all about the power and flexibility .NET framework integration provides in Sql Server, read the news articles, seen the example code on blogs, but yet you are still unsure on what benefits it can provide. Some of the best demonstration code of SQLCLR is located in a place you may never have thought to look: on your own computer already, conveniently located in the Sql Server directory under “Samples” (If you forgot to check the box to install the samples or are using Sql Server Express, don’t worry; all the sample code is available for download).
A brief overview of some of the more interesting CLR integration related samples:
- Spatial How many applications do you know of that include a sample written by a Turing award winner along with a team of researchers? Jim Gray and the folks at Johns Hopkins developed this code for use in the Sloan Digital Sky Survey. The code performs spatial calculations in a C# TVF and uses the result as an index filter to limit the amount of data that needs to be processed in queries.
- UTF8 UDT If you need to interop with systems that use UTF8 encoding, this code lets you easily do so by storing the UTF8 representation of a string as a UDT.
- Calendar Aware DateTime Similarly, if you frequently need to represent a DateTime in a variety of calendar types, it may be easier to store the DateTime as a UDT which allows for easy manipulation and a variety of representations.
- Large Object Handling This code shows how to simply read and write files between the database and the filesystem in an example PhotoStore application.
What about the simple “Hello, World!” demonstration you expected? Thankfully, “Hello, World!” is included as well, except that this version comes ready to be localized into any language.