Bennett Adelson

You Can Do AnythingYou Can Do AnythingIf You Think “Yukon”If You Think “Yukon”

Presented by: Presented by:

Richard BroidaRichard Broida

Senior ArchitectSenior Architect

Bennett Adelson

AgendaAgenda

• Overview of SQL Server 2005Overview of SQL Server 2005• Enhancements to Database Engine and Enhancements to Database Engine and

T-SQLT-SQL• CLR HostingCLR Hosting

– Writing CLR functions, procedures and Writing CLR functions, procedures and triggerstriggers

– Creating user-defined data types and Creating user-defined data types and aggregatesaggregates

• ResourcesResources• Q & AQ & A

Bennett Adelson

History of Microsoft SQL ServerHistory of Microsoft SQL Server

• 1988 – MS/Sybase version for OS/21988 – MS/Sybase version for OS/2• 1993 – 4.21 for NT 3.11993 – 4.21 for NT 3.1• 1994 – MS/Sybase partnership ends1994 – MS/Sybase partnership ends• 1995 – 6.0, major rewrite1995 – 6.0, major rewrite• 1996 – 6.51996 – 6.5• 1997 – 6.5 Enterprise Edition1997 – 6.5 Enterprise Edition• 1998 – 7.0, total rewrite1998 – 7.0, total rewrite• 2000 – 20002000 – 2000

Bennett Adelson

SQL Server 2000 Has Lived to See …SQL Server 2000 Has Lived to See …

• Windows XP and Windows 2003 ServerWindows XP and Windows 2003 Server• .NET Framework 1.0 and 1.1.NET Framework 1.0 and 1.1• BizTalk 2000, 2002 and 2004BizTalk 2000, 2002 and 2004• Commerce Server 2000 and 2002Commerce Server 2000 and 2002• CMS 2000 and 2003CMS 2000 and 2003• SharePoint 2001 and 2003SharePoint 2001 and 2003• Exchange Server 2000 and 2003Exchange Server 2000 and 2003• Oracle 9i and 10gOracle 9i and 10g• DB2 8.1 and 8.2DB2 8.1 and 8.2

Bennett Adelson

New Features in SQL Server 2005New Features in SQL Server 2005

• SQLCLRSQLCLR• XML as Native Data TypeXML as Native Data Type• Hosting Web ServicesHosting Web Services• Enhancements to T-SQLEnhancements to T-SQL• Client API EnhancementsClient API Enhancements• Service BrokerService Broker• Notification ServicesNotification Services• Enhanced Reporting ServicesEnhanced Reporting Services• Enhanced Analysis ServicesEnhanced Analysis Services

Bennett Adelson

SQL Server 2005 EditionsSQL Server 2005 Editions

• Express EditionExpress Edition– Replaces MSCEReplaces MSCE– Freely downloadable and redistributableFreely downloadable and redistributable

• Workgroup EditionWorkgroup Edition– Less expensive than StandardLess expensive than Standard

• Standard EditionStandard Edition• Enterprise EditionEnterprise Edition• Developer EditionDeveloper Edition

Bennett Adelson

Where is It Now?Where is It Now?

• First Technology Preview released at First Technology Preview released at PDC in October 2003PDC in October 2003

• Betas 1 and 2 Released in 2004Betas 1 and 2 Released in 2004• Most current version on MSDN is Most current version on MSDN is

February 2005 Community Technology February 2005 Community Technology PreviewPreview

• A “Beta 3” was announced for April A “Beta 3” was announced for April 2005 release, along with Beta 2 of 2005 release, along with Beta 2 of Visual Studio 2005. Not out yet.Visual Studio 2005. Not out yet.

Bennett Adelson

Big DisclaimerBig 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 TipsBeta/CTP Installation Tips

• Use a clean VMUse a clean VM– Or, completely remove old build before installing Or, completely remove old build before installing

new one using \Setup Tools\Build Uninstall Wizard\new one using \Setup Tools\Build Uninstall Wizard\sqlbuw.exe in installation mediasqlbuw.exe in installation media

• Install SQL Server and Visual Studio on Install SQL Server and Visual Studio on separate VMsseparate VMs– They tend to have incompatible builds of the CLRThey tend to have incompatible builds of the CLR

• Even on separate VMs, not all Yukon/Whidbey Even on separate VMs, not all Yukon/Whidbey combinations work together. These do:combinations work together. These do:– Yukon Beta 1 with Whidbey Beta 1Yukon Beta 1 with Whidbey Beta 1– Yukon Dec CTP with Whidbey Oct CTPYukon Dec CTP with Whidbey Oct CTP– Yukon Feb CTP with Whidbey Feb CTPYukon Feb CTP with Whidbey Feb CTP

Bennett Adelson

Enhancements to the Database Enhancements to the Database Engine and T-SQLEngine and T-SQL

Bennett Adelson

Does a SQL Server 2005 Does a SQL Server 2005 Programmer Need to Know T-SQL?Programmer Need to Know T-SQL?

