.NET database programmability and extensibility in microsoft SQL server

Abstract

The integration of the .NET Common Language Runtime (CLR) into the SQL Server DBMS enables rich business logic written in modern .NET languages to run close to the data. Database application developers can write business logic as functions, stored procedures, and triggers. They can also extend the native capabilities of the DBMS by adding new scalar data types, and aggregates. A previous paper [2] described the architecture and design principles of the integration of the CLR inside SQL Server. Here we present new aspects of this work. First, we describe the extensibility contracts for user-defined types and aggregates in detail. Second, we present the advances to the CLR integration in SQL Server 2008 which significantly enhances the breath of applications supported by SQL Server. In particular, we describe the support for large (greater than 8000 byte) user-defined types and aggregates, multiple-input user-defined aggregates, and order-aware table valued functions. Third, we show how we leveraged scalar type extensibility to provide a hierarchical identifier data type that enables encoding of keys describing hierarchies as well as built-in support for spatial applications. This support includes both flat- and round-earth spatial types, as well as a spatial index. Fourth, we present how we use Language Integrated Query (LINQ) enhancements in .NET languages to improve developer productivity when creating routines that require data access. Finally, we present preliminary performance results showing the efficiency of streaming TVFs and aggregates relative to equivalent native features.