Transcript An Introduction To CLR Integration in SQL Server 2005 (Yukon)

An Introduction To CLR
Integration in SQL Server
2005 (Yukon)
Dr Greg Low
Who Am I?
Director of White Bear Consulting
Director of Lowell Computing
Microsoft MVP for .NET
INETA User Group Relations Chair for Asia-Pacific
President of Qld MSDN User Group
President of Qld SQL Server User Group
What we will cover:
Why have CLR Integration? (Is T-SQL Dead?)
Dealing with Assemblies
Example Assemblies
Scalar-Valued Functions
Table-Valued Functions
Stored Procedures
Triggers
User-defined Data Types
User-defined Aggregates
Management Issues
Conclusion
Versions Used
This material is based on the Beta 1 Refresh of
Whidbey (September CTP) and Beta 2 of SQL
Server 2005 (Yukon).
Source material for the CLR examples is
summarised and updated from the MSDN
article:
Overview of .NET Programming Features in SQL
Server "Yukon" Beta 1
http://msdn.microsoft.com/library/default.asp?url=
/library/enus/dnsql90/html/sql_ovyukonnetprogfeatures.asp
Is T-SQL Dead?
T-SQL remains the language of choice for dataintensive operations
But, there are many things it’s not great at
doing…
Splitting Strings T-SQL Style
Splitting Strings VB Style
Check Constraint
Validating Email Address Format
T-SQL vs Managed Code
T-SQL best for data access with minimal
procedural logic
Managed code best for CPU intensive
operations or complex logic (or accessing base
class library)
Decision is needed regarding client vs server
regarding use of processing power.
CLR Integration Basics
CLR integrated into the database
SQL Server acts as the host for the CLR
Any .NET language ok (VB & C# most common)
Stored Procedures, Triggers & User-Defined
Functions can now be in assemblies
Two new object types:
User-defined type (no longer just a sub-type)
User-defined aggregate
Why Have CLR Integration?
Why CLR?
Type safety, garbage collection, rich class library,
exception handling, thread management
Loaded on first execution of a .NET assembly
User-defined types (classes) and aggregates
Common IDE environment
Higher performance in some situations
Rich programming model
Arrays, collections, loops, encapsulation,
inheritance, polymorphism, etc.
.NET code access security model
Why CLR Integration (continued)
Namespaces help organise code assets
Managed code better for number-crunching
tasks (plus string handling, regular expressions,
file access, cryptography, etc.)
Classes that don't make sense (eg WinForms)
are not available
Dealing With Assemblies
What is an assembly?
Only DLLs can be registered
Register via CREATE ASSEMBLY statement:
CREATE ASSEMBLY MyCLR FROM
'C:\MyApp\MyCLR.DLL'
Unregister via DROP ASSEMBLY statement:
DROP ASSEMBLY MyCLR
Dealing With Assemblies (cont)
Data and code (ie assemblies) owned by a user
are isolated from those owned by another user
unless access is granted.
Permission can be given to create an assembly.
The owner can then assign permission to
reference the assembly.
Calling chains (assembly calls assembly) can be
an issue. Must be in same database and with
appropriate permissions.
Dealing With Assemblies (cont)
3 security levels for assemblies (can be
specified during CREATE as WITH
PERMISSION_SET = SAFE|EXTERNAL
ACCESS|UNSAFE)
Default is SAFE
EXTERNAL ACCESS allows network, registry, file
system, environment variables
UNSAFE is unrestricted (eg Win32 API)
Example Assemblies
Scalar-valued User-defined Functions, Tablevalued User-defined Functions, User-Defined
Procedures and User-defined Triggers
Inproc Provider is contained in
System.Data.SqlServer (similar to
System.Data.SqlClient but optimised for working
with data inside the SQL Server process)
Requires a reference to sqlaccess.dll
3 new objects: SqlContext, SqlPipe and
SqlTriggerContext
Scalar-Valued Functions Demo
Notes:
DataAccessKind.None allows higher
optimisation
Microsoft.VisualStudio.DataTools.SqlAttributes
reference is required for previous beta
T-SQL assembly name must match the dll name
(maybe…)
T-SQL function name does not have to match
the target method's name
VB namespace makes the syntax more
challenging
Further Function Example
Much more reasonable use of CLR integration
Consider what would be involved in T-SQL to
accomplish the same
Table-Valued Functions
UDF that returns a table
Data is returned through an ISqlReader
Declarations are similar to Scalar
CREATE FUNCTION SomeFunction
RETURNS @SomeTable TABLE (AColumn
INT ,AnotherColumn VARCHAR(35))
EXTERNAL NAME
SomeAssembly.SomeNamespaceOrClass
.SomeFunction
Stored Procedures
Can return tabular results and messages,
invoke DDL and DML and return parameters.
SqlPipe object is used to return tabular results
and messages
SqlPipe has an overloaded Send() method
SqlPipe sp = SqlContect.GetPipe();
SqlDataReader SomeReader =
cmSQL.ExecuteReader();
sp.Send(SomeReader);
Stored Procedures (cont)
[SQLProcedure] attribute on method
Register by:
CREATE PROCEDURE MyProc AS EXTERNAL
NAME
MyAssembly.MyNamespaceOrClass.MyP
roc
Triggers
Similar to previous code using SqlPipe but
access to INSERTED and DELETED tables as in
T-SQL triggers.
Dim TriggerContext As SqlTriggerContext =
SqlContext.GetTriggerContext()
Dim TriggerPipe As SqlPipe =
SqlContext.GetPipe()
If TriggerContext.TriggerAction =
TriggerAction.Insert Then
…
Register via:
CREATE TRIGGER MyTrigger ON MyTable FOR INSERT
AS EXTERNAL NAME
SomeAssembly.SomeNamespace.MyTrigger
User-Defined Data Types
Now possible to build a CLR-based class and
use it in a CREATE TYPE statement.
Rules apply:
Must be Serializable
Must Have SqlUserDefinedTypeAttribute
Should implement INullable (ie should be NULL
aware)
Must have a public constructor with no arguments
Should support conversion to/from a string via
ToString() and Parse()
User-Defined Types Example
SqlUserDefinedTypeAttribute properties:
MaxByteSize (maximum size in bytes)
IsFixedLength (are all instances the same length)
IsByteOrdered (can binary representation be used in
comparisons)
Format (Native, UserDefined,
SerializedDataWithMetaData)
User-Defined Aggregate Example
Totally new object type for SQL Server
Four methods required:
Init()
Accumulate()
Merge()
Terminate()
SqlUserDefinedAggregateAttribute
IsInvariantToDuplicates (MAX & MIN vs SUM)
IsInvariantToNulls (MIN & SUM vs COUNT)
IsInvariantToOrder
Management & Design Issues
How does an assembly get to the server? (file
system vs streaming)
CLR versioning issues (& publishing policy)
Normalisation ????
Conclusions
Stunning new set of capabilities
Many DBA's very scared…
Lots of design decisions to be considered
For More Information
SQL Server DBA’s Guide To The .NET Framework
And CLR Integration
(coming soon). Send an email to
[email protected] for prerelease info
Overview of .NET Programming Features in SQL
Server "Yukon" Beta 1
MSDN Library
http://msdn.microsoft.com/library/default.asp?url=
/library/enus/dnsql90/html/sql_ovyukonnetprogfeatures.asp
Thanks for listening!
[email protected]
[email protected]