SQL CLR

Download Report

Transcript SQL CLR

SQL Server 2008 for Developers

UTS Short Course

Course Website

 Course Timetable & Materials  http://www.ssw.com.au/ssw/Events/2010UTSSQL/  Resources  http://sharepoint.ssw.com.au/Training/UTSSQL/

Course Overview

Session Date

1 2 3 4 5 Tuesday 06-03-2012 Tuesday 13-03-2012 Tuesday 20-03-2012 Tuesday 27-03-2012 Tuesday 03-04-2012

Time Topic

18:00 - 21:00 SQL Server 2008 Management Studio 18:00 - 21:00 T-SQL Enhancements 18:00 - 21:00 High Availability 18:00 - 21:00 CLR Integration 18:00 - 21:00 Full-Text Search

What we did last week High availability

  ?

What can go wrong? What can we do?

 Implementing Database Snapshots  Configuring a Database Mirror  Partitioned Tables  SQL Agent Proxies  Performing Online Index Operations  Mirrored Backups

SQL CLR Integration

Agenda - CLR Integration

        What is .NET?

What is CLR Integration?

Requirements on SQL box Samples Internals CLR Integration: Pros CLR Integration: Cons Real world - When to use CLR Integration

What is .NET?

 An application development platform from Microsoft  Tools, Languages, Runtime (Virtual machine), IDE, …  Rapidly develop secure and robust software  Web and Windows  Full support for object-oriented programming

.NET Overview

 IL = Intermediate Language  CLR = Runtime

 CLR   

C

ommon

L

anguage

R

untime  = Virtual machine

.NET Framework

 Evolution  The whole .NET FX  http://shrinkster.com/1515 (PDF Poster)

What is CLR Integration?

 Lets you write your database queries using .NET

 Create and debug using VS 2008 IDE  Brand new in SQL 2005 (Standard and Express)  Support for large UDT (User defined types) in SQL 2008 (up to 2GB)  Support for multiple inputs on UDA (User defined aggregators)   e.g. string concatenator that takes in a column and separator char Any .NET language (C#, VB, C++)

SQL CLR Project

CLR Integration

      You can do the same thing as SQL Server using .NET code Stored Procedures Triggers User-defined functions User-defined types Aggregate functions

Server - Enabling CLR Integration

 Enabled on an instance (not per database)  SQL Script   Execute sp_configure ‘clr enabled’, ‘1’ reconfigure

Stored Procedures

Sample

public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void HelloWorld() { // Put your code here using (SqlConnection cn = new SqlConnection("

Context Connection=true

")) { cn.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM DEMO", cn); } } SqlContext.Pipe.Send(cmd.ExecuteReader());

What you do

1.

2.

3.

4.

5.

6.

Enable CLR Create a new database project in Visual Studio Create a new stored procedure in Visual Studio Connect to current context using “Context Connection=true” Add a simple SELECT statement Deploy and run it

Internals

    Assembly collated as set of files   Stored within SQL Server system tables Assembly, references, program database (pdb), source files Deployed to SQL Server • • Manually Catalogued with CREATE ASSEMBLY Dropped with DROP ASSEMBLY • Automatically Deployed from VS 2008

Security Levels

  Safe (default) Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop.

  External_Access Access is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code.

  Unsafe Access is not limited whatsoever. User-defined types

Custom CLR Functions

Sample

public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static bool IsValidPostCode(string postcode) { return System.Text.RegularExpressions.Regex.IsMatch( postcode, ^(((2|8|9)\d{2})|((02|08|09)\d{2})|([1-9]\d{3}))$"); } };

What you do

1.

2.

3.

Create

IsValidPostCode

in C# (.NET) Deploy it to SQL Server Run it

Stored procedure vs. Function

 What is the difference?

 Function    Base functionality Independent of Database itself Stored procedure   Many operations at once Normally database specific

Triggers

Sample

public partial class Triggers { [Microsoft.SqlServer.Server.SqlTrigger(Name = "EmailTrigger", Target = "

Customers

", Event = "

FOR UPDATE

")] public static void SalaryFraudTrigger() { SqlTriggerContext context = SqlContext.TriggerContext; using (SqlConnectioncnn = new SqlConnection("context connection=true")) { cnn.Open(); SqlCommand command = cnn.CreateCommand(); command.CommandText = "SELECT * FROM

inserted

"; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { for (intcolumnNumber = 0; columnNumber

What you do

1.

2.

3.

Create

EmailTrigger

in C# (.NET) Deploy it to SQL Server Test it

CLR Integration:

Pros (Continued)   Take advantage of the powerful .NET Framework    .NET is a full-featured programming language Supports things like “for each” loops, arrays, collections Object Oriented programming model to organise your queries • • • • Obtaining data from external resources The File System The Event Log A Web Service The Registry

CLR Integration:

Pros       For complex calculations Parsing strings (like the regular expression code) • • • User-defined types Date, time, currency, and extended numeric types Geospatial applications Encoded or encrypted data (see books online) User-defined aggregates Powerful Intellisense and debugging  Generally faster E.g. CLR aggregate 100x faster than cursor

CLR Integration:

Cons (Continued)       NON MAINSTREAM Lots of programming for simple operations Some overhead in communicating with assemblies Remember – T-SQL is designed and optimised for data, use it!

Not useful if your guys do not know any .NET

 Potentially costly to rewrite logic Companies (including us) have invested a lot in T-SQL

CLR Integration:

Cons      There are some restrictions to observe when calling between T-SQL and SQL-CLR.

You must only use T-SQL supported data types (No streams) You can't use inheritance or polymorphism • .NET cannot easily represent either VARCHAR or TIMESTAMP .NET strings are Unicode, the equivalent of NVARCHAR The CLR decimal type is not the same as SQL_DECIMAL

When to use CLR Integration

     Do I need to manipulate data before it is displayed?

 .NET code and SQLCLR Do I need to do set-based operations such as pivoting?

 T-SQL Do I need to do extensive computation or custom algorithms?  .NET code and SQLCLR Are my developers SQL gurus but .NET newbies?

 T-SQL Do I have loads of stored procs that are becoming hard to manage?

 .NET code and SQLCLR

CLR Integration

• •

The Bottom Line

Use T-SQL for all data operations Use CLR assemblies for any complex calculations and transformations

Quick tips

 SQL Management Studio

Shortcuts

  Ctrl + L – Display query execution plan F5 – Run/Show result grid  Rules for SQL Server http://www.ssw.com.au/SSW/Standards/default.aspx

 SQL Server Cheat sheet http://www.pinaldave.com/sql-download/SQLServerCheatSheet.pdf

http://www.addedbytes.com/cheat-sheets/sql-server-cheat-sheet/

Session 4 Lab

 CLR Integration Download from Course Materials Site (to copy/paste scripts) or type manually: http://tinyurl.com/utssql2009

3 things …

 EricPhan @ssw.com.au

 http:// ericphan.info

 twitter.com/ ericphan

3 things …

 mehmet @ssw.com.au

 http:// blog.ozdemir.id.au

 twitter.com/ mozdemir_au

Thank You!

Gateway Court Suite 10 81 - 91 Military Road Neutral Bay, Sydney NSW 2089 AUSTRALIA ABN: 21 069 371 900 Phone: + 61 2 9953 3000 Fax: + 61 2 9953 3105 [email protected]

www.ssw.com.au