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

1. An Introduction To CLR Integration in SQL Server 2005 (Yukon) Dr Greg Low

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

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

4. 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/en-us/dnsql90/html/sql_ovyukonnetprogfeatures.asp

5. Is T-SQL Dead? • T-SQL remains the language of choice for data-intensive 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

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

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

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

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

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

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

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

13. Example Assemblies • Scalar-valued User-defined Functions, Table-valued 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

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

15. Further Function Example • Much more reasonable use of CLR integration • Consider what would be involved in T-SQL to accomplish the same

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

17. 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);

18. Stored Procedures (cont) • [SQLProcedure] attribute on method • Register by: CREATE PROCEDURE MyProc AS EXTERNAL NAME MyAssembly.MyNamespaceOrClass.MyProc

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

20. 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()

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

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

23. Management & Design Issues • How does an assembly get to the server? (file system vs streaming) • CLR versioning issues (& publishing policy) • Normalisation ????

24. Conclusions • Stunning new set of capabilities • Many DBA's very scared… • Lots of design decisions to be considered

25. For More Information • SQL Server DBA’s Guide To The .NET Framework And CLR Integration • (coming soon). Send an email to greg@whitebearconsulting.com 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/en-us/dnsql90/html/sql_ovyukonnetprogfeatures.asp

26. Thanks for listening! greglow@lowell.com.au greg@whitebearconsulting.com