• Absolutely!Absolutely!– SQLCLR relies on T-SQL for querying and SQLCLR relies on T-SQL for querying and

updating the databaseupdating the database– T-SQL is still the fastest and most powerful T-SQL is still the fastest and most powerful

for what it doesfor what it does– New T-SQL enhancements reduce the New T-SQL enhancements reduce the

situations where procedural code is situations where procedural code is necessarynecessary

Bennett Adelson

Enhancements to the Database Enhancements to the Database EngineEngine

• SNAPSHOT Isolation LevelSNAPSHOT Isolation Level– Uses versioning instead of locks, like that Uses versioning instead of locks, like that

“Greek” database“Greek” database– Can provide better concurrency than Can provide better concurrency than

traditional SERIALIZABLE and traditional SERIALIZABLE and READ_COMMITTEDREAD_COMMITTED

• Large Value Data TypesLarge Value Data Types– VARCHAR(MAX), NVARCHAR(MAX) and VARCHAR(MAX), NVARCHAR(MAX) and

VARBINARY(MAX) can hold up to 2VARBINARY(MAX) can hold up to 23131 bytes bytes– Use instead of TEXT and IMAGEUse instead of TEXT and IMAGE

• Statement-Level Recompilation for SPsStatement-Level Recompilation for SPs

Bennett Adelson

Enhancements to T-SQLEnhancements to T-SQL

• TRY … CATCH Exception HandlingTRY … CATCH Exception Handling– With support for TransactionsWith support for Transactions

• OUTPUT CommandOUTPUT Command– Use with INSERT, UPDATE and DELETE to save copies Use with INSERT, UPDATE and DELETE to save copies

of the affected rows in a temporary tableof the affected rows in a temporary table

• TOP CommandTOP Command– Supported in INSERT, UPDATE and DELETESupported in INSERT, UPDATE and DELETE– Quantity can be a calculated valueQuantity can be a calculated value

• PIVOT commandPIVOT command– Rotates rows into columns and columns into rowsRotates rows into columns and columns into rows

Bennett Adelson

Common Table ExpressionsCommon Table Expressions

• The “WITH” ClauseThe “WITH” Clause– Creates a “virtual” table for use in a single Creates a “virtual” table for use in a single

queryquery– Often provides a simpler syntax than using Often provides a simpler syntax than using

a VIEW or subquery, and may be easier for a VIEW or subquery, and may be easier for Optimizer to optimizeOptimizer to optimize

– Allows recursive queriesAllows recursive queries

Bennett Adelson

Simple WITH ClauseSimple WITH Clause

WITH BigSales(RepID) ASWITH BigSales(RepID) AS(SELECT RepId from Sales WHERE (SELECT RepId from Sales WHERE

TotSales > 50000)TotSales > 50000)SELECT ‘Big Seller’, * FROM SalesReps SELECT ‘Big Seller’, * FROM SalesReps

WHERE SalesReps.Id = BigSales.RepIdWHERE SalesReps.Id = BigSales.RepIdUNIONUNIONSELECT ‘Small Seller’, * FROM SalesRepsSELECT ‘Small Seller’, * FROM SalesReps

WHERE SalesReps.Id NOT INWHERE SalesReps.Id NOT IN(SELECT RepId FROM BigSales)(SELECT RepId FROM BigSales)

Bennett Adelson

Recursion Example: a “Linked List” Recursion Example: a “Linked List” Table: OrgChartTable: OrgChart

IDID NameName ReportsToReportsTo

11 DubyaDubya NULLNULL

22 BillBill 33

33 RamaRama 11

44 SalSal 22

55 JaneJane 33

66 ShuShu 11

Bennett Adelson

Query: How Many Levels from the Query: How Many Levels from the Top Are You?Top Are You?

Levels From TopLevels From Top

DubyaDubya 00

RamaRama 11

ShuShu 11

BillBill 22

JaneJane 22

SalSal 33

Bennett Adelson

Performing the QueryPerforming the Query

WITH LevelsDown (Id, Tot) ASWITH LevelsDown (Id, Tot) AS((

SELECT Id, 0SELECT Id, 0 FROM OrgChartFROM OrgChart WHERE ReportsTo is NULLWHERE ReportsTo is NULLUNION ALLUNION ALLSELECT OrgChart.Id, LevelsDown.Tot + 1SELECT OrgChart.Id, LevelsDown.Tot + 1FROM LevelsDown JOIN OrgChart FROM LevelsDown JOIN OrgChart ON LevelsDown.Id = OrgChart.ReportsToON LevelsDown.Id = OrgChart.ReportsTo

))SELECT Name, TotSELECT Name, Tot FROMFROM OrgChart OrgChart JOIN LevelsDownJOIN LevelsDown ON OrgChart.IDON OrgChart.ID = = LevelsDown.IdLevelsDown.Id ORDER BY ORDER BY 22

Bennett Adelson

New Ranking CommandsNew Ranking Commands

