sql summit clr, service broker and xml

SQL Server 2005 for SQL Server 2005 for DevelopersDevelopers

David TruxallDavid TruxallNuSoft SolutionsNuSoft Solutions

Drew RobbinsDrew RobbinsMicrosoft CorporationMicrosoft Corporation

AgendaAgenda

XML Enhancements XML Enhancements CLR in SQL Server CLR in SQL Server Service BrokerService Broker

XML in the DatabaseXML in the DatabaseOverviewOverview XML has become widely adoptedXML has become widely adopted XML is ubiquitous, extensible, and a XML is ubiquitous, extensible, and a

platform independent transport formatplatform independent transport format Supports structured and semi-Supports structured and semi-

structured datastructured data Limited support in SQL Server 2000Limited support in SQL Server 2000 First class support in SQL Server 2005First class support in SQL Server 2005

XML In the DatabaseXML In the DatabaseBusiness OpportunityBusiness Opportunity The majority of all data transmitted The majority of all data transmitted

electronically between organizations is electronically between organizations is now in XML. now in XML.

By storing XML natively, SQL Server By storing XML natively, SQL Server 2005 helps eliminate the friction of data 2005 helps eliminate the friction of data transfer between organizations.transfer between organizations.

With the improved XML support in VS With the improved XML support in VS 2005, building e-business applications 2005, building e-business applications is radically simplified.is radically simplified.

XML in the DatabaseXML in the DatabaseXML Data TypeXML Data Type

SQL Server 2005 adds native XML SQL Server 2005 adds native XML storage with XML data typestorage with XML data type

Stores both well-formed and validated Stores both well-formed and validated XMLXML

ImplementationImplementation Markup documentsMarkup documents Tree-structured data where re-assembly Tree-structured data where re-assembly

costs should be minimalcosts should be minimal Semi-structured dataSemi-structured data

XML in the DatabaseXML in the DatabaseXML Data TypeXML Data Type

An alternative to storing XML on diskAn alternative to storing XML on disk Supports column, variable, or parameterSupports column, variable, or parameter Can represent Version 1.0 documents or Can represent Version 1.0 documents or

fragmentsfragments Methods on XML data typeMethods on XML data type

query(), value(), exist(), modify(), and query(), value(), exist(), modify(), and nodes()nodes()

XML in the DatabaseXML in the DatabaseXML Schema SupportXML Schema Support

Validation and typing provided by XSDValidation and typing provided by XSD W3C recommendationW3C recommendation Mechanism for type definitionsMechanism for type definitions Validation constraintsValidation constraints

Typed DataTyped Data Guarantees the Guarantees the shapeshape of the data of the data Allows storage and query optimizationsAllows storage and query optimizations

XML in the DatabaseXML in the DatabaseXML Schema CollectionsXML Schema Collections

Creating an XML Schema CollectionCreating an XML Schema CollectionCREATE XML SCHEMA COLLECTION ResumeSchema ASCREATE XML SCHEMA COLLECTION ResumeSchema AS

'<xs:schema '<xs:schema

targetNameSpace='http://schemas.ms.com/emp" …/>'targetNameSpace='http://schemas.ms.com/emp" …/>'

Constraining XML documentsConstraining XML documentsCREATE TABLE Employee (..., Resume XML(ResumeSchema))CREATE TABLE Employee (..., Resume XML(ResumeSchema))

Retrieving XML Schema CollectionsRetrieving XML Schema CollectionsSELECT NameSELECT Name

FROM AdventureWorks.sys.xml_schema_collectionsFROM AdventureWorks.sys.xml_schema_collections

XML Data TypeXML Data Type

XML in the DatabaseXML in the DatabaseXML IndexesXML Indexes

Create XML index on XML columnCreate XML index on XML columnCREATE PRIMARY XML INDEX IDX1 ON JobCandidate (Resume)CREATE PRIMARY XML INDEX IDX1 ON JobCandidate (Resume)

Creates a secondary, B-tree index onCreates a secondary, B-tree index on Path, Property, or ValuePath, Property, or Value

Speeds up queriesSpeeds up queries Results can be served directly from indexResults can be served directly from index Uses SQL Server's cost based optimizerUses SQL Server's cost based optimizer

Full-Text Indexing/Searching supports Full-Text Indexing/Searching supports XMLXML

XML in the DatabaseXML in the DatabaseXML IndexesXML Indexes

PATH IndexPATH Index When querying by path and valueWhen querying by path and value

/ItemList/Item[@ProductID="1"]/ItemList/Item[@ProductID="1"]

PROPERTY IndexPROPERTY Index When retrieving node values by pathWhen retrieving node values by path

(/ItemList/Item/@ProductID)[1](/ItemList/Item/@ProductID)[1]

VALUE IndexVALUE Index When querying imprecisely, like for existenceWhen querying imprecisely, like for existence

//Item[@ProductID="1"]//Item[@ProductID="1"]

XML in the DatabaseXML in the DatabaseXML IndexesXML Indexes

RequirementsRequirements A clustered PK must exist in the tableA clustered PK must exist in the table If XML index exists, the PK may not be If XML index exists, the PK may not be

alteredaltered Only one primary XML index per columnOnly one primary XML index per column Many secondary XML indexes per columnMany secondary XML indexes per column

PATH, PROPERTY, and VALUEPATH, PROPERTY, and VALUE

All index names must be unique per tableAll index names must be unique per table

XML in the DatabaseXML in the DatabaseXQueryXQuery

Query XML documents and dataQuery XML documents and data Based on W3C working draftBased on W3C working draft FLWOR - For, Let, Where, Order by, ReturnFLWOR - For, Let, Where, Order by, Return

XQuery methodsXQuery methods .query() – returns XML data type instance.query() – returns XML data type instance .value() – returns a single (scalar) value.value() – returns a single (scalar) value .exist() – returns 1 if expression returns .exist() – returns 1 if expression returns

item(s)item(s) .nodes() – returns a row per selected node.nodes() – returns a row per selected node

XML in the DatabaseXML in the DatabaseXML Data Manipulation LanguageXML Data Manipulation Language

Inefficient to UPDATE the entire columnInefficient to UPDATE the entire column XML sub-tree modification XML sub-tree modification

using using .modify().modify() Three actions: Three actions: InsertInsert, , DeleteDelete, or , or ReplaceReplace

UPDATE xmlTable SET xmlColumn.modify('insert UPDATE xmlTable SET xmlColumn.modify('insert

<section num="2"><section num="2">

<heading>Background</heading><heading>Background</heading>

</section></section>

after (/doc/section[@num=1])[1]')after (/doc/section[@num=1])[1]')

XQueryXQuery

XML in the DatabaseXML in the DatabaseFOR XML ImprovementsFOR XML Improvements

Support for XSD schemasSupport for XSD schemas Support for NULL valuesSupport for NULL values Support for XML data typeSupport for XML data type Nested FOR XMLNested FOR XML Can be assigned to an XML data typeCan be assigned to an XML data type FOR XML PATHFOR XML PATH

CLR in the DatabaseCLR in the DatabaseOverviewOverview Enhanced programming modelEnhanced programming model Enhanced safety and securityEnhanced safety and security User defined types and aggregatesUser defined types and aggregates Common development environmentCommon development environment Performance and scalabilityPerformance and scalability

CLR in the DatabaseCLR in the DatabaseBusiness OpportunityBusiness Opportunity Leverage .NET productivity and the Leverage .NET productivity and the

BCLBCL Better way to encapsulate your IPBetter way to encapsulate your IP A new world of ways to extend SQL A new world of ways to extend SQL

ServerServer User-defined types to fit your domainUser-defined types to fit your domain User-defined aggregates to fit your User-defined aggregates to fit your

domaindomain

Go over the wall you hit with T-SQLGo over the wall you hit with T-SQL Replace Extended Procedures (XPs)Replace Extended Procedures (XPs)

CLR in the DatabaseCLR in the DatabaseArchitectureArchitecture Common Language Runtime (CLR) Common Language Runtime (CLR)

AssembliesAssemblies Can write stored procedures, triggers, Can write stored procedures, triggers,

UD functions, UD types, and aggregate UD functions, UD types, and aggregate functionsfunctions

Any CLR languageAny CLR language Great replacement for Extended Stored Great replacement for Extended Stored

Procedures (XPs)Procedures (XPs)

Supported in SQL ExpressSupported in SQL Express

CLR in the DatabaseCLR in the Database

Microsoft® Visual

Studio® .NET Project

VB, C#

Microsoft®SQL Server™

SQL Queries: select sum(tax(sal,state) ) from Emp where county = ‘King’

Runtime hosted inside

SQL

Assembly: “TaxLib.dll”

build

SQL Data Definition: create assembly … create function … create procedure … create trigger … create type …

deploy

CLR in the DatabaseCLR in the DatabaseSecuritySecurity

Managed Code running in CLRManaged Code running in CLR Type safety, application domains, CASType safety, application domains, CAS More secure than XPsMore secure than XPs

Reliability and safety design goalsReliability and safety design goals User code can't compromise SQL engineUser code can't compromise SQL engine User code can't overwrite SQL engine User code can't overwrite SQL engine

memory buffers or internal data memory buffers or internal data structuresstructures

User code must be authenticated and User code must be authenticated and authorized before being allowed accessauthorized before being allowed access

CLR in the DatabaseCLR in the DatabaseSecuritySecurity Permission SetsPermission Sets

SAFESAFE EXTERNAL_ACCESSEXTERNAL_ACCESS UNSAFEUNSAFE

CLR in the DatabaseCLR in the DatabaseT-SQL or Managed Code?T-SQL or Managed Code? T-SQL is superior whenT-SQL is superior when

Code mostly performs data accessCode mostly performs data access Code has little or no procedural logicCode has little or no procedural logic

Managed Code is superior whenManaged Code is superior when Code has CPU-intensive methodsCode has CPU-intensive methods Code has complex logicCode has complex logic Code can make use of the .NET BCLCode can make use of the .NET BCL

CLR in the DatabaseCLR in the DatabaseManaged Database ObjectsManaged Database Objects

T-SQL (SQL 2000)T-SQL (SQL 2000) .NET (SQL 2005).NET (SQL 2005)

Stored ProceduresStored Procedures XX XX

TriggersTriggers XX XX

User Defined FunctionsUser Defined Functions XX XX

User Defined TypesUser Defined Types XX

Aggregate FunctionsAggregate Functions XX

CLR in the DatabaseCLR in the DatabaseManaged Stored ProceduresManaged Stored Procedures

Stored ProceduresStored Procedures Public, static methods on a classPublic, static methods on a class Returns a void or intReturns a void or int SqlPipe.Execute() and SqlPipe.Send() SqlPipe.Execute() and SqlPipe.Send()

public static void ExecuteToClient() {public static void ExecuteToClient() {SqlConnection conn = SqlConnection conn = new new SqlConnection("context connection=true");SqlConnection("context connection=true");

SqlCommand cmd = conn.CreateCommand();SqlCommand cmd = conn.CreateCommand();cmd.CommandText = "select @@version";cmd.CommandText = "select @@version";SqlContext.GetPipe().Execute(cmd);SqlContext.GetPipe().Execute(cmd);

}}

CLR in the DatabaseCLR in the DatabaseManaged TriggersManaged Triggers

TriggersTriggers Similar to stored proceduresSimilar to stored procedures Determine which columns have been modifiedDetermine which columns have been modified Reference INSERTED and DELETED tablesReference INSERTED and DELETED tables

SqlConnection conn = SqlConnection conn = new new SqlConnection("context connection=true");SqlConnection("context connection=true");

sqlCommand sqlComm = conn.CreateCommand()sqlCommand sqlComm = conn.CreateCommand()

sqlComm.CommandText = "SELECT column_1 from sqlComm.CommandText = "SELECT column_1 from INSERTED";INSERTED";

CLR in the DatabaseCLR in the DatabaseManaged User Defined FunctionsManaged User Defined Functions

Two types of User Defined FunctionsTwo types of User Defined Functions Scalar-valued function (SVF)Scalar-valued function (SVF)

