You Can Do Anything If You Think Yukon

You Can Do Anything If You Think “Yukon” Presented by: Richard Broida Senior Architect Bennett Adelson

Agenda • Overview of SQL Server 2005 • Enhancements to Database Engine and T-SQL • CLR Hosting – Writing CLR functions, procedures and triggers – Creating user-defined data types and aggregates • Resources • Q&A Bennett Adelson

History of Microsoft SQL Server • • 1988 1993 1994 1995 1996 1997 1998 2000 – – – – MS/Sybase version for OS/2 4. 21 for NT 3. 1 MS/Sybase partnership ends 6. 0, major rewrite 6. 5 Enterprise Edition 7. 0, total rewrite 2000 Bennett Adelson

SQL Server 2000 Has Lived to See … • • • Windows XP and Windows 2003 Server. NET Framework 1. 0 and 1. 1 Biz. Talk 2000, 2002 and 2004 Commerce Server 2000 and 2002 CMS 2000 and 2003 Share. Point 2001 and 2003 Exchange Server 2000 and 2003 Oracle 9 i and 10 g DB 2 8. 1 and 8. 2 Bennett Adelson

New Features in SQL Server 2005 • • • SQLCLR XML as Native Data Type Hosting Web Services Enhancements to T-SQL Client API Enhancements Service Broker Notification Services Enhanced Reporting Services Enhanced Analysis Services Bennett Adelson

SQL Server 2005 Editions • Express Edition – Replaces MSCE – Freely downloadable and redistributable • Workgroup Edition – Less expensive than Standard • Standard Edition • Enterprise Edition • Developer Edition Bennett Adelson

Where is It Now? • First Technology Preview released at PDC in October 2003 • Betas 1 and 2 Released in 2004 • Most current version on MSDN is February 2005 Community Technology Preview • A “Beta 3” was announced for April 2005 release, along with Beta 2 of Visual Studio 2005. Not out yet. Bennett Adelson

Big Disclaimer This presentation is based on the February 2005 Community Technology Previews of SQL Server 2005 and Visual Studio 2005. Anything and everything could change between now and the final release of these products. To the extent of such changes, the information in this presentation could end up wrong! Bennett Adelson

Beta/CTP Installation Tips • Use a clean VM – Or, completely remove old build before installing new one using Setup ToolsBuild Uninstall Wizardsqlbuw. exe in installation media • Install SQL Server and Visual Studio on separate VMs – They tend to have incompatible builds of the CLR • Even on separate VMs, not all Yukon/Whidbey combinations work together. These do: – – – Yukon Beta 1 with Whidbey Beta 1 Dec CTP with Whidbey Oct CTP Feb CTP with Whidbey Feb CTP Bennett Adelson

Enhancements to the Database Engine and T-SQL Bennett Adelson

Does a SQL Server 2005 Programmer Need to Know T-SQL? • Absolutely! – SQLCLR relies on T-SQL for querying and updating the database – T-SQL is still the fastest and most powerful for what it does – New T-SQL enhancements reduce the situations where procedural code is necessary Bennett Adelson

Enhancements to the Database Engine • SNAPSHOT Isolation Level – Uses versioning instead of locks, like that “Greek” database – Can provide better concurrency than traditional SERIALIZABLE and READ_COMMITTED • Large Value Data Types – VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) can hold up to 231 bytes – Use instead of TEXT and IMAGE • Statement-Level Recompilation for SPs Bennett Adelson

Enhancements to T-SQL • TRY … CATCH Exception Handling – With support for Transactions • OUTPUT Command – Use with INSERT, UPDATE and DELETE to save copies of the affected rows in a temporary table • TOP Command – Supported in INSERT, UPDATE and DELETE – Quantity can be a calculated value • PIVOT command – Rotates rows into columns and columns into rows Bennett Adelson

Common Table Expressions • The “WITH” Clause – Creates a “virtual” table for use in a single query – Often provides a simpler syntax than using a VIEW or subquery, and may be easier for Optimizer to optimize – Allows recursive queries Bennett Adelson

Simple WITH Clause WITH Big. Sales(Rep. ID) AS (SELECT Rep. Id from Sales WHERE Tot. Sales > 50000) SELECT ‘Big Seller’, * FROM Sales. Reps WHERE Sales. Reps. Id = Big. Sales. Rep. Id UNION SELECT ‘Small Seller’, * FROM Sales. Reps WHERE Sales. Reps. Id NOT IN (SELECT Rep. Id FROM Big. Sales) Bennett Adelson

Recursion Example: a “Linked List” Table: Org. Chart ID 1 2 3 4 5 6 Name Dubya Bill Rama Sal Jane Shu Reports. To NULL 3 1 2 3 1 Bennett Adelson

Query: How Many Levels from the Top Are You? Dubya Rama Shu Bill Jane Sal Levels From Top 0 1 1 2 2 3 Bennett Adelson

Performing the Query WITH Levels. Down (Id, Tot) AS ( SELECT Id, 0 FROM Org. Chart WHERE Reports. To is NULL UNION ALL SELECT Org. Chart. Id, Levels. Down. Tot + 1 FROM Levels. Down JOIN Org. Chart ON Levels. Down. Id = Org. Chart. Reports. To ) SELECT Name, Tot FROM Org. Chart JOIN Levels. Down ON Org. Chart. ID = Levels. Down. Id ORDER BY 2 Bennett Adelson

New Ranking Commands • Require an OVER clause to specify the sorting order • ROW_NUMBER – Inserts a column showing absolute position in the sort order • RANK – Assigns same value to all rows with same rank in the sort order • DENSE_RANK – Like RANK, but doesn’t leave “holes” • NTILE – Divides results into equal or near-equal divisions – Great for efficient paging in a Data. Grid Bennett Adelson

Adding Row Numbers to Query Output SELECT ROW_NUMBER() OVER(ORDER BY Last. Name) AS Row. Number, First. Name, Last. Name FROM Person Bennett Adelson

Selecting the 78 th of 83 “Pages” of Data SELECT Last. Name, First. Name FROM ( SELECT NTILE(83) OVER (ORDER BY Last. Name) AS Page. No, First. Name, Last. Name FROM Person ) AS TEMP WHERE TEMP. Page. No = 78 Bennett Adelson

The SQLCLR Bennett Adelson

Overview of SQLCLR • Write Procedures, Triggers and Functions in. NET languages to run in a CLR hosted by SQL Server • Use ADO. NET data access classes to run T-SQL commands inside the server • Create User-Defined Data Types that can be stored in database tables • Write Aggregate functions to operate on UDTs Bennett Adelson

Procedural Database Code before SQLCLR • T-SQL – – – Clumsy syntax Slow when not dealing directly with the database Syntax unfamiliar to many programmers • Extended Stored Procedures – Write an external DLL in C – Supported in SQL Server 2005, but likely to be deprecated • Difficult to develop and test • Risky, because external DLL runs in SQL Server’s address space – – – Memory leaks Database corruption Security holes • External Code in Data Access, Business Logic and/or Presentation layers Bennett Adelson

Benefits of SQLCLR • Write in your favorite. NET language – Initially supports C#, VB. NET and C++ • • • Use any. NET development tools Use Framework Class Library or other libraries Computation-intensive code executes faster than T-SQL • Stability and security of the CLR • Use native SQL security, Code Access Security, or both Bennett Adelson

What is a CLR Host? • Host: Any process that loads the CLR • . NET Framework 1. x has three hosts: – Command shell – IIS – Internet Explorer • Can write other CLR hosts using the Hosting API • . NET Framework 2. 0 expands the Hosting API to accommodate the needs of SQL Server Bennett Adelson

Requirements for Hosting in SQL Server • To maximize performance, SQL Server manages its own memory and threads without help from the NT Kernel – SQL Server understands internal memory needs better than NT Kernel; can use memory more efficiently with less paging – SQL Server uses cooperative multitasking to switch threads without kernel-mode context switching • Or in “fiber mode”, SQL Server may use fibers instead of threads – Requires multi-CPU machine Bennett Adelson

How SQL Server Implements CLR Hosting • SQLCLR memory management is handled by SQL Server, not NT Kernel • SQLCLR threads come from SQL Server thread pool and are cooperatively multitasked – Or if SQL Server is in “fiber mode”, the CLR threads are actually fibers • SQLCLR stores assemblies inside the database and loads them from there, not from the file system Bennett Adelson