• Require an OVER clause to specify the sorting Require an OVER clause to specify the sorting orderorder

• ROW_NUMBERROW_NUMBER– Inserts a column showing absolute position in the Inserts a column showing absolute position in the

sort ordersort order• RANKRANK

– Assigns same value to all rows with same rank in the Assigns same value to all rows with same rank in the sort ordersort order

• DENSE_RANKDENSE_RANK– Like RANK, but doesn’t leave “holes”Like RANK, but doesn’t leave “holes”

• NTILENTILE– Divides results into equal or near-equal divisionsDivides results into equal or near-equal divisions– Great for efficient paging in a DataGridGreat for efficient paging in a DataGrid

Bennett Adelson

Adding Row Numbers to Query Adding Row Numbers to Query OutputOutput

SELECT ROW_NUMBER() SELECT ROW_NUMBER()

OVER(OVER(ORDER BYORDER BY LLastastNName) ame)

ASAS RowNumber, RowNumber,

FFirstirstNName, ame, LLastastNNameame

FROMFROM PPerson erson

Bennett Adelson

Selecting the 78Selecting the 78thth of 83 “Pages” of of 83 “Pages” of DataData

SELECT SELECT LastName, FirstNameLastName, FirstName FROM FROM

((

SELECT NTILE(SELECT NTILE(8383) )

OVER (ORDER BY LastName) OVER (ORDER BY LastName)

AS PageNo, AS PageNo,

FirstName, LastNameFirstName, LastName FROM Person FROM Person

) AS ) AS TEMPTEMP

WHERE WHERE TEMP.TEMP.PageNo = PageNo = 7878

Bennett Adelson

The SQLCLRThe SQLCLR

Bennett Adelson

Overview of SQLCLROverview of SQLCLR

• Write Procedures, Triggers and Write Procedures, Triggers and Functions in .NET languages to run in a Functions in .NET languages to run in a CLR hosted by SQL ServerCLR hosted by SQL Server

• Use ADO.NET data access classes to run Use ADO.NET data access classes to run T-SQL commands inside the serverT-SQL commands inside the server

• Create User-Defined Data Types that Create User-Defined Data Types that can be stored in database tablescan be stored in database tables

• Write Aggregate functions to operate on Write Aggregate functions to operate on UDTsUDTs

Bennett Adelson

Procedural Database Code before Procedural Database Code before SQLCLRSQLCLR

• T-SQLT-SQL– Clumsy syntaxClumsy syntax– Slow when not dealing directly with the databaseSlow when not dealing directly with the database– Syntax unfamiliar to many programmersSyntax unfamiliar to many programmers

• Extended Stored ProceduresExtended Stored Procedures– Write an external DLL in CWrite an external DLL in C– Supported in SQL Server 2005, but likely to be deprecatedSupported in SQL Server 2005, but likely to be deprecated

• Difficult to develop and testDifficult to develop and test• Risky, because external DLL runs in SQL Server’s address Risky, because external DLL runs in SQL Server’s address

spacespace– Memory leaksMemory leaks– Database corruptionDatabase corruption– Security holesSecurity holes

• External Code in Data Access, Business Logic and/or External Code in Data Access, Business Logic and/or Presentation layersPresentation layers

Bennett Adelson

Benefits of SQLCLRBenefits of SQLCLR

• Write in your favorite .NET languageWrite in your favorite .NET language– Initially supports C#, VB.NET and C++Initially supports C#, VB.NET and C++

• Use any .NET development toolsUse any .NET development tools• Use Framework Class Library or other librariesUse Framework Class Library or other libraries• Computation-intensive code executes faster Computation-intensive code executes faster

than T-SQLthan T-SQL• Stability and security of the CLRStability and security of the CLR• Use native SQL security, Code Access Security, Use native SQL security, Code Access Security,

or bothor both

Bennett Adelson

What is a CLR Host?What is a CLR Host?

• Host: Any process that loads the CLRHost: Any process that loads the CLR• .NET Framework 1.x has three hosts:.NET Framework 1.x has three hosts:

– Command shellCommand shell– IISIIS– Internet ExplorerInternet Explorer

• Can write other CLR hosts using the Can write other CLR hosts using the Hosting APIHosting API

• .NET Framework 2.0 expands the .NET Framework 2.0 expands the Hosting API to accommodate the needs Hosting API to accommodate the needs of SQL Serverof SQL Server

Bennett Adelson

Requirements for Hosting in SQL Requirements for Hosting in SQL ServerServer

• To maximize performance, SQL Server To maximize performance, SQL Server manages its own memory and threads manages its own memory and threads without help from the NT Kernelwithout help from the NT Kernel– SQL Server understands internal memory SQL Server understands internal memory

needs better than NT Kernel; can use needs better than NT Kernel; can use memory more efficiently with less pagingmemory more efficiently with less paging

