Sql server 2005 the common language runtime clr integration
This presentation is the property of its rightful owner.
Sponsored Links
1 / 24

SQL Server 2005 The Common Language Runtime (CLR) Integration PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

SQL Server 2005 The Common Language Runtime (CLR) Integration. CLR Integration CLR Introduction. Highlights Common type system (CTS) Mapping of data types. Programming language  Framework Just-in-time (JIT) compilers JIT compiles intermediary language (MSIL) into native code

Download Presentation

SQL Server 2005 The Common Language Runtime (CLR) Integration

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Sql server 2005 the common language runtime clr integration

SQL Server 2005The Common Language Runtime (CLR) Integration

Clr integration clr introduction

CLR IntegrationCLR Introduction

  • Highlights

    • Common type system (CTS)

      • Mapping of data types. Programming language  Framework

    • Just-in-time (JIT) compilers

      • JIT compiles intermediary language (MSIL) into native code

      • Highly optimized for platform or device

    • Garbage collector

    • Permission and policy-based security

    • Exceptions

    • Threading

    • Diagnostics and profiling

Clr integration clr diagram

Thread Support

COM Marshaler

Type Checker

Exception Manager

Security Engine

Debug Engine

MSIL to Native

Compilers (JIT)




Collector (GC)

CLR IntegrationCLR Diagram

  • Common Language Runtime Diagram

Base Class Library Support

Class Loader

Clr integration sql server 2005 clr

CLR IntegrationSQL Server 2005 – CLR

  • Run managed code within a database by using in-process assemblies

  • Create managed stored procedures, triggers, user-defined functions, user-defined types, and aggregates

  • Integration benefits:

    • Enhanced programming model

    • Enhanced safety and security

    • Common development environment

    • Performance and scalability

Clr integration deep integration with the database

SQL Engine




SQL OS Layer

Windows OS

CLR IntegrationDeep Integration with the Database

  • CLR Hosting layer provides coordination

    • Assembly Loading

    • Memory management

    • Security Model

    • Reliability

    • Threads & Fibers

    • Deadlock detection

    • Execution context

Sql server 2005 the common language runtime clr integration



Assembly: “TaxLib.dll”

SQL Data Definition: create assembly … create function … create procedure … create trigger … create type …

SQL Queries: select sum(tax(sal,state)) from Emp where county = ‘King’

SQL Server

The Developer Experience

VS .NET Project

Runtime hosted by SQL (in-proc)

Clr integration available classes

CLR IntegrationAvailable Classes

  • Even in supported assemblies, some APIs are not available in SQL

    • Environment.Exit(), Console, etc.

  • Potentially unreliable constructs disabled

    • No thread creation

    • No shared state or synchronization

    • No listening on sockets in server

    • No finalizers

  • Eliminate functionality N/A to database

    • System.Windows.Forms

    • System.Drawing

    • System.Web, …

Clr integration sql server projects in visual studio 2005

CLR IntegrationSQL Server Projects in Visual Studio 2005

  • Project for creating managed database objects

  • Automatically includes necessary references

    • System

    • System.Data.dll

  • Includes templates for each object type

    • Stored procedure

    • Trigger

    • User-defined function

    • User-defined type

    • Aggregate

  • Allows immediate deployment and debugging

Clr integration the system data sqlserver namespace

CLR IntegrationThe System.Data.SqlServer Namespace

Clr integration registering an assembly

CLR IntegrationRegistering an assembly

  • CREATE ASSEMBLY assembly_name

    • [ AUTHORIZATION owner_name ]

    • FROM { < client_assembly_specifier > | < assembly_bits > [,...n] }


    • < client_assembly_specifier > :: =

    • '[\\machine_name\]share_name\[path\]manifest_file_name'

    • < assembly_bits > :: =

    • { varbinary_literal | varbinary_expression }

Clr integration assembly security permission set

CLR IntegrationAssembly Security -PERMISSION_SET

  • SAFE

    • May not access external resources: registry, file system, or network

    • May access data using the current context but not via SQLClient or any other data provider

    • No thread processing


    • May access external resources:registry, file system, network, environment variables


    • May access external resources

    • Can use SQLClient and other data providers

    • Can use thread constructs

    • (No restrictions; similar to extended stored procedures)

