1 / 18

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. Intro to Microsoft SQL 2005 CLR Supported Programming Languages When to use T-SQL vs. SQL CLR Demo: Hello World Security & Performance

amena
Download Presentation

SQL CLR – May 15, 2007

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL CLR – May 15, 2007 • Tony AnzelmoSQL 2005 • Microsoft.Net Common Language Runtime • C#, VB.Net, C++ (Safe Compilation) • UDF, UDA, UDT, USP

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

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

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

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

  6. .Net namespaces • SQL CLR .Net Namespaces • System.Data • System.Data.Sql • System.Data.SqlTypes • Microsoft.SqlServer.Server

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

  8. Enabling SQL CLR sp_configure ‘clr enabled’, 1 AND reconfigure

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

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

  11. Assembly DMVs • sys.assemblies • sys.assembly_files • sys.assembly_modules • sys.assembly_references • sys.module_assembly_usages

  12. 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)

  13. Performance Comparisons • Regular Expressions T-SQL vs. CLR

  14. 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; } }

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

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

  17. SQL CLR Advanced • User defined aggregates • TransactionScope • Triggers • Method Attributes

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

More Related