Transcript SQL Server 2005 -CLR
SQL Server 2005
The Common Language Runtime (CLR) Integration
Presented by Tarek Ghazali IT Technical Specialist Microsoft SQL Server MVP Web Development MCP LebDev Vice President
© 2006 Tarek Ghazali. All rights reserved.
CLR Integration
CLR 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
CLR Integration
CLR Diagram Thread Support Type Checker Security Engine MSIL to Native Compilers (JIT) Code Manager COM Marshaler Exception Manager Debug Engine Garbage Collector (GC) Class Loader
CLR Integration
SQL 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
CLR Integration
Deep Integration with the Database
SQL Engine CLR Hosting Layer SQL OS Layer Windows OS
CLR Hosting layer provides coordination – Assembly Loading – Memory management – Security Model – Reliability – Threads & Fibers – Deadlock detection – Execution context
The Developer Experience
VB,C#,C++ VS .NET Project Build Assembly: “TaxLib.dll” Runtime hosted by SQL (in-proc) SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … SQL Queries: select sum( tax(sal,state) = ‘King’ ) from Emp where county SQL Server
CLR Integration
Available 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, …
CLR Integration
SQL 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
CLR Integration
The System.Data.SqlServer Namespace Class
SqlContext SqlConnection SqlCommand SqlParameter SqlPipe SqlDataReader SqlResultSet SqlTransaction SqlTriggerContext
Description
Provides access to other objects, like a connection An open connection to a SQL Server database Used to send a command to the database server Supplies a parameter for a SqlCommand object Used to send results or information to the client Reads the data one row at a time, forward only For working with flexible server-side cursors For providing transactional behavior Provides information about the trigger action
CLR Integration
Registering 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_nam e' – < assembly_bits > :: = { varbinary_literal | varbinary_expression }
CLR Integration
Assembly 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)
CLR Integration
Meta Data of Assemblies
Details of Assembly: Sys.assemblies
Assembly source code: Sys.assembly_files
Assembly references: Sys.assembly_references
Other meta data information •SYS.OBJECTS •SYS.ASSEMBLY_MODULES •SYS.ASSEMBLY_TYPES
CLR Integration
User 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
CLR Integration
User Defined Functions -Example
public class MyFunctions { [SqlFunction] public static SqlString GetLongDate(SqlDateTime DateVal) { // Return the date as a long string return DateVal.Value.ToLongDateString(); } }
CLR Integration
User 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. )
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
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
CLR Integration
Stored 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); } }
CLR Integration
Create Sql Server Proc.
Syntax : create procedure ProcName as external name
CLR Integration
Triggers
public class ContactCode { [SqlTrigger(Name="ContactUpdTrg", Target="Person.Contact", Event="FOR UPDATE")] public static void ChangeEmail() {SqlTriggerContext trg = SqlContext.GetTriggerContext();
DEMO
CLR Integration
When 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
CLR Integration
Feature Comparison with T-SQL T-SQL X CLR X User Defined Functions Stored Procedures Triggers User Defined Types Aggregates X X X X X X
CLR Integration
Best 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
SQLCLR Guidance
Mid 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
Resources & Questions
Microsoft Resources: – –
msdn.microsoft.com/sqlserver/ www.microsoft.com/sql/community
Contact me: – – [email protected]
www.sqlmvp.com (will be available soon) Download Presentation : – www.lebdev.net