Clr integration meta data of assemblies

CLR IntegrationMeta Data of Assemblies



Assembly source code:


Assembly references:


Details of Assembly:


Other meta data information




Clr integration user defined functions

CLR IntegrationUser Defined Functions

  • Similar to T-SQL function

  • Written in CLR language

    • Decorated with [SqlFunction] attribute in code

    • Assembly loaded into the database

    • Function defined from assembly

  • Limits on functions

    • must be in public class

    • cannot be in nested class

    • method must be public and static

Clr integration user defined functions example

CLR IntegrationUser Defined Functions -Example

public class MyFunctions



public static SqlString GetLongDate(SqlDateTime DateVal)


// Return the date as a long string

return DateVal.Value.ToLongDateString();



Clr integration user defined functions1

CLR IntegrationUser Defined Functions

  • Properties have impact on whether or not computed column that use these functions can be indexed.

    • IsDeterministic = true

      (it always produces the same output values given the same input values and the same database state.)

    • DataAccess

      • DataAccessKind.None: Does not access data.

      • DataAccessKind.Read: Only reads data.

    • SystemDataAccess

      • SystemDataAccessKind.None: Does not access system data.

      • SystemDataAccessKind.Read: Only reads system data.

    • IsPrecise = { true | false }

      (that indicates whether the routine involves imprecise computations such as floating point operations. )

Clr integration net stored procedures 1

CLR Integration.Net Stored Procedures (1)

  • Capable of doing everything a T-SQL proc can do.

  • Uses a Shared method (static in C#)

  • Pass parameters both ways

    • OUTPUT parameters should be byref (ref in C#)

  • Return multiple result sets

Clr integration net stored proc can return 2

CLR Integration.Net Stored Proc Can Return (2)

  • Numeric return code

  • Count of rows affected by the command

  • Scalar value

  • Single row

  • One or more multi row result sets

  • A stream of XML

Clr integration stored procedure

CLR IntegrationStored Procedure

public class ContactCode



public static void GetContactNames()


SqlCommand cmd = ……. ……

cmd.CommandText = "SELECT FirstName + ' ' + LastName" +

  • " AS [Name] FROM Person.Contact";

    SqlDataReader rdr = cmd.ExecuteReader();

    SqlPipe sp = …………..;




Clr integration create sql server proc

CLR IntegrationCreate Sql Server Proc.

Syntax :

create procedure ProcName

as external name <assemblyname>.<classname>.<methodname>

Example :

create procedure GetContactsName as external name assemblyname.ContactCode. GetContactNames

Clr integration triggers

CLR IntegrationTriggers

public class ContactCode


[SqlTrigger(Name="ContactUpdTrg", Target="Person.Contact", Event="FOR UPDATE")]

public static void ChangeEmail()

{SqlTriggerContext trg = SqlContext.GetTriggerContext();

Clr integration when to use t sql

CLR IntegrationWhen to use T-SQL

  • T-SQL better used for data access

    • All pre-SQL Server 2005 code is written in T-SQL

    • SQL Server 2005 adds exception handling to T-SQL

  • T-SQL can be faster for data access

    • Direct access to SQL Server's internal buffers

    • Rich, data-centric library of functions

    • No conversion of types

Clr integration feature comparison with t sql

CLR IntegrationFeature Comparison with T-SQL

Clr integration best uses of sqlclr

CLR IntegrationBest uses of SQLCLR

  • Computational functions are always faster

  • Streaming table valued functions

  • User defined aggregates

    • Orders magnitude faster than server or client cursor solutions

  • Scalar functions

    • Function body is compiled to native code

  • Use managed code for:

    • Procedures that feature complex logic

    • Access to the .NET Framework class library

    • CPU intensive functions

Sqlclr guidance mid tier vs data tier

SQLCLR GuidanceMid Tier vs. Data Tier

  • SQLCLR support does not mean move all business logic to server

  • Candidates for moving to server

    • Centralized data validation

    • Process large amount of data while needing a small portion of it for application use

  • Login