– SQL Server uses cooperative multitasking to SQL Server uses cooperative multitasking to switch threads without kernel-mode context switch threads without kernel-mode context switchingswitching• Or in “fiber mode”, SQL Server may use fibers Or in “fiber mode”, SQL Server may use fibers

instead of threadsinstead of threads– Requires multi-CPU machineRequires multi-CPU machine

Bennett Adelson

How SQL Server Implements CLR How SQL Server Implements CLR HostingHosting

• SQLCLR memory management is SQLCLR memory management is handled by SQL Server, not NT Kernelhandled by SQL Server, not NT Kernel

• SQLCLR threads come from SQL Server SQLCLR threads come from SQL Server thread pool and are cooperatively thread pool and are cooperatively multitaskedmultitasked– Or if SQL Server is in “fiber mode”, the CLR Or if SQL Server is in “fiber mode”, the CLR

threads are actually fibersthreads are actually fibers

• SQLCLR stores assemblies inside the SQLCLR stores assemblies inside the database and loads them from there, database and loads them from there, not from the file systemnot from the file system

Bennett Adelson

Permission Sets Defined for SQLCLR Permission Sets Defined for SQLCLR AssembliesAssemblies

• SAFESAFE– The defaultThe default– Restrictions to assure the stability and security of Restrictions to assure the stability and security of

SQL ServerSQL Server

• EXTERNAL_ACCESSEXTERNAL_ACCESS– Can access external data sourcesCan access external data sources

• UNSAFEUNSAFE– No restrictions, except those imposed by .NET No restrictions, except those imposed by .NET

Framework on all managed codeFramework on all managed code– Similar risks as unmanaged External Stored Similar risks as unmanaged External Stored

ProceduresProcedures

Bennett Adelson

SAFE Permission SetSAFE Permission Set

• Prohibited toProhibited to– Create threadsCreate threads– Access external resources such file system, Access external resources such file system,

network, registry or environment variablesnetwork, registry or environment variables– Connect to external databasesConnect to external databases– Call unmanaged code via PInvoke or RCWsCall unmanaged code via PInvoke or RCWs– Call portions of the Framework Class Library Call portions of the Framework Class Library

not marked as safe for SQL Servernot marked as safe for SQL Server• E.g., System.Windows.Forms, System.Security, E.g., System.Windows.Forms, System.Security,

System.ReflectionSystem.Reflection

Bennett Adelson

EXTERNAL_ACCESS Permission SetEXTERNAL_ACCESS Permission Set

• Permitted toPermitted to– Access external resources such as file Access external resources such as file

system, network, registry or environment system, network, registry or environment variablesvariables

– Connect to external databasesConnect to external databases

• Everything else prohibited same as Everything else prohibited same as SAFESAFE

Bennett Adelson

Some Consequences of SQL Server Some Consequences of SQL Server HostingHosting

• Static fields must be readonlyStatic fields must be readonly• After try{}, a finally{} block is After try{}, a finally{} block is notnot

guaranteed to be calledguaranteed to be called

Bennett Adelson

How to Install an Assembly in How to Install an Assembly in SQLCLRSQLCLR

• Create the Assembly outside SQL ServerCreate the Assembly outside SQL Server– SQL Server itself comes with no tools to write or SQL Server itself comes with no tools to write or

compile assemblies. Can use Visual Studio, compile assemblies. Can use Visual Studio, Framework SDK, or other toolsFramework SDK, or other tools

– SQL Server doesn’t need a Strong NameSQL Server doesn’t need a Strong Name

• Enable SQLCLR on the serverEnable SQLCLR on the server• Install Assembly in SQL Server with CREATE Install Assembly in SQL Server with CREATE

ASSEMBLYASSEMBLY• Declare a procedure, function or trigger with Declare a procedure, function or trigger with

CREATE [PROCEDURE|FUNCTION|TRIGGER] … CREATE [PROCEDURE|FUNCTION|TRIGGER] … EXTERNAL NAME …EXTERNAL NAME …

Bennett Adelson

Enabling SQLCLR on a ServerEnabling SQLCLR on a Server

• CLR Execution is Disabled by DefaultCLR Execution is Disabled by Default– To enable it, execute:To enable it, execute:

• Sp_configure ‘clr enabled’, 1Sp_configure ‘clr enabled’, 1• RECONFIGURERECONFIGURE

Bennett Adelson

Installing An AssemblyInstalling An Assembly

CREATE ASSEMBLY MyAssembly CREATE ASSEMBLY MyAssembly FROM ‘C:\Projects\bin\MyAssembly.dll’FROM ‘C:\Projects\bin\MyAssembly.dll’WITH PERMISSION_SET = SAFEWITH PERMISSION_SET = SAFE

ALTER ASSEMBLY MyAssembly ALTER ASSEMBLY MyAssembly FROM ‘C:\Projects\bin\MyAssembly.dll’FROM ‘C:\Projects\bin\MyAssembly.dll’WITH PERMISSION_SET = SAFEWITH PERMISSION_SET = SAFE

