650 likes | 776 Views
SQLCLR Programming with SQL Server 2005. Andrew Novick. The Subject:. What is SQLCLR programming? Why would you use it? Why not to use it?. Introduction – Andrew Novick. Novick Software Business Application Development SQL Server and .Net specialization www.NovickSoftware.com Books:
E N D
SQLCLR Programming with SQL Server 2005 Andrew Novick SQLCLR Programming with SQL Server 2005 Andrew Novick
The Subject: • What is SQLCLR programming? • Why would you use it? • Why not to use it? SQLCLR Programming with SQL Server 2005 Andrew Novick
Introduction – Andrew Novick • Novick Software • Business Application Development • SQL Server and .Net specialization • www.NovickSoftware.com • Books: • Transact-SQL UDFs • SQL 2000 XML Distilled SQLCLR Programming with SQL Server 2005 Andrew Novick
Authorship This presentation is based in part on material developed for the Mini Code-Camp “Programming SQL Server: From 2000 to 2005” By Andrew Novick and Adam Machanic Adam’s Web site is: http://www.datamanipulation.net Andrew Novick’s Web Site is: http://www.NovickSoftware.com SQLCLR Programming with SQL Server 2005 Andrew Novick
What Is SQLCLR? • Writing code using .Net Languages that runs inside SQL Server. • User-Defined Functions • Stored Procedures • Aggregates • User-Defined Types • Triggers • SSIS Packages • Expressions and Data Providers in Reports SQLCLR Programming with SQL Server 2005 Andrew Novick
Why Use .Net in S2K5 • Replace Extended SP’s with a safer alternative • Replace COM automation: sp_OA_* • Take advantage of .Net Framework classes • Run computationally intense algorithms • Reuse code • Programmer productivity • Use new capabilities not available in T-SQL • User Defined Types • User Defined Aggregates SQLCLR Programming with SQL Server 2005 Andrew Novick
Hosting the CLR • S2K5 is one of many CLR hosts • Such as: ASP.Net, WinForms • Goals for CLR hosting: • Security • Reliability • Performance SQLCLR Programming with SQL Server 2005 Andrew Novick
SQLCLR Overall Architecture • Runtime hosted within SQL Server’s process space • “OS” layer provided by SQL Server’s scheduler, SQLOS • Memory • Processor • IO • Hosting enabled on a per-server basis SQLCLR Programming with SQL Server 2005 Andrew Novick
SQLCLR AppDomains • App Domains created per database, per assembly owner • SQL Server will restart an app domain if it detects: • Deadlocks • Memory leaks • Hung threads • … And other abnormal conditions SQLCLR Programming with SQL Server 2005 Andrew Novick
CLR is Optional! • Disabled by default • You must turn it on to use it • SQL Server 2005 Surface Area Configuration • This script: EXEC sp_configure 'show advanced options' , '1';goreconfigure;goEXEC sp_configure 'clr enabled' , '1'goreconfigure; SQLCLR Programming with SQL Server 2005 Andrew Novick
Assemblies • .Net Code compiled into an IL DLL • Assemblies must be added to S2K5 with CREATE ASSEMBLY • Bits are stored in the database’s sys.assembly_files table CREATE ASSEMBLY my_assembly_name FROM ‘\\myserver\…path\MyAssembly.dll’ SQLCLR Programming with SQL Server 2005 Andrew Novick
Assemblies 2 • Viewing Assemblies • SQL Management Studio • Reflector with the SQL 2005 Browser SQLCLR Programming with SQL Server 2005 Andrew Novick
Assembly Security: PERMISSION_SET • SAFE • Allows data access, basic libraries • EXTERNAL_ACCESS • Allows access to IO libraries • UNSAFE • Allows access to unmanaged code SQLCLR Programming with SQL Server 2005 Andrew Novick
PERMISSION_SET: Safe • May not access external resources: registry, file system, or network • May not use unmanaged code or PInvoke • May access data using the current context but not via SQLClient or any other data provider • No thread processing SQLCLR Programming with SQL Server 2005 Andrew Novick
PERMISSION_SET: EXTERNAL_ACCESS • May access external resources:registry, file system, network, environment variables • May not use: • unmanaged code • PInvoke SQLCLR Programming with SQL Server 2005 Andrew Novick
PERMISSION_SET: UNSAFE • Can do anything… • Know your code! SQLCLR Programming with SQL Server 2005 Andrew Novick
SQLCLR User-Defined Functions SQLCLR Programming with SQL Server 2005 Andrew Novick
Overview of CLR UDFs • Two Types • Scalar • Table-Valued • Similar Rules • No side effects • More flexible • Can use the .Net Base Classes • Can use user created classes • Can execute Dynamic SQL • Performance • Similar to better than T-SQL SQLCLR Programming with SQL Server 2005 Andrew Novick
T-SQL or SQLCLR? • TSQL • Best for pure data access tasks. • SQLCLR • Low overhead for calling functions • Faster for math operations • Better for accessing external data SQLCLR Programming with SQL Server 2005 Andrew Novick
CLR Scalar Functions • Simple and Flexible • .Net Functions • Useful for encapsulating BCL functionality SQLCLR Programming with SQL Server 2005 Andrew Novick
CLR Table-Valued Functions • “Streaming”: Returns one row at time • Requires a method with: • Returns IEnumerable • SqlFunctionAttribute • A second method “shreds” the data • FillRowMethod • Columns defined by output parameters SQLCLR Programming with SQL Server 2005 Andrew Novick
CLR Table-Valued Function Lifecycle • SQL Server invokes IEnumerable method • Which returns a reference to the collection • SQL Server enumerates the collection • Enumerate() calls the FillRowMethod • Input object is mapped to output parameters by the SQLCLR Programming with SQL Server 2005 Andrew Novick
SQLCLR Stored ProceduresAnd Data Access SQLCLR Programming with SQL Server 2005 Andrew Novick
SQLCLR Stored Procedures • Can do everything that T-SQL sprocs can • Multiple result sets • Output Parms • Messages • Return Code • Can do what other SQLCLR objects can: • C# or VB.net code SQLCLR Programming with SQL Server 2005 Andrew Novick
The ADO.NET In-Proc Provider • Access to almost every ADO.NET object you know and love • No access to some ADO.NET features, such as MARS and asynchronous queries • All of this is possible via the “context connection”: SqlConnection conn = new SqlConnection(“context connection=true”); SQLCLR Programming with SQL Server 2005 Andrew Novick
Reading Data • DataSet • Disconnected • Implements IEnumerable • Easy to use in a table-valued UDF; not easy to use in a stored procedure • DataReader • Connected (Streaming) • Directly supported by SqlPipe • Easy to use in a stored procedure; not easy to use in a table-valued UDF SQLCLR Programming with SQL Server 2005 Andrew Novick
The Same Rules Apply..! • ADO.NET in-process provider makes it easy to move code between tiers • Best practice in the application tier: • Use T-SQL stored procedures to encapsulate database data access • Best practice in the data tier (CLR stored procedure or UDF): • Use T-SQL stored procedures to encapsulate database data access SQLCLR Programming with SQL Server 2005 Andrew Novick
ADO.NET: Sending Back Data • SqlPipe • Send() • Result Sets (SqlDataReader) • Rows (SqlDataRecord) • Messages (System.String) • ExecuteAndSend() • Executes SqlCommand in the connected context • Should probably be called SendAndExecute() SQLCLR Programming with SQL Server 2005 Andrew Novick
CLR Stored Procedures • Best for: • External Data Sources • Multiple output parameters from external data sources • complex math/logic • Not indicated for • single result sets • single scalar output SQLCLR Programming with SQL Server 2005 Andrew Novick
SQLCLRUser Defined Aggregates SQLCLR Programming with SQL Server 2005 Andrew Novick
User Defined Aggregates • Aggregates scalar values into another scalar • Uses the SqlUserDefinedAggregate attribute CREATE AGGREGATE [Product](@Value float) Returns [float] EXTERNAL NAME [SampleAggregate].[SampleAggregate.Product]goGRANT EXEC ON dbo.Product TO PUBLICgo SQLCLR Programming with SQL Server 2005 Andrew Novick
Possible UDAGGs • Product • CountOfNulls • BitwiseOr • Concatenation • RegExMatchCount SQLCLR Programming with SQL Server 2005 Andrew Novick
Impossible UDAGG • First • Last • Impossible due to possible Parallelism SQLCLR Programming with SQL Server 2005 Andrew Novick
User Defined Aggregate Class public class myAggregate { public void Accumulate (<input-type> value) { } public <return-type> Terminate() { } public void Init () { } public void Merge (myAggregate) { } } SQLCLR Programming with SQL Server 2005 Andrew Novick
UDAGG: Init • Initialize the aggregate • Reinitializes the when SQL Server chooses to reuse it. Public Sub Init() m_Accumulator = 1.0 m_HasNonNull = False End Sub SQLCLR Programming with SQL Server 2005 Andrew Novick
UDAGG: Accumulate • Called once per row to be aggregated. Public Sub Accumulate(ByVal value As SqlDouble) if value.isnull then exit sub m_Accumulator *= value m_HasNonNull = True End Sub SQLCLR Programming with SQL Server 2005 Andrew Novick
UDAGG: Merge • SQL Server may use multiple threads for an aggregation. • Each thread would need it’s own instance of the UDAGG class. • Results combined by the Merge method. SQLCLR Programming with SQL Server 2005 Andrew Novick
UDAGG: Merge Public Sub Merge(ByVal value As Product) if value is nothing then exit sub if not value.m_HasNonNull then exit sub m_Accumulator *= value.m_Accumulator m_HasNonNull = True End Sub SQLCLR Programming with SQL Server 2005 Andrew Novick
UDAGG: Terminate • Terminate method returns the results. Public Function Terminate() As SqlDouble if not m_HasNonNull then return sqldouble.null else Return m_Accumulator end if End Function SQLCLR Programming with SQL Server 2005 Andrew Novick
Using the User Defined Aggregate SELECT dbo.product(sample) [Three integers] FROM ( SELECT CAST(1.0 as float) as Sample UNION ALL SELECT 3 UNION ALL SELECT 5 ) Numbers (Result) Three integers --------------- 15 SQLCLR Programming with SQL Server 2005 Andrew Novick
Best Practices: UDAGG • Consider T-SQL alternatives • Use SQLTypes • Code efficiently • No SQL input if you can help it. • If you need data, create a cache. • Remember Parallel execution SQLCLR Programming with SQL Server 2005 Andrew Novick
User-Defined Types UDT SQLCLR Programming with SQL Server 2005 Andrew Novick
Two Types of UDTs • Alias Types • CLR Types • Both use the CREATE TYPE statement SQLCLR Programming with SQL Server 2005 Andrew Novick
Alias User Defined Types • Alias Types • These give an alternate name to a SQL type • Have existed in SQL Server since Sybase • Example: CREATE TYPE CompletionCODE FROM CHAR(2) NOT NULL SQLCLR Programming with SQL Server 2005 Andrew Novick
CLR User-Defined Types (UDTs) • Created with the .Net CLR in VB.Net or C# • New to SQL Server 2005 • Example: CREATE Assembly myAssembly FROM ‘\\server\share\myAssembly.dll Go CREATE TYPE myType EXTERNAL NAME myAssembly.[myAssembly.myType] go SQLCLR Programming with SQL Server 2005 Andrew Novick
Why CLR User-Defined Types • Encapsulate application logic and data in the database. • Benefits: • Programmer productivity • Consistency over the long term SQLCLR Programming with SQL Server 2005 Andrew Novick
Steps to create a CLR UDT • Create a Class in a .Net language (VB or C#) • Create the ASSEMBLY database object • CREATE TYPE statement SQLCLR Programming with SQL Server 2005 Andrew Novick
CLASS or Structure • Class is a reference type • Structure is a Value type SQLCLR Programming with SQL Server 2005 Andrew Novick
Create your CLASS Required attributes: <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> Required Interface: INullable Required Methods: ToString Parse SQLCLR Programming with SQL Server 2005 Andrew Novick
Your Class • Public and Private fields • Static (Shared in VB) and instance members SQLCLR Programming with SQL Server 2005 Andrew Novick