Permission Sets Defined for SQLCLR Assemblies • SAFE – The default – Restrictions to assure the stability and security of SQL Server • EXTERNAL_ACCESS – Can access external data sources • UNSAFE – No restrictions, except those imposed by. NET Framework on all managed code – Similar risks as unmanaged External Stored Procedures Bennett Adelson

SAFE Permission Set • Prohibited to – Create threads – Access external resources such file system, network, registry or environment variables – Connect to external databases – Call unmanaged code via PInvoke or RCWs – Call portions of the Framework Class Library not marked as safe for SQL Server • E. g. , System. Windows. Forms, System. Security, System. Reflection Bennett Adelson

EXTERNAL_ACCESS Permission Set • Permitted to – Access external resources such as file system, network, registry or environment variables – Connect to external databases • Everything else prohibited same as SAFE Bennett Adelson

Some Consequences of SQL Server Hosting • Static fields must be readonly • After try{}, a finally{} block is not guaranteed to be called Bennett Adelson

How to Install an Assembly in SQLCLR • Create the Assembly outside SQL Server – SQL Server itself comes with no tools to write or compile assemblies. Can use Visual Studio, Framework SDK, or other tools – SQL Server doesn’t need a Strong Name • Enable SQLCLR on the server • Install Assembly in SQL Server with CREATE ASSEMBLY • Declare a procedure, function or trigger with CREATE [PROCEDURE|FUNCTION|TRIGGER] … EXTERNAL NAME … Bennett Adelson

Enabling SQLCLR on a Server • CLR Execution is Disabled by Default – To enable it, execute: • Sp_configure ‘clr enabled’, 1 • RECONFIGURE Bennett Adelson

Installing An Assembly CREATE ASSEMBLY My. Assembly FROM ‘C: ProjectsbinMy. Assembly. dll’ WITH PERMISSION_SET = SAFE ALTER ASSEMBLY My. Assembly FROM ‘C: ProjectsbinMy. Assembly. dll’ WITH PERMISSION_SET = SAFE DROP ASSEMBLY My. Assembly Bennett Adelson

Making the Assembly Usable from T-SQL CREATE PROCEDURE My. Procedure (@arg 1 int, @arg 2 varchar(20)) EXTERNAL NAME My. Assembly. [My. Name. Space. My. Class]. My. Procedure CREATE FUNCTION My. Function (arg 1 int) RETURNS int EXTERNAL NAME My. Assembly. [My. Name. Space. My. Class]. My. Function Bennett Adelson

Viewing Assemblies in SQL Server’s Metadata • To view metadata about assemblies installed in SQL Server SELECT * FROM Sys. Assemblies • To view assembly code itself SELECT * FROM Sys. Assembly_Files • To view procedures, functions and triggers defined from an assembly SELECT * FROM Sys. Assembly_References Bennett Adelson

Demonstration Creating a SQLCLR Function with Notepad Viewing assembly metadata Bennett Adelson

SQL Database Projects in Visual Studio 2005 • Allow quick creation of classes for Stored Procedures, Functions, Triggers, UDTs and Aggregates • One-click deployment of assembly into a database, no need to write T-SQL code • SQL Script provided to run while debugging Bennett Adelson

Debugging SQLCLR • Visual Studio 2005 Interactive Debugger can step through SQLCLR code – Must have Visual Studio Remote Debugging Monitor running on remote SQL Server • Microsoft has announced intention to release a free stand alone debugger sometime after SQL Server 2005 ships Bennett Adelson

Creating SQLCLR Functions, Procedures and Triggers • Must be a static method with one of – [Sql. Function. Attribute] – [Sql. Procedure. Attribute] – [Sql. Trigger. Attribute] • Can belong to a class or struct, whose name isn’t important • SQL doesn’t support overloading, so avoid overloaded method names Bennett Adelson

Using SQL Data Types in. NET • SQL types don’t map perfectly to CTS types – All SQL types can be NULL, but CTS value types can never be null – SQL decimal has a broader range of values than CTS Decimal – CTS Float and Double can hold the values Infinity and NAN, but SQL float and double cannot Bennett Adelson