DROP ASSEMBLY MyAssemblyDROP ASSEMBLY MyAssembly

Bennett Adelson

Making the Assembly Usable from Making the Assembly Usable from T-SQLT-SQL

CREATE PROCEDURE MyProcedureCREATE PROCEDURE MyProcedure(@arg1 int, @arg2 varchar(20))(@arg1 int, @arg2 varchar(20))EXTERNAL NAME MyAssembly.EXTERNAL NAME MyAssembly.[MyNameSpace.MyClass].[MyNameSpace.MyClass].

MyProcedureMyProcedure

CREATE FUNCTION MyFunction CREATE FUNCTION MyFunction (arg1 int) RETURNS int(arg1 int) RETURNS intEXTERNAL NAME MyAssembly.EXTERNAL NAME MyAssembly.[MyNameSpace.MyClass].[MyNameSpace.MyClass].

MyFunctionMyFunction

Bennett Adelson

Viewing Assemblies in SQL Server’s Viewing Assemblies in SQL Server’s MetadataMetadata

• To view metadata about assemblies To view metadata about assemblies installed in SQL Serverinstalled in SQL Server

SELECT * FROM Sys.AssembliesSELECT * FROM Sys.Assemblies• To view assembly code itselfTo view assembly code itself

SELECT * FROM Sys.Assembly_FilesSELECT * FROM Sys.Assembly_Files• To view procedures, functions and To view procedures, functions and

triggers defined from an assemblytriggers defined from an assemblySELECT * FROM SELECT * FROM

Sys.Assembly_ReferencesSys.Assembly_References

Bennett Adelson

DemonstrationDemonstration

Creating a SQLCLR Function with Creating a SQLCLR Function with NotepadNotepad

Viewing assembly metadataViewing assembly metadata

Bennett Adelson

SQL Database Projects in Visual SQL Database Projects in Visual Studio 2005Studio 2005

• Allow quick creation of classes for Allow quick creation of classes for Stored Procedures, Functions, Triggers, Stored Procedures, Functions, Triggers, UDTs and AggregatesUDTs and Aggregates

• One-click deployment of assembly into One-click deployment of assembly into a database, no need to write T-SQL a database, no need to write T-SQL codecode

• SQL Script provided to run while SQL Script provided to run while debuggingdebugging

Bennett Adelson

Debugging SQLCLRDebugging SQLCLR

• Visual Studio 2005 Interactive Debugger Visual Studio 2005 Interactive Debugger can step through SQLCLR codecan step through SQLCLR code– Must have Visual Studio Remote Debugging Must have Visual Studio Remote Debugging

Monitor running on remote SQL ServerMonitor running on remote SQL Server

• Microsoft has announced intention to Microsoft has announced intention to release a free stand alone debugger release a free stand alone debugger sometime after SQL Server 2005 shipssometime after SQL Server 2005 ships

Bennett Adelson

Creating SQLCLR Functions, Creating SQLCLR Functions, Procedures and TriggersProcedures and Triggers

• Must be a static method with one ofMust be a static method with one of– [SqlFunctionAttribute][SqlFunctionAttribute]– [SqlProcedureAttribute][SqlProcedureAttribute]– [SqlTriggerAttribute][SqlTriggerAttribute]

• Can belong to a class or struct, whose Can belong to a class or struct, whose name isn’t importantname isn’t important

• SQL doesn’t support overloading, so SQL doesn’t support overloading, so avoid overloaded method namesavoid overloaded method names

Bennett Adelson

Using SQL Data Types in .NETUsing SQL Data Types in .NET

• SQL types don’t map perfectly to CTS SQL types don’t map perfectly to CTS typestypes– All SQL types can be NULL, but CTS value All SQL types can be NULL, but CTS value

types can never be nulltypes can never be null– SQL decimal has a broader range of values SQL decimal has a broader range of values

than CTS Decimalthan CTS Decimal– CTS Float and Double can hold the values CTS Float and Double can hold the values

Infinity and NAN, but SQL float and double Infinity and NAN, but SQL float and double cannotcannot

Bennett Adelson

System.Data.SqlTypesSystem.Data.SqlTypes

• System.Data.SqlTypes implementsSystem.Data.SqlTypes implements

SQL types in .NETSQL types in .NET• SqlTypes should be used forSqlTypes should be used for

– Parameters to functions and stored Parameters to functions and stored proceduresprocedures

– Return values from functionsReturn values from functions

• You can also use SqlTypes in code You can also use SqlTypes in code outside SQL Serveroutside SQL Server

Bennett Adelson

SqlType ExamplesSqlType Examples

CLRCLR SQLTypeSQLType SQLSQLStringString SqlStringSqlString (n)(var)char,(n)(var)char,

(n)text(n)textInt32Int32 SqlInt32SqlInt32 intintDoubleDouble SqlDoubleSqlDouble floatfloatBooleanBooleanSqlBooleanSqlBoolean bitbitByte[]Byte[] SqlBinarySqlBinary (var)binary,(var)binary,

image, image, timestamptimestamp

DateTimeDateTime SqlDateTimeSqlDateTime datetimedatetimeGuidGuid SqlGuidSqlGuid uniqueidentifieruniqueidentifier

Bennett Adelson

Operations on SqlTypesOperations on SqlTypes

• Numerical SqlTypes support unary and binary Numerical SqlTypes support unary and binary arithmetic operators (+,-,* etc)arithmetic operators (+,-,* etc)

• SqlTypes have boolean IsNull propertySqlTypes have boolean IsNull property– Use it instead of comparing a SqlType with CLR nullUse it instead of comparing a SqlType with CLR null

• SqlTypes support comparison operators (==,!SqlTypes support comparison operators (==,!=,<,> etc), but watch out for special rules =,<,> etc), but watch out for special rules when value = NULLwhen value = NULL

• SqlTypes have static Null method to create an SqlTypes have static Null method to create an instance with value = NULLinstance with value = NULL

• SqlString has concatenation with +SqlString has concatenation with +

Bennett Adelson

Converting SqlType to CLR TypeConverting SqlType to CLR Type

void foo( SqlInt32 sqlInt )void foo( SqlInt32 sqlInt ){{

Int32 clrInt;Int32 clrInt;clrInt = sqlInt.Value;clrInt = sqlInt.Value;

// or// orclrInt = (Int32)sqlInt;clrInt = (Int32)sqlInt;

// but next is error, no implicit conversion// but next is error, no implicit conversionclrInt = sqlInt;clrInt = sqlInt;

}}

Bennett Adelson

Converting CLR Type to SqlTypeConverting CLR Type to SqlType

void foo( Int32 clrInt )void foo( Int32 clrInt ){{

SqlInt32 mySqlInt;SqlInt32 mySqlInt;mySqlInt = new SqlInt32(clrInt);mySqlInt = new SqlInt32(clrInt);

// or// ormySqlInt.Value = clrInt;mySqlInt.Value = clrInt;

// or// ormySqlInt = (SqlInt)clrInt;mySqlInt = (SqlInt)clrInt;

// ok, implicit conversion allowed this direction// ok, implicit conversion allowed this directionmySqlInt = clrIntmySqlInt = clrInt

}}

Bennett Adelson

DemonstrationDemonstration

Creating a SQLCLR Function with Creating a SQLCLR Function with Visual Studio 2005Visual Studio 2005

Using SqlTypesUsing SqlTypes

Bennett Adelson

Accessing the Database from Accessing the Database from SQLCLR CodeSQLCLR Code

• Database code written in T-SQL Database code written in T-SQL can issue SQL statements like can issue SQL statements like SELECT, UPDATE and EXEC at any SELECT, UPDATE and EXEC at any timetime

• SQLCLR code must go through an SQLCLR code must go through an ADO.NET data providerADO.NET data provider

Bennett Adelson

The System.Data.SQLServer Provider The System.Data.SQLServer Provider (as of Feb 2005 CTP)(as of Feb 2005 CTP)

• System.Data.SqlServer provides data System.Data.SqlServer provides data access classes for use (only) within access classes for use (only) within SQLCLRSQLCLR

• Connection objects aren’t neededConnection objects aren’t needed• Static SqlContext methods create Static SqlContext methods create

commands, DataAdapters, etccommands, DataAdapters, etc• In SAFE assembly, SQL Server won’t In SAFE assembly, SQL Server won’t

allow data access unless class has this allow data access unless class has this attribute property:attribute property:

DataAccess=DataAccessKind.ReadDataAccess=DataAccessKind.Read

Bennett Adelson

Changes ComingChanges Coming

• Microsoft announced that in next pre-Microsoft announced that in next pre-release of SQL Server 2005, release of SQL Server 2005, System.Data.SqlServer will merge into System.Data.SqlServer will merge into System.Data.SqlClientSystem.Data.SqlClient

• To connect to the database from within To connect to the database from within SQLCLR, you will use a SQLConnection SQLCLR, you will use a SQLConnection with this connection string:with this connection string:

““context connection=true”context connection=true”

Bennett Adelson

Data Access Example (Feb CTP)Data Access Example (Feb CTP)

SqlCommand comm = SqlCommand comm = SqlContext.CreateCommand();SqlContext.CreateCommand();

comm.CommandText = “SELECT *comm.CommandText = “SELECT *

FROM MyTable”;FROM MyTable”;

SqlDataReader reader = SqlDataReader reader =

comm.ExecuteReader();comm.ExecuteReader();

// use the reader …// use the reader …

Bennett Adelson

Data Access Example (Coming Soon)Data Access Example (Coming Soon)

SqlConnection conn = new SqlConnection conn = new SqlConnection(“context SqlConnection(“context connection=true”)connection=true”)SqlCommand comm = new SqlCommand comm = new SqlCommand()SqlCommand()comm.Connection = conn;comm.Connection = conn;comm.CommandText = “…”;comm.CommandText = “…”;conn.Open();conn.Open();// use the command …// use the command …