Returns a single value, such as string or integerReturns a single value, such as string or integer Class methods determine return typeClass methods determine return type

Table-valued function (TVF)Table-valued function (TVF) Data returned through an ISqlReader objectData returned through an ISqlReader object

public static ISqlReader ReadLog(String logname) public static ISqlReader ReadLog(String logname) {{

return (ISqlReader)new MySqlReader(logname);return (ISqlReader)new MySqlReader(logname);

}}

CLR in the DatabaseCLR in the DatabaseManaged User Defined TypesManaged User Defined Types

When the built-in SQL Server types won't doWhen the built-in SQL Server types won't do Create custom, complex structured typesCreate custom, complex structured types BenefitsBenefits

Strong encapsulation (both at client and server)Strong encapsulation (both at client and server) Deep integration support with SQL ServerDeep integration support with SQL Server

RequirementsRequirements SqlUserDefinedTypeAttribute, Serializable, SqlUserDefinedTypeAttribute, Serializable,

INullable, Parse(), ToString()INullable, Parse(), ToString() Read() and Write() to support custom Read() and Write() to support custom

serializationserialization OthersOthers

CLR in the DatabaseCLR in the DatabaseManaged Aggregate FunctionsManaged Aggregate Functions

Perform a calculation on a set of values Perform a calculation on a set of values and return a single value, such as SUM and return a single value, such as SUM or MAXor MAX SqlUserDefinedAttributeSqlUserDefinedAttribute Must implement these methods so the Must implement these methods so the

Query Processor can use your aggregate Query Processor can use your aggregate functionfunction Init(), Accumulate(), Merge(), Terminate()Init(), Accumulate(), Merge(), Terminate()

CLR in the DatabaseCLR in the DatabaseAssembliesAssemblies .NET Assemblies.NET Assemblies

Self-describing, metadata, dependenciesSelf-describing, metadata, dependencies Unit of deployment, CLR permission Unit of deployment, CLR permission

grants, versioning, etc.grants, versioning, etc. Stored in databaseStored in database

CREATE, ALTER, DROP DDLCREATE, ALTER, DROP DDLCREATE ASSEMBLY FinanceFunctions FROM CREATE ASSEMBLY FinanceFunctions FROM

'c:\code\FinanceFunctions.dll' WITH 'c:\code\FinanceFunctions.dll' WITH PERMISSION_SET = SAFEPERMISSION_SET = SAFE

CLR in the DatabaseCLR in the Database

Service BrokerService BrokerOverviewOverview

Service Broker adds asynchronous, Service Broker adds asynchronous, distributed, and decoupled environmentdistributed, and decoupled environment Fully integrated into database engineFully integrated into database engine New set of SQL Server objects (DDL & DML)New set of SQL Server objects (DDL & DML) Integrated management and deploymentIntegrated management and deployment

Custom solutions and benefitsCustom solutions and benefits Generalized asynchronous semanticsGeneralized asynchronous semantics Inter and intra-instance queuingInter and intra-instance queuing

Distributed transactions not requiredDistributed transactions not required Multi-threading in stored proceduresMulti-threading in stored procedures

Service BrokerService BrokerBusiness OpportunityBusiness Opportunity Don't call us, we'll call youDon't call us, we'll call you Asynch can result in faster Asynch can result in faster

applicationsapplications Asynch can smooth out connectivity Asynch can smooth out connectivity

bugsbugs Notifications can help w/ middle-tier or Notifications can help w/ middle-tier or

client caching of dataclient caching of data Leverage Message Queue architectureLeverage Message Queue architecture Shares Begin/End patterns with .NETShares Begin/End patterns with .NET

Service BrokerService BrokerAsynchronous ArchitectureAsynchronous Architecture Loose couplingLoose coupling Load distributionLoad distribution Batch processingBatch processing PerformancePerformance Scale outScale out

Service BrokerService BrokerOrder Entry Data Flow DiagramOrder Entry Data Flow Diagram

Service BrokerService BrokerFeaturesFeatures

