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
Time
Topic
1
Tuesday
06-03-2012
18:00 - 21:00
SQL Server 2008 Management Studio
2
Tuesday
13-03-2012
18:00 - 21:00
T-SQL Enhancements
3
Tuesday
20-03-2012
18:00 - 21:00
High Availability
4
Tuesday
27-03-2012
18:00 - 21:00
CLR Integration
5
Tuesday
03-04-2012
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
Common
Language
Runtime
= 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.
Enable CLR
2.
Create a new database project in Visual Studio
3.
Create a new stored procedure in Visual Studio
4.
Connect to current context using “Context Connection=true”
5.
Add a simple SELECT statement
6.
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.
Create IsValidPostCode in C# (.NET)
2.
Deploy it to SQL Server
3.
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<context.ColumnCount; columnNumber++)
{
SqlContext.Pipe.Send(string.Format("Col: {0} = {1}", columnNumber,
reader[columnNumber].ToString()));
}
};
reader.Close();
}
}
}
What you do
1.
Create EmailTrigger in C# (.NET)
2.
Deploy it to SQL Server
3.
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?
Do I need to do set-based operations such as pivoting?
.NET code and SQLCLR
Are my developers SQL gurus but .NET newbies?
T-SQL
Do I need to do extensive computation or custom algorithms?
.NET code and SQLCLR
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…
[email protected]
http://ericphan.info
twitter.com/ericphan
3 things…
[email protected]
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