Bennett Adelson

Splitting the SqlCommandSplitting the SqlCommand

• SQLDefinition – the parts of the SQLDefinition – the parts of the command that don’t change in multiple command that don’t change in multiple invocationsinvocations

• SQLExecutionContext – the parts that SQLExecutionContext – the parts that do, such as the parametersdo, such as the parameters

• These can yield better performance These can yield better performance than a single SqlCommandthan a single SqlCommand

Bennett Adelson

SqlPipeSqlPipe

• Returns a TDS (tabular data set) stream Returns a TDS (tabular data set) stream to the clientto the client

• Use to return the results of stored Use to return the results of stored procedures to clientsprocedures to clients

SqlDataReader reader = SqlDataReader reader = command.ExecuteReader();command.ExecuteReader();

SqlPipe pipe = SqlContext.GetPipe();SqlPipe pipe = SqlContext.GetPipe();pipe.Send( reader );pipe.Send( reader );

Bennett Adelson

DemonstrationDemonstration

Executing SELECT statement Executing SELECT statement inside a SQLCLR Functioninside a SQLCLR Function

Putting It Together: Selecting a Putting It Together: Selecting a Random Row from a TableRandom Row from a Table

Bennett Adelson

User-Defined Data Types and User-Defined Data Types and AggregatesAggregates

Bennett Adelson

Is SQL Server 2005 an Object-Is SQL Server 2005 an Object-Oriented Database?Oriented Database?

• NoNo– Will not store any arbitrary CLR type in the Will not store any arbitrary CLR type in the

databasedatabase– Does not support inheritance or Does not support inheritance or

polymorphismpolymorphism

• It It willwill store User-Defined Types, which store User-Defined Types, which emulate native SQL scalar typesemulate native SQL scalar types

• Though created in .NET, UDTs behave Though created in .NET, UDTs behave more like SQL types than OOP typesmore like SQL types than OOP types

Bennett Adelson

What is a User-Defined Data Type?What is a User-Defined Data Type?

• A UDT is a .NET Class or Struct that can A UDT is a .NET Class or Struct that can function as a SQL function as a SQL scalerscaler type type

• A scaler can be stored in a column of a A scaler can be stored in a column of a table, or be the type of a variabletable, or be the type of a variable

• Examples of built-in scaler typesExamples of built-in scaler types– INTINT– CHARCHAR– DATETIMEDATETIME

Bennett Adelson

What Must a Scaler Be Able to Do?What Must a Scaler Be Able to Do?

• MandatoryMandatory– Be serializable to/from a byte arrayBe serializable to/from a byte array– Be serializable to/from a stringBe serializable to/from a string– Be able to equal NULLBe able to equal NULL

• Optional, But Usually NecessaryOptional, But Usually Necessary– Support comparison operators (=,<,>, etc)Support comparison operators (=,<,>, etc)– Support numerical operators (+,-,*,/ etc)Support numerical operators (+,-,*,/ etc)– Support aggregation functionsSupport aggregation functions– Be indexableBe indexable

Bennett Adelson

Creating a UDTCreating a UDT

• Can be a Class or StructCan be a Class or Struct• Must have [SqlUserDefinedAttribute]Must have [SqlUserDefinedAttribute]• Must implement INullableMust implement INullable• Must override ToString()Must override ToString()• Must have a static Parse() that converts Must have a static Parse() that converts

a string to an instance of the UDTa string to an instance of the UDT

Bennett Adelson

Binary Serialization of UDTsBinary Serialization of UDTs

• Byte stream cannot exceed 8,000 bytesByte stream cannot exceed 8,000 bytes• If [SqlUserDefinedAttribute] is set to If [SqlUserDefinedAttribute] is set to

Format.Native, SQL Server will handle Format.Native, SQL Server will handle serializationserialization– All UDT fields must be “blittable”All UDT fields must be “blittable”

• I.e., fields that are simple value typesI.e., fields that are simple value types

– Sort order must be based on order of the Sort order must be based on order of the binary fieldsbinary fields

• Use Format.Native whenever possibleUse Format.Native whenever possible

Bennett Adelson

Format.Native SerializationFormat.Native Serialization

[Serializable][Serializable]

[SqlUserDefinedType[SqlUserDefinedType

(Format = Format.Native,(Format = Format.Native,

IsByteOrdered = true)]IsByteOrdered = true)]

[StructLayout(LayoutKind = [StructLayout(LayoutKind = Sequential)]Sequential)]

struct MyUDTstruct MyUDT

{ … }{ … }

Bennett Adelson

User-Defined Binary SerializationUser-Defined Binary Serialization

[Serializable][Serializable][SqlUserDefinedType([SqlUserDefinedType(

Format = Format.UserDefined,Format = Format.UserDefined,IsByteOrdered = true,IsByteOrdered = true,IsFixedLength = true,IsFixedLength = true,MaxByteSize = 17)]MaxByteSize = 17)]

