SQL Server 2005 The Common Language Runtime (CLR) Integration

1. SQL Server 2005The Common Language Runtime (CLR) Integration

2. CLR IntegrationCLR Introduction • Highlights • Common type system (CTS) • Mapping of data types. Programming language  Framework • Just-in-time (JIT) compilers • JIT compiles intermediary language (MSIL) into native code • Highly optimized for platform or device • Garbage collector • Permission and policy-based security • Exceptions • Threading • Diagnostics and profiling

3. Thread Support COM Marshaler Type Checker Exception Manager Security Engine Debug Engine MSIL to Native Compilers (JIT) Code Manager Garbage Collector (GC) CLR IntegrationCLR Diagram • Common Language Runtime Diagram Base Class Library Support Class Loader

4. CLR IntegrationSQL Server 2005 – CLR • Run managed code within a database by using in-process assemblies • Create managed stored procedures, triggers, user-defined functions, user-defined types, and aggregates • Integration benefits: • Enhanced programming model • Enhanced safety and security • Common development environment • Performance and scalability

5. SQL Engine CLR Hosting Layer SQL OS Layer Windows OS CLR IntegrationDeep Integration with the Database • CLR Hosting layer provides coordination • Assembly Loading • Memory management • Security Model • Reliability • Threads & Fibers • Deadlock detection • Execution context

6. Build VB,C#,C++ Assembly: “TaxLib.dll” SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … SQL Queries: select sum(tax(sal,state)) from Emp where county = ‘King’ SQL Server The Developer Experience VS .NET Project Runtime hosted by SQL (in-proc)

7. CLR IntegrationAvailable Classes • Even in supported assemblies, some APIs are not available in SQL • Environment.Exit(), Console, etc. • Potentially unreliable constructs disabled • No thread creation • No shared state or synchronization • No listening on sockets in server • No finalizers • Eliminate functionality N/A to database • System.Windows.Forms • System.Drawing • System.Web, …

8. CLR IntegrationSQL Server Projects in Visual Studio 2005 • Project for creating managed database objects • Automatically includes necessary references • System • System.Data.dll • Includes templates for each object type • Stored procedure • Trigger • User-defined function • User-defined type • Aggregate • Allows immediate deployment and debugging

9. CLR IntegrationThe System.Data.SqlServer Namespace

10. CLR IntegrationRegistering an assembly • CREATE ASSEMBLY assembly_name • [ AUTHORIZATION owner_name ] • FROM { < client_assembly_specifier > | < assembly_bits > [,...n] } • [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ] • < client_assembly_specifier > :: = • '[\\machine_name\]share_name\[path\]manifest_file_name' • < assembly_bits > :: = • { varbinary_literal | varbinary_expression }

11. CLR IntegrationAssembly Security -PERMISSION_SET • SAFE • May not access external resources: registry, file system, or network • May access data using the current context but not via SQLClient or any other data provider • No thread processing • EXTERNAL_ACCESS • May access external resources:registry, file system, network, environment variables • UNSAFE • May access external resources • Can use SQLClient and other data providers • Can use thread constructs • (No restrictions; similar to extended stored procedures)

12. CLR IntegrationMeta Data of Assemblies Create Assembly Assembly source code: Sys.assembly_files Assembly references: Sys.assembly_references Details of Assembly: Sys.assemblies Other meta data information • SYS.OBJECTS • SYS.ASSEMBLY_MODULES • SYS.ASSEMBLY_TYPES

13. CLR IntegrationUser Defined Functions • Similar to T-SQL function • Written in CLR language • Decorated with [SqlFunction] attribute in code • Assembly loaded into the database • Function defined from assembly • Limits on functions • must be in public class • cannot be in nested class • method must be public and static

14. CLR IntegrationUser Defined Functions -Example public class MyFunctions { [SqlFunction] public static SqlString GetLongDate(SqlDateTime DateVal) { // Return the date as a long string return DateVal.Value.ToLongDateString(); } }

15. CLR IntegrationUser Defined Functions • Properties have impact on whether or not computed column that use these functions can be indexed. • IsDeterministic = true (it always produces the same output values given the same input values and the same database state.) • DataAccess • DataAccessKind.None: Does not access data. • DataAccessKind.Read: Only reads data. • SystemDataAccess • SystemDataAccessKind.None: Does not access system data. • SystemDataAccessKind.Read: Only reads system data. • IsPrecise = { true | false } (that indicates whether the routine involves imprecise computations such as floating point operations. )

16. CLR Integration.Net Stored Procedures (1) • Capable of doing everything a T-SQL proc can do. • Uses a Shared method (static in C#) • Pass parameters both ways • OUTPUT parameters should be byref (ref in C#) • Return multiple result sets

17. CLR Integration.Net Stored Proc Can Return (2) • Numeric return code • Count of rows affected by the command • Scalar value • Single row • One or more multi row result sets • A stream of XML

18. CLR IntegrationStored Procedure public class ContactCode { [SqlProcedure] public static void GetContactNames() { SqlCommand cmd = ……. …… cmd.CommandText = "SELECT FirstName + ' ' + LastName" + • " AS [Name] FROM Person.Contact"; SqlDataReader rdr = cmd.ExecuteReader(); SqlPipe sp = …………..; sp.Send(rdr); } }

19. CLR IntegrationCreate Sql Server Proc. Syntax : create procedure ProcName as external name <assemblyname>.<classname>.<methodname> Example : create procedure GetContactsName as external name assemblyname.ContactCode. GetContactNames

20. CLR IntegrationTriggers public class ContactCode { [SqlTrigger(Name="ContactUpdTrg", Target="Person.Contact", Event="FOR UPDATE")] public static void ChangeEmail() {SqlTriggerContext trg = SqlContext.GetTriggerContext();

21. CLR IntegrationWhen to use T-SQL • T-SQL better used for data access • All pre-SQL Server 2005 code is written in T-SQL • SQL Server 2005 adds exception handling to T-SQL • T-SQL can be faster for data access • Direct access to SQL Server's internal buffers • Rich, data-centric library of functions • No conversion of types

22. CLR IntegrationFeature Comparison with T-SQL

23. CLR IntegrationBest uses of SQLCLR • Computational functions are always faster • Streaming table valued functions • User defined aggregates • Orders magnitude faster than server or client cursor solutions • Scalar functions • Function body is compiled to native code • Use managed code for: • Procedures that feature complex logic • Access to the .NET Framework class library • CPU intensive functions

24. SQLCLR GuidanceMid Tier vs. Data Tier • SQLCLR support does not mean move all business logic to server • Candidates for moving to server • Centralized data validation • Process large amount of data while needing a small portion of it for application use