Database objects and DML statements that Database objects and DML statements that enable asynchronous messaging operationsenable asynchronous messaging operations Queues, Message Types, Contracts, ServicesQueues, Message Types, Contracts, Services ““BEGIN DIALOG”, “SEND”, “RECEIVE”BEGIN DIALOG”, “SEND”, “RECEIVE”

Fully integrated into the database engineFully integrated into the database engine Single Programming ModelSingle Programming Model Local TransactionsLocal Transactions Integrated Management and DeploymentIntegrated Management and Deployment

Service BrokerService Broker

TransactionTransaction

Both the client & stored procedure run in: Both the client & stored procedure run in: • Same TransactionSame Transaction• Same Security ContextSame Security Context• Same Thread of ExecutionSame Thread of Execution

ClientClientAppApp

SQL EngineSQL Engine

StoredStoredProcProc

Without Service Broker

Service BrokerService Broker

Tran

saction

Tran

saction

TransactionTransaction

ClientClientAppApp

LaunchedLaunchedStoredStoredProcProc

PersistedPersisted DialogDialog

Stored proc runs on:Stored proc runs on:- Different transactionDifferent transaction- Different threadDifferent thread- Different securityDifferent security- Different timeDifferent time

With ServiceBroker

Service BrokerService BrokerDialogsDialogs Two-way Messaging “Dialogs”Two-way Messaging “Dialogs”

Exactly-once, In-orderExactly-once, In-order Reliable delivery to local and remote Reliable delivery to local and remote

queuesqueues Large Message FragmentationLarge Message Fragmentation

APP 1

SQL Engine SQL Engine

APP 2

Service BrokerService BrokerNew CommandsNew Commands

CREATE MESSAGE TYPECREATE MESSAGE TYPECREATE CONTRACTCREATE CONTRACTCREATE QUEUECREATE QUEUECREATE SERVICECREATE SERVICEALTER SERVICEALTER SERVICEALTER MESSAGE TYPE ALTER MESSAGE TYPE ALTER QUEUEALTER QUEUEALTER CONTRACTALTER CONTRACTDROP MESSAGE TYPEDROP MESSAGE TYPEDROP CONTRACTDROP CONTRACTDROP QUEUEDROP QUEUEDROP SERVICEDROP SERVICECREATE ROUTECREATE ROUTEALTER ROUTEALTER ROUTEDROP ROUTEDROP ROUTE

New DDLNew DDL

BEGIN DIALOG CONVERSATIONBEGIN DIALOG CONVERSATIONEND CONVERSATIONEND CONVERSATIONMOVE CONVERSATIONMOVE CONVERSATIONGET SERVICE INSTANCEGET SERVICE INSTANCERECEIVERECEIVESENDSENDBEGIN DIALOG TIMERBEGIN DIALOG TIMER

New DMLNew DML

Service BrokerService BrokerImpact on PerformanceImpact on Performance

0

10

20

30

40

50

60

70

80

90

100

Shipping

Order

Shipping as trigger during OrderShipping as trigger during OrderShipping as trigger during OrderShipping as trigger during Order

0

10

20

30

40

50

60

70

80

90

100

Shipping

Order

Shipping queued by trigger during OrderShipping queued by trigger during OrderShipping queued by trigger during OrderShipping queued by trigger during Order

Service BrokerService Broker

Demo ScenarioDemo Scenario

OrderServiceOrderService

ShippingServiceShippingService AccountsServiceAccountsService

NewOrderNewOrder

Service BrokerService Broker

Call To ActionCall To Action

Leverage SQL Server 2005 to connect Leverage SQL Server 2005 to connect with applications and partners using with applications and partners using the ubiquitous XML format.the ubiquitous XML format.

Be smart about building functionality Be smart about building functionality using CLR in SQL Server.using CLR in SQL Server.

Leverage the Service Broker to create Leverage the Service Broker to create loosely-coupled message queues for loosely-coupled message queues for your applications.your applications.

Call To ActionCall To Action

http://www.chrysalisevents.com/vs2005devcon/http://www.chrysalisevents.com/vs2005devcon/

Detroit – June 16Detroit – June 16

© 2004 Microsoft Corporation. All rights reserved.© 2004 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.