Sql clr may 15 2007
Download
1 / 18

SQL CLR – May 15, 2007 - PowerPoint PPT Presentation


  • 70 Views
  • 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

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

PowerPoint Slideshow about ' SQL CLR – May 15, 2007' - shelby-boyle


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)

  • UDF, UDA, UDT, USP


Agenda
Agenda

  • 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

Aggregates

Functions (Scalar & Table)

Triggers (DDL & DML)

Types

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

--deploy.sql

use Playground

GO

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

drop procedure HelloWorld

GO

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

drop assembly SQLAssembly

GO

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

GO

create procedure dbo.HelloWorld

as external name SQLAssembly.SQLLibrary.HelloWorld

GO

exec dbo.HelloWorld

GO

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


Security
Security

  • 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

USE PLAYGROUND

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

GO


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


ad