System. Data. Sql. Types • System. Data. Sql. Types implements SQL types in. NET • Sql. Types should be used for – Parameters to functions and stored procedures – Return values from functions • You can also use Sql. Types in code outside SQL Server Bennett Adelson

Sql. Type Examples CLR String SQLType Sql. String Int 32 Double Boolean Byte[] Sql. Int 32 Sql. Double Sql. Boolean Sql. Binary Date. Time Guid Sql. Date. Time Sql. Guid SQL (n)(var)char, (n)text int float bit (var)binary, image, timestamp datetime uniqueidentifier Bennett Adelson

Operations on Sql. Types • Numerical Sql. Types support unary and binary arithmetic operators (+, -, * etc) • Sql. Types have boolean Is. Null property – Use it instead of comparing a Sql. Type with CLR null • Sql. Types support comparison operators (==, !=, <, > etc), but watch out for special rules when value = NULL • Sql. Types have static Null method to create an instance with value = NULL • Sql. String has concatenation with + Bennett Adelson

Converting Sql. Type to CLR Type void foo( Sql. Int 32 sql. Int ) { Int 32 clr. Int; clr. Int = sql. Int. Value; // or clr. Int = (Int 32)sql. Int; // but next is error, no implicit conversion clr. Int = sql. Int; } Bennett Adelson

Converting CLR Type to Sql. Type void foo( Int 32 clr. Int ) { Sql. Int 32 my. Sql. Int; my. Sql. Int = new Sql. Int 32(clr. Int); // or my. Sql. Int. Value = clr. Int; // or my. Sql. Int = (Sql. Int)clr. Int; // ok, implicit conversion allowed this direction my. Sql. Int = clr. Int } Bennett Adelson

Demonstration Creating a SQLCLR Function with Visual Studio 2005 Using Sql. Types Bennett Adelson

Accessing the Database from SQLCLR Code • Database code written in T-SQL can issue SQL statements like SELECT, UPDATE and EXEC at any time • SQLCLR code must go through an ADO. NET data provider Bennett Adelson

The System. Data. SQLServer Provider (as of Feb 2005 CTP) • System. Data. Sql. Server provides data access classes for use (only) within SQLCLR • Connection objects aren’t needed • Static Sql. Context methods create commands, Data. Adapters, etc • In SAFE assembly, SQL Server won’t allow data access unless class has this attribute property: Data. Access=Data. Access. Kind. Read Bennett Adelson

Changes Coming • Microsoft announced that in next prerelease of SQL Server 2005, System. Data. Sql. Server will merge into System. Data. Sql. Client • To connect to the database from within SQLCLR, you will use a SQLConnection with this connection string: “context connection=true” Bennett Adelson

Data Access Example (Feb CTP) Sql. Command comm = Sql. Context. Create. Command(); comm. Command. Text = “SELECT * FROM My. Table”; Sql. Data. Reader reader = comm. Execute. Reader(); // use the reader … Bennett Adelson

Data Access Example (Coming Soon) Sql. Connection conn = new Sql. Connection(“context connection=true”) Sql. Command comm = new Sql. Command() comm. Connection = conn; comm. Command. Text = “…”; conn. Open(); // use the command … Bennett Adelson

Splitting the Sql. Command • SQLDefinition – the parts of the command that don’t change in multiple invocations • SQLExecution. Context – the parts that do, such as the parameters • These can yield better performance than a single Sql. Command Bennett Adelson

Sql. Pipe • Returns a TDS (tabular data set) stream to the client • Use to return the results of stored procedures to clients Sql. Data. Reader reader = command. Execute. Reader(); Sql. Pipe pipe = Sql. Context. Get. Pipe(); pipe. Send( reader ); Bennett Adelson

Demonstration Executing SELECT statement inside a SQLCLR Function Putting It Together: Selecting a Random Row from a Table Bennett Adelson

User-Defined Data Types and Aggregates Bennett Adelson

Is SQL Server 2005 an Object. Oriented Database? • No – Will not store any arbitrary CLR type in the database – Does not support inheritance or polymorphism • It will store User-Defined Types, which emulate native SQL scalar types • Though created in. NET, UDTs behave more like SQL types than OOP types Bennett Adelson

What is a User-Defined Data Type? • A UDT is a. NET Class or Struct that can function as a SQL scaler type • A scaler can be stored in a column of a table, or be the type of a variable • Examples of built-in scaler types – INT – CHAR – DATETIME Bennett Adelson

What Must a Scaler Be Able to Do? • Mandatory – Be serializable to/from a byte array serializable to/from a string able to equal NULL • Optional, But Usually Necessary – Support comparison operators (=, <, >, etc) – Support numerical operators (+, -, *, / etc) – Support aggregation functions – Be indexable Bennett Adelson

Creating a UDT • • • Can be a Class or Struct Must have [Sql. User. Defined. Attribute] Must implement INullable Must override To. String() Must have a static Parse() that converts a string to an instance of the UDT Bennett Adelson

Binary Serialization of UDTs • Byte stream cannot exceed 8, 000 bytes • If [Sql. User. Defined. Attribute] is set to Format. Native, SQL Server will handle serialization – All UDT fields must be “blittable” • I. e. , fields that are simple value types – Sort order must be based on order of the binary fields • Use Format. Native whenever possible Bennett Adelson

Format. Native Serialization [Serializable] [Sql. User. Defined. Type (Format = Format. Native, Is. Byte. Ordered = true)] [Struct. Layout(Layout. Kind = Sequential)] struct My. UDT {…} Bennett Adelson

User-Defined Binary Serialization [Serializable] [Sql. User. Defined. Type( Format = Format. User. Defined, Is. Byte. Ordered = true, Is. Fixed. Length = true, Max. Byte. Size = 17)] struct My. UDT {…} Bennett Adelson

UDTs Outside the Database • Clients outside the database will need UDT type information when they see a table with a UDT column, and they won’t get the info from SQL Server • You’ll need to deploy the UDT’s assembly in the client’s folder, in the GAC, or elsewhere in the probe path • This leads to possible version conflicts between assemblies in and out of database Bennett Adelson

What is an Aggregate? • A class or struct whose methods implement a SQL aggregation function • The aggregation function can be applied in a T-SQL statement to a database column • Example: SELECT My. Agg. Sum(My. Table. UDTCol) FROM My. Table Bennett Adelson

Creating an Aggregate • Class or struct must have [Serializable. Attribute] • Class or struct must have [Sql. User. Defined. Aggregate. Attribute] • Must provide four public methods: – Init – Accumulate – Merge – Terminate Bennett Adelson

Aggregate Attribute Properties • Is. Invariant. To. Nulls – The aggregate ignores null values. The optimizer can choose not to send nulls. • Is. Invariant. To. Duplicates – The aggregate ignores duplicate values. The optimizer can choose not to send dupes. • Is. Invariant. To. Order – The result is unaffected by the order in which rows are processed. Optimizer can send them in any order • Is. Null. Empty – Result is NULL if no rows are passed. Optimizer can choose not to create an aggregate at all. Bennett Adelson

Demonstration Creating a 2 D Point Data Type And a String Aggregator Bennett Adelson

Microsoft SQL Server Summit 2005 • With Karen Delaney, author of Inside SQL Server 2000 and Inside SQL Server 2005 – May 17, 2005 Park Center III 6050 Oak Tree Blvd, Suite 300 Independence, Ohio 44131 Event code: 1032271939 – To register call 1. 877. 673. 8368 • Free, but seating limited Bennett Adelson

Resources on the Web • SQL Server 2005 Official Site – http: //msdn. microsoft. com/SQL/2005/defau lt. aspx • SQLJunkies – http: //www. sqljunkies. com/ • SQL Server 2005 Hands-On Labs Online – http: //msdn. microsoft. com/sql/2005 la bs/default. aspx Bennett Adelson

Conclusion • SQL Server 2005 confirms Microsoft’s ongoing commitment to the product – Gets closer to parity with rival databases • SQLCLR has compelling advantages in some situations – Intensive computations in memory – Use of FCL library features • In other situations, alternative designs, including T-SQL, may be better Bennett Adelson

Conclusion • SQLCLR solutions require care to build and test • SQLCLR developers will need mastery of both SQL and CLR Bennett Adelson

Q&A Bennett Adelson