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

• ID Name ReportsTo
• 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, 0 FROM 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 OrgChart JOIN 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 doesnt 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 BY LastName)
• AS RowNumber,
• FirstName, LastName
• FROM Person

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 Servers
  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 doesnt 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 PROCEDUREFUNCTIONTRIGGER 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
• (_at_arg1 int, _at_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 Servers 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 isnt
  important
• SQL doesnt support overloading, so avoid
  overloaded method names

42
Using SQL Data Types in .NET

• SQL types dont 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

• CLR SQLType SQL
• 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 (,!,lt,gt
  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 arent needed
• Static SqlContext methods create commands,
  DataAdapters, etc
• In SAFE assembly, SQL Server wont allow data
  access unless class has this attribute property
• DataAccessDataAccessKind.Read

51
Changes Coming

• Microsoft announced that in next pre-release of
  SQL Server 2005, System.Data.SqlServer will merge
  into System.Data.SqlClient
• To connect to the database from within SQLCLR,
  you will use a SQLConnection with this connection
  string
• context connectiontrue

52
Data Access Example (Feb CTP)

• SqlCommand comm SqlContext.CreateCommand()
• comm.CommandText SELECT
• FROM MyTable
• SqlDataReader reader
• comm.ExecuteReader()
• // use the reader

53
Data Access Example (Coming Soon)

• SqlConnection conn new SqlConnection(context
  connectiontrue)
• SqlCommand comm new SqlCommand()
• comm.Connection conn
• comm.CommandText
• conn.Open()
• // use the command

54
Splitting the SqlCommand

• SQLDefinition the parts of the command that
  dont change in multiple invocations
• SQLExecutionContext the parts that do, such as
  the parameters
• These can yield better performance than a single
  SqlCommand

55
SqlPipe

• Returns a TDS (tabular data set) stream to the
  client
• Use to return the results of stored procedures to
  clients
• SqlDataReader reader
• command.ExecuteReader()
• SqlPipe pipe SqlContext.GetPipe()
• pipe.Send( reader )

56
Demonstration

• Executing SELECT statement inside a SQLCLR
  Function
• Putting It Together Selecting a Random Row from
  a Table

57
User-Defined Data Types and Aggregates
58
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

59
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

60
What Must a Scaler Be Able to Do?

• Mandatory
• Be serializable to/from a byte array
• Be serializable to/from a string
• Be able to equal NULL
• Optional, But Usually Necessary
• Support comparison operators (,lt,gt, etc)
• Support numerical operators (,-,,/ etc)
• Support aggregation functions
• Be indexable

61
Creating a UDT

• Can be a Class or Struct
• Must have SqlUserDefinedAttribute
• Must implement INullable
• Must override ToString()
• Must have a static Parse() that converts a string
  to an instance of the UDT

62
Binary Serialization of UDTs

• Byte stream cannot exceed 8,000 bytes
• If SqlUserDefinedAttribute 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

63
Format.Native Serialization

• Serializable
• SqlUserDefinedType
• (Format Format.Native,
• IsByteOrdered true)
• StructLayout(LayoutKind Sequential)
• struct MyUDT

64
User-Defined Binary Serialization

• Serializable
• SqlUserDefinedType(
• Format Format.UserDefined,
• IsByteOrdered true,
• IsFixedLength true,
• MaxByteSize 17)
• struct MyUDT

65
UDTs Outside the Database

• Clients outside the database will need UDT type
  information when they see a table with a UDT
  column, and they wont get the info from SQL
  Server
• Youll need to deploy the UDTs assembly in the
  clients folder, in the GAC, or elsewhere in the
  probe path
• This leads to possible version conflicts between
  assemblies in and out of database

66
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 MyAgg.Sum(MyTable.UDTCol)
• FROM MyTable

67
Creating an Aggregate

• Class or struct must have SerializableAttribute
• Class or struct must have SqlUserDefinedAggregate
  Attribute
• Must provide four public methods
• Init
• Accumulate
• Merge
• Terminate

68
Aggregate Attribute Properties

• IsInvariantToNulls
• The aggregate ignores null values. The optimizer
  can choose not to send nulls.
• IsInvariantToDuplicates
• The aggregate ignores duplicate values. The
  optimizer can choose not to send dupes.
• IsInvariantToOrder
• The result is unaffected by the order in which
  rows are processed. Optimizer can send them in
  any order
• IsNullEmpty
• Result is NULL if no rows are passed. Optimizer
  can choose not to create an aggregate at all.

69
Demonstration

• Creating a 2D Point Data Type
• And a String Aggregator

70
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

71
Resources on the Web

• SQL Server 2005 Official Site
• http//msdn.microsoft.com/SQL/2005/default.aspx
• SQLJunkies
• http//www.sqljunkies.com/
• SQL Server 2005 Hands-On Labs Online
• http//msdn.microsoft.com/sql/2005/2005labs/defaul
  t.aspx

72
Conclusion

• SQL Server 2005 confirms Microsofts 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

73
Conclusion

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

74
Q A