.NET Database Programmability and Extensibility in Microsoft SQL Server

1
.NET Database Programmability and
Extensibilityin Microsoft SQL Server

• José A. Blakeley, Mat Henaire, Christian
  Kleinerman, Isaac Kunen,
• Adam Prout, Vineet Rao

2
Introduction

• Authors are all Microsoft employees
• This paper is part 2 of a series of papers
•  1st paper described the architecture and design
  principles of the integration of the CLR in SQL
  Server
• This paper is going to present newer aspects
  using SQL Server 2008
• Topics
• User-defined Types/Functions
• CLR integration with SQL Server 2008
• LINQ with .NET
• Performance results

3
.NET SQL Server

• The Microsoft .NET Framework is a software
  technology that is available with several
  Microsoft Windows operating systems
• Microsoft SQL Server is a relational database
  management system (RDBMS) produced by Microsoft.
  Its primary query languages are MS-SQL and T-SQL.
• Common Language Runtime  Java Virtual Machine

4
...from terabytes to petabytes andexabytes

• Data volumes are
• EXPLODING
• HEAVY
• Why move something heavy?

5
Basic Concepts

• The ability to run application code inside the
  database in a
• reliable, secure, scalable, and efficient manner
  adds significant
• flexibility to the design of applications
• SQL Server acts as the operating 
• system for the CLR when it is hosted
•  inside the SQL Server process

6
User-Defined Functions (UDFs)

• CLR user-defined functions (UDFs) provide a
  mechanism for
• extending SQL Server with new scalar and
  table-valued routines written in .NET languages
• T-SQL UDFs vs. CLR UDFs
• T-SQL is more constraining
• T-SQL interperated vs. CLR compiled
• T-SQL prevents multi-threading

7
Tabled-Value Functions (TVFs)

• CLR TVFs provide a mechanism forconverting raw
  data (e.g., in a text/binary file or on the
  network) into a relation. 

8
User-Defined Types (UDTs)

• SQL Server 2005 introduced user-defined types
  (UDTs) as a
• mechanism to extend the scalar type system of SQL
• UDTs allow users to define a type using the .NET
  framework, and to deploy and use these types
  within the database
• Since CLR UDTs are simply CLR classes with some
  additional restrictions, they can be instantiated
  on a managed client as well as in the server
• See Example 4.9

9
User-Defined Aggregates (UDAs)

• CLR user-defined aggregates (UDAs) give users the
  ability to write their own aggregates to suite
  their own specific needs. 
• An example aggregate for calculating population
  covariance (a measure of how two sets of data
  vary together) is shown to highlight the
  different parts of the UDA contract
• The contract consists of four methods 
• Init(),
• Accumulate(),
• Merge()
• Terminate()

10
Data Access In Routines

• Microsoft has recently introduced new Language
  Integrated Query (LINQ) technologies that extend
  .NET languages natively with query expressions to
  further reduce, and for some scenarios completely
  eliminate, the impedance mismatch for
  applications.

11
An Example

•  

12
Questions?

•