struct MyUDTstruct MyUDT{ … }{ … }

Bennett Adelson

UDTs Outside the DatabaseUDTs Outside the Database

• Clients outside the database will need Clients outside the database will need UDT type information when they see a UDT type information when they see a table with a UDT column, and they table with a UDT column, and they won’t get the info from SQL Serverwon’t get the info from SQL Server

• You’ll need to deploy the UDT’s You’ll need to deploy the UDT’s assembly in the client’s folder, in the assembly in the client’s folder, in the GAC, or elsewhere in the probe pathGAC, or elsewhere in the probe path

• This leads to possible version conflicts This leads to possible version conflicts between assemblies in and out of between assemblies in and out of databasedatabase

Bennett Adelson

What is an Aggregate?What is an Aggregate?

• A class or struct whose methods A class or struct whose methods implement a SQL aggregation functionimplement a SQL aggregation function

• The aggregation function can be applied The aggregation function can be applied in a T-SQL statement to a database in a T-SQL statement to a database columncolumn

• Example:Example:

SELECT MyAgg.Sum(MyTable.UDTCol)SELECT MyAgg.Sum(MyTable.UDTCol)

FROM MyTableFROM MyTable

Bennett Adelson

Creating an AggregateCreating an Aggregate

• Class or struct must have Class or struct must have [SerializableAttribute][SerializableAttribute]

• Class or struct must have Class or struct must have [SqlUserDefinedAggregateAttribute][SqlUserDefinedAggregateAttribute]

• Must provide four public methods:Must provide four public methods:– InitInit– AccumulateAccumulate– MergeMerge– TerminateTerminate

Bennett Adelson

Aggregate Attribute PropertiesAggregate Attribute Properties

• IsInvariantToNullsIsInvariantToNulls– The aggregate ignores null values. The optimizer can The aggregate ignores null values. The optimizer can

choose not to send nulls.choose not to send nulls.

• IsInvariantToDuplicatesIsInvariantToDuplicates– The aggregate ignores duplicate values. The The aggregate ignores duplicate values. The

optimizer can choose not to send dupes.optimizer can choose not to send dupes.

• IsInvariantToOrderIsInvariantToOrder– The result is unaffected by the order in which rows The result is unaffected by the order in which rows

are processed. Optimizer can send them in any orderare processed. Optimizer can send them in any order

• IsNullEmptyIsNullEmpty– Result is NULL if no rows are passed. Optimizer can Result is NULL if no rows are passed. Optimizer can

choose not to create an aggregate at all.choose not to create an aggregate at all.

Bennett Adelson

DemonstrationDemonstration

Creating a 2D Point Data TypeCreating a 2D Point Data Type

And a String AggregatorAnd a String Aggregator

Bennett Adelson

Microsoft SQL Server Summit 2005Microsoft SQL Server Summit 2005

• With Karen Delaney, author of With Karen Delaney, author of Inside Inside SQL Server 2000SQL Server 2000 and and Inside SQL Server Inside SQL Server 20052005– May 17, 2005 May 17, 2005

Park Center III Park Center III 6050 Oak Tree Blvd, Suite 300 6050 Oak Tree Blvd, Suite 300 Independence, Ohio 44131 Independence, Ohio 44131 Event code: 1032271939Event code: 1032271939

– To register call 1.877.673.8368To register call 1.877.673.8368

• Free, but seating limitedFree, but seating limited

Bennett Adelson

Resources on the WebResources on the Web

• SQL Server 2005 Official SiteSQL Server 2005 Official Site– http://msdn.microsoft.com/SQL/2005/defaulthttp://msdn.microsoft.com/SQL/2005/default

.aspx.aspx

• SQLJunkiesSQLJunkies– http://www.sqljunkies.com/http://www.sqljunkies.com/

• SQL Server 2005 Hands-On Labs OnlineSQL Server 2005 Hands-On Labs Online– http://msdn.microsoft.com/sql/http://msdn.microsoft.com/sql/

2005/2005labs/default.aspx2005/2005labs/default.aspx

Bennett Adelson

ConclusionConclusion

• SQL Server 2005 confirms Microsoft’s SQL Server 2005 confirms Microsoft’s ongoing commitment to the productongoing commitment to the product– Gets closer to parity with rival databasesGets closer to parity with rival databases

• SQLCLR has compelling advantages in SQLCLR has compelling advantages in some situationssome situations– Intensive computations in memoryIntensive computations in memory– Use of FCL library featuresUse of FCL library features

• In other situations, alternative designs, In other situations, alternative designs, including T-SQL, may be betterincluding T-SQL, may be better

Bennett Adelson

ConclusionConclusion

• SQLCLR solutions require care to build SQLCLR solutions require care to build and testand test

• SQLCLR developers will need mastery of SQLCLR developers will need mastery of both SQL and CLRboth SQL and CLR

Bennett Adelson

Q & AQ & A