CLR Datatypes and the .NET Framework

Both of the spatial datatypes in SQL Server 2008 (geometry and geography) are implemented as .NET CLR datatypes. This means that, rather than using the regular database engine query processor, as T-SQL functions do, spatial operations are performed using the .NET CLR process hosted by SQL Server (SQLCLR). In contrast to T-SQL, which is a predominantly set-based language, .NET is an object-oriented programming framework. This has a number of implications for the way in which you write any queries using spatial data.

In this chapter, I'll first give a brief overview of the underlying concepts behind object-oriented programming (OOP), and how these relate to the geography and geometry datatypes. I'll then examine the syntax required for .NET CLR datatypes and how this differs to regular T-SQL syntax. I'll demonstrate the generic syntax to create new items of spatial data using static methods, and how to use instance methods to perform operations on those items of data and access their properties. Finally, I'll explain a little bit about how exceptions are raised in the SQLCLR, and the implications this has for any error-handling routines in your spatial applications.

Because all of the spatial methods and types are implemented in a self-contained library, Microsoft.SqlServer.Types.dll, you can even import this assembly into your own project to use the same spatial methods as used in SQL Server 2008 in any other .NET project! To see how this technique can be used, check out some of the example code on this site.