sql server 2005 the common language runtime clr integration
Skip this Video
Download Presentation
SQL Server 2005 The Common Language Runtime (CLR) Integration

Loading in 2 Seconds...

play fullscreen
1 / 24

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

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' SQL Server 2005 The Common Language Runtime (CLR) Integration' - shira

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
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



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 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 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