sql clr may 15 2007 n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL CLR – May 15, 2007 PowerPoint Presentation
Download Presentation
SQL CLR – May 15, 2007

Loading in 2 Seconds...

play fullscreen
1 / 18

SQL CLR – May 15, 2007 - PowerPoint PPT Presentation

  • Uploaded on

SQL CLR – May 15, 2007. Tony AnzelmoSQL 2005 Microsoft.Net Common Language Runtime C#, VB.Net , C++ (Safe Compilation) UDF, UDA, UDT, USP. Agenda. Intro to Microsoft SQL 2005 CLR Supported Programming Languages When to use T-SQL vs. SQL CLR Demo: Hello World Security & Performance

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 CLR – May 15, 2007' - amena

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 clr may 15 2007
SQL CLR – May 15, 2007
  • Tony AnzelmoSQL 2005
  • Microsoft.Net Common Language Runtime
  • C#, VB.Net, C++ (Safe Compilation)
  • Intro to Microsoft SQL 2005 CLR
  • Supported Programming Languages
  • When to use T-SQL vs. SQL CLR
  • Demo: Hello World
  • Security & Performance
  • Demo: Regular Expressions
  • Resources and Question/Answers
intro to sql 2005 clr
Intro to SQL 2005 CLR
  • SQL CLR/.Net assemblies not for everyone, and only useful for certain circumstances
  • CLR Integration new to SQL 2005
    • Pre-2005 options were extended stored procedures, C/C++, sp_oa OLE automation extended stored procedures
  • Microsoft.Net V1.0 RTM released January 5, 2002
sql clr benefits
Complex formula operations

String manipulation

Alternative ways to bridge between legacy systems

Iteration as alternative to cursor

Managed .Net code

Supported objects:

Stored Procedures


Functions (Scalar & Table)

Triggers (DDL & DML)


SQL CLR Benefits
supported programming languages
Supported Programming Languages
  • C# and VB.Net only supported .Net languages
    • “Have built-in project templates which guarantee code that is safe for execution in SQLCLR”
  • Host Protection Attributes
    • Determines what types are allowed on the host CLR environment and not. Used in conjunction with Code Access Security
  • C++ is allowed with safe compilation option
    • compile.exe /safe <source.c>
net namespaces
.Net namespaces
  • SQL CLR .Net Namespaces
    • System.Data
    • System.Data.Sql
    • System.Data.SqlTypes
    • Microsoft.SqlServer.Server
demo hello world
Demo: Hello World

No need to pay for Visual Studio $$$, just notepad, csc, and osql…. but an IDE like Visual Studio would be very helpful, for things like IntelliSense and debugging tools

  • Write our class file
  • Compile into a dynamic linked library/Microsoft.Net assembly
  • Bring assembly into SQL
  • Attach assembly to T-SQL endpoints
enabling sql clr
Enabling SQL CLR

sp_configure ‘clr enabled’, 1 AND reconfigure

demo hello world c
Demo: Hello World C#

using System;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class SQLLibrary


public static void HelloWorld()


SqlContext.Pipe.Send(String.Format("Hello world! The current time is {0}",System.DateTime.Now.ToString()));



C:\set path=%path%;c:\windows\Microsoft.Net\Framework\v2.0.50727\

C:\csc /target:library HelloWorld.cs

demo hello world deploy
Demo: Hello World Deploy


use Playground


if exists(select * from sys.procedures where name = 'HelloWorld')

drop procedure HelloWorld


if exists(select * from sys.assemblies where name = 'SQLAssembly')

drop assembly SQLAssembly


create assembly SQLAssembly from 'c:\drop\test.dll'


create procedure dbo.HelloWorld

as external name SQLAssembly.SQLLibrary.HelloWorld


exec dbo.HelloWorld


C:\osql -E -S localhost -i deploy.sql

assembly dmvs
Assembly DMVs
  • sys.assemblies
  • sys.assembly_files
  • sys.assembly_modules
  • sys.assembly_references
  • sys.module_assembly_usages
  • SQL CLR CAS Permission Sets
    • SAFE: string, computation, local data access
    • EXTERNAL_ACCESS: adds on file, network, registry, and environment vars
    • UNSAFE: adds less restriction, adds ability to call unsafe code like COM (do not allow!)
  • Role Based Security (User must be logged in with their AD/NTLM account)
performance comparisons
Performance Comparisons
  • Regular Expressions T-SQL vs. CLR
demo regular expressions
Demo: Regular Expressions

using System;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public class RegExCompiled


[SqlFunction(IsDeterministic = true, IsPrecise = true)]

public static bool RegExCompiledMatch(string pattern, string matchString)


return Regex.Match(matchString.TrimEnd(null), pattern.TrimEnd(null), RegexOptions.Compiled).Success;



demo udf deployment
Demo: UDF Deployment


--Same assembly/procedure drop existing and add logic

CREATE FUNCTION dbo.RegExMatch(@RegExPattern nvarchar(4000),@SearchString nvarchar(4000)) RETURNS BIT

AS EXTERNAL NAME RegExBase.RegExCompiled.RegExCompiledMatch --assembly_name.class_name.method_name


debugging options
Debugging Options
  • Debugging SQL CLR routines can be tricky, but there are several options:
    • Compile time debug/release mode
    • Try/Catch/Finally code blocks (C#)
      • Code generated error messages
    • Debugging logging tables
    • Visual Studio/Other Debugging Tools
sql clr advanced
SQL CLR Advanced
  • User defined aggregates
  • TransactionScope
  • Triggers
  • Method Attributes
resources and q a
Resources and Q&A
  • Pro. SQL Server 2005 CLR Programming
    • Derek Comingore, Douglas Hinson (WROX)
    • $33 on Amazon.com
  • SQL OS: blogs.msdn.com/slavao/
  • CLR: blogs.msdn.com/sqlclr/
    • blogs.msdn.com/sqlprogrammability/
  • RegExMatch: http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx