You Can Do Anything If You Think “Yukon”

1. You Can Do AnythingIf You Think “Yukon” Presented by: Richard Broida Senior Architect

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

3. History of Microsoft SQL Server • 1988 – MS/Sybase version for OS/2 • 1993 – 4.21 for NT 3.1 • 1994 – MS/Sybase partnership ends • 1995 – 6.0, major rewrite • 1996 – 6.5 • 1997 – 6.5 Enterprise Edition • 1998 – 7.0, total rewrite • 2000 – 2000

4. SQL Server 2000 Has Lived to See … • Windows XP and Windows 2003 Server • .NET Framework 1.0 and 1.1 • BizTalk 2000, 2002 and 2004 • Commerce Server 2000 and 2002 • CMS 2000 and 2003 • SharePoint 2001 and 2003 • Exchange Server 2000 and 2003 • Oracle 9i and 10g • DB2 8.1 and 8.2

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

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

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

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

9. Beta/CTP Installation Tips • Use a clean VM • Or, completely remove old build before installing new one using \Setup Tools\Build Uninstall Wizard\sqlbuw.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 • Yukon Dec CTP with Whidbey Oct CTP • Yukon Feb CTP with Whidbey Feb CTP

10. Enhancements to the Database Engine and T-SQL

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

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

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

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

15. Simple WITH Clause WITH BigSales(RepID) AS (SELECT RepId from Sales WHERE TotSales > 50000) SELECT ‘Big Seller’, * FROM SalesReps WHERE SalesReps.Id = BigSales.RepId UNION SELECT ‘Small Seller’, * FROM SalesReps WHERE SalesReps.Id NOT IN (SELECT RepId FROM BigSales)

16. Recursion Example: a “Linked List” Table: OrgChart IDNameReportsTo 1 Dubya NULL 2 Bill 3 3 Rama 1 4 Sal 2 5 Jane 3 6 Shu 1

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

18. Performing the Query WITH LevelsDown (Id, Tot) AS ( SELECT Id, 0FROM OrgChart WHERE ReportsTo is NULL UNION ALL SELECT OrgChart.Id, LevelsDown.Tot + 1 FROM LevelsDown JOIN OrgChart ON LevelsDown.Id = OrgChart.ReportsTo ) SELECT Name, Tot FROM OrgChartJOIN LevelsDown ON OrgChart.ID = LevelsDown.Id ORDER BY 2

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

20. Adding Row Numbers to Query Output SELECT ROW_NUMBER() OVER(ORDER BYLastName) AS RowNumber, FirstName, LastName FROMPerson

21. Selecting the 78th of 83 “Pages” of Data SELECT LastName, FirstName FROM ( SELECT NTILE(83) OVER (ORDER BY LastName) AS PageNo, FirstName, LastName FROM Person ) AS TEMP WHERE TEMP.PageNo = 78

22. The SQLCLR

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

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

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

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

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

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

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

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

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

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

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

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

35. Installing An Assembly CREATE ASSEMBLY MyAssembly FROM ‘C:\Projects\bin\MyAssembly.dll’ WITH PERMISSION_SET = SAFE ALTER ASSEMBLY MyAssembly FROM ‘C:\Projects\bin\MyAssembly.dll’ WITH PERMISSION_SET = SAFE DROP ASSEMBLY MyAssembly

36. Making the Assembly Usable from T-SQL CREATE PROCEDURE MyProcedure (@arg1 int, @arg2 varchar(20)) EXTERNAL NAME MyAssembly.[MyNameSpace.MyClass]. MyProcedure CREATE FUNCTION MyFunction (arg1 int) RETURNS int EXTERNAL NAME MyAssembly.[MyNameSpace.MyClass]. MyFunction

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

38. Demonstration Creating a SQLCLR Function with Notepad Viewing assembly metadata

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

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

41. Creating SQLCLR Functions, Procedures and Triggers • Must be a static method with one of • [SqlFunctionAttribute] • [SqlProcedureAttribute] • [SqlTriggerAttribute] • Can belong to a class or struct, whose name isn’t important • SQL doesn’t support overloading, so avoid overloaded method names

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

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

44. SqlType Examples CLRSQLTypeSQL String SqlString (n)(var)char, (n)text Int32 SqlInt32 int Double SqlDouble float Boolean SqlBoolean bit Byte[] SqlBinary (var)binary, image, timestamp DateTime SqlDateTime datetime Guid SqlGuid uniqueidentifier

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

46. Converting SqlType to CLR Type void foo( SqlInt32 sqlInt ) { Int32 clrInt; clrInt = sqlInt.Value; // or clrInt = (Int32)sqlInt; // but next is error, no implicit conversion clrInt = sqlInt; }

47. Converting CLR Type to SqlType void foo( Int32 clrInt ) { SqlInt32 mySqlInt; mySqlInt = new SqlInt32(clrInt); // or mySqlInt.Value = clrInt; // or mySqlInt = (SqlInt)clrInt; // ok, implicit conversion allowed this direction mySqlInt = clrInt }

48. Demonstration Creating a SQLCLR Function with Visual Studio 2005 Using SqlTypes

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

50. The System.Data.SQLServer Provider (as of Feb 2005 CTP) • System.Data.SqlServer provides data access classes for use (only) within SQLCLR • Connection objects aren’t needed • Static SqlContext methods create commands, DataAdapters, etc • In SAFE assembly, SQL Server won’t allow data access unless class has this attribute property: DataAccess=DataAccessKind.Read