SQL Server 2005 for Developers

1
SQL Server 2005 for Developers

• Juhani Vuoriojuhaniv_at_microsoft.comISV Developer
  EvangelistMicrosoft Oy358505727617

2
Whats new in SQL Server 2005?

• SQL Server 2005 is a major change for Developers
• Security enhancements
• User / schema separation
• Relational enhancements
• Integration of .NET framework
• User-defined types and aggregates
• XML data type and XQuery language
• HTTP Endpoints
• XML Web Servics
• SQL Server Service Broker
• Queing system inside database

3
Whats new in client-side data access?

• Major changes in SqlClient needed to support SQL
  Server 2005
• Support of .NET data types
• Support of XML type
• SQL Native Client for OLE DB and ODBC
• SqlClient enhancements
• Multiple active resultsets
• Notifications
• Asynchronous execution
• SQLXML
• XQuery support with integrated SQL Server data

4
Common Language Runtime (CLR) Integration
5
Whats new Feature Overview
6
Scenarios

• Richer languages and richer development platform
• Complex procedural logic and computation
• .NET Framework APIs
• Leverage existing skills
• C, Visual Basic .NET,..
• ADO.NET
• Robust way to access external resources replace
  extended stored procedures

7
Assemblies

• CREATE ASSEMBLY FinanceFunctions
• FROM \\m1\public\FinanceFunctions.dll
• WITH PERMISSION_SET SAFE
• Self-describing metadata, dependencies
• Stored in database
• Unit of deployment, CLR permission grants,
  versioning, etc.

8
Permission sets
9
.NET Framework functions

• CREATE FUNCTION ComputeTax(_at_Salary Int)
• RETURNS int
• AS EXTERNAL NAME FinanceFunctions.Tax.ComputeTax
• Can be scalar or table-valued
• Mapped to static methods of the class
• Cannot do data modifications, send results to
  client
• SELECT
• FROM Employees e
• WHERE dbo.ComputeTax(e.Salary) lt 20000

10
.NET Framework functions Scenarios

• Scalar functions
• Typically called in queries with column values as
  arguments.
• Used for computation on arguments that returns a
  single value
• Arithmetic, string manipulation (e.g.
  ValidateZipCode())
• Table-valued functions Tabular access to non-SQL
  data
• Web Services/XML
• Other ADO.NET managed providers.
• Arrays/lists
• Can be significantly faster than T-SQL
• Access to rich .NET Frameworks APIs that are
  difficult in T-SQL
• e.g., string manipulation functions, mathematical
  functions, regular expressions, encryption APIs,
  etc.

11
.NET Framework Procedures

• CREATE PROCEDURE check_inventory
• AS
• EXTERNAL NAME lib_eventsCInventorycheck_level
• Procedures not called from queries
• Can contain SQL queries, updates, or data
  definition languages (DDL)
• Can return results directly to client
• Typically use data access through in-proc
  ADO.NET.
• Scenarios
• Complex business logic with some or no data
  access
• Replacement for extended stored procedures
• (WebServices, files, registry, etc.)

12
User Defined Types

• Small types with encapsulated behavior
• Implemented as managed classes with a specific
  contract
• Deep engine support
• Query processing, Distributed Queries,
  Replication, Bridging Control Protocol (BCP)

13
Uniform Data Transfer (UDT) Client - Programming
Model

• ADO.NET Full fidelity access
• Bind to UDT columns as the underlying class with
  full access to its behaviors
• OLE DB
• Bind as bytes/object
• Behavior exposed via Component Object Model (COM)
  Interop
• Microsoft Open Database Connectivity (ODBC)
• Bind as bytes

14
Strengths and Weaknesses

• Strengths
• Computation, business logic
• Ease of development rich APIs, tools
• Fast-forward, read-only cursors
• Weakness
• Not suited for pure data access scenarios with
  little or no computation

15
Demo

• Development environment
• Deployment
• Data types

16
XML data type
17
XML as a data type

• The XML data type is native database type
• used as type of column in table
• used as type of parameter in stored procedure
• used as type of return value of a user-defined
  function
• used as type of a variable

18
XML column (Demo)

• XML column can store well-formed XML
• XML 1.0 recommendation
• documents or fragments

19
XML column usage

• XML column is not a TEXT column
• XML technologies supported
• the contents can validated using XML Schema
• XML-aware indexes are supported
• XQuery and XPath 2.0 supported
• in-database XML-related functionality works on
  the type
• FOR XML
• OpenXML

20
XML schema collections (Demo)

• XML schemas can be associated with an XML type
• insures that data inserted into column complies
  with schemas
• set of schemas known as a schema collection
• schema valid XML can disallow fragments
• http//www.w3.org/2001/XMLSchema implicitly
  associated
• contains types for primitives like float, int,
  etc.
• Other useful namespaces also implicitly
  associated
• e.g. schema instance, xml

21
XML schema in database

• XML schemas used by XML types must be in database
• Create XML SCHEMA COLLECTION
• collection name associated with XML instance
• Create XML SCHEMA COLLECTION requires literal
  schemas

22
XML indexes

• You can create XML (XQuery) indexes on an XML
  column
• optimizes XML Queries on the column
• table or view must have clustered primary key
• composite XML index not allowed
• primary XML index must be created first
• three specialized index types also available
• VALUE, PATH, PROPERTY

23
XML data type methods

• XML data type can use XML-specific methods
• all methods use XQuery as input
• exist - true or false if nodes returned
• value - single SQL scalar value from query
• query - returns XML type
• modify - change XML data in place
• nodes - return alternate context nodes
• used as input to "sub-XQuery"

24
SQL Server 2005 Support of XQuery (demo)

• XQuery is supported through methods on the XML
  type
• xml.exist
• xml.value
• xml.query
• xml.modify
• xml.nodes
• These methods can return
• columns in rowsets - when used with SQL SELECT
• variables

25
XQuery Expressions

• XQuery is a superset of XPath
• can use XPath or F(L)WOR expressions
• most valid XPath statements are also XQuery
  statements
• XPath used to select values in FLWOR expressions

26
Enhancements to SELECT...FOR XML

• SELECT FOR XML is an extension to Transact-SQL
• usually produces a stream
• in SQL Server 2005, it can also produce an XML
  data type
• use TYPE keyword after FOR XML
• can be used with RAW, AUTO formats
• can be used to query a "collection" of documents
• SELECT FOR XML PATH allows shaping of output
• FOR XML AUTO, RAW allow little shaping
• FOR XML EXPLICIT is complex to write
• FOR XML PATH uses path-syntax in column aliases
• allows "creation" of elements
• simpler than XML EXPLICIT

27
More FOR XML Enhancements

• Many refinements and extensions to FOR XML
• element-centric XML using FOR XML RAW
• generate xsinil for NULL database values
• FOR XML AUTO/RAW, ELEMENTS XSINIL
• nested FOR XML queries
• can specify ROOT element
• can name ltrowgt element in FOR XML RAW
• changes to nesting algorithm in XML AUTO
• supports new data types
• varchar(max), nvarchar(max), varbinary(max)
• UDTs (not in beta2)

28
SQL Service Broker
29
SQL Server Service Broker

• Broker adds asynchronous operations to SQL Server
• New set of SQL Server objects
• Defines new Transact-SQL statements
• CREATE MESSAGE TYPE
• CREATE CONTRACT
• CREATE QUEUE
• CREATE SERVICE
• BEGIN DIALOG CONVERSATION
• SEND
• RECEIVE
• WAITFOR
• GET CONVERSATION GROUPMOVE CONVERSATION
• Some new features implemented using Broker
• Events
• Notifications

30
Service Broker solutions

• SQL Broker can be used in custom solutions
• generalized asynchronous semantics
• inter and intra-instance queuing
• distributed transactions not required
• multi-threading in stored procedures

31
Service Broker semantic features

• Service Broker solves several hard application
  problems
• message ordering even across multiple exchanges
• once and only once delivery
• message correlation
• related messages received on different threads
• multiple queue readers for
• Time based load balancing
• failover
• conversation management
• error handling
• ability to group conversations for serialized
  behavior

32
Messages, Contracts, Queues, Services, Dialogs

• Messages are the atoms of services
• XML format (typed or untyped)
• binary format
• header only
• Messages can be composed into contracts
• Contracts have unique names
• Specifies INITIATOR,TARGET or ALL for each
  message
• Queues are the physical endpoints of services
• can point to a service program (procedure)
• can specify number of queue readers
• can specify execution principal
• Sets of functionality are comprised by services
• services and queues are referred to in code
• services route messages to correct
• queue
• service program (procedure)
• contracts are enforced by services
• Dialogs are "message pipes" between two points
• identified by dialog handle (GUID) for
  correlation

33
SEND (messages)

• SEND is a Transact-SQL keyword
• CONVERSATION (dialog handle)
• MESSAGE (must adhere to contract)
• Must be first statement in batch
• or start with semicolon
• DECLARE _at_SqlEventMessage varbinary(max)
• SET _at_SqlEventMessage 0x019381...
• SEND
• ON CONVERSATION _at_conversation_handle
• MESSAGE TYPE
• //semppu.com/tapahtumat/palaute
• (_at_SqlEventMessage)

34
RECEIVE (messages)

• Messages can be auto-routed to stored procedures
• associated during CREATE QUEUE
• Service programs use the Transact-SQL RECEIVE
• specify the QUEUE to receive from
• can qualify by conversation handle
• initiator and target have different conversation
  handles
• can quality by service instance
• can read message into
• series of variables
• resultset (RECEIVE from...)
• TABLE variable

35
Conversation Groups

• Related conversations can be grouped
• use related_conversation or related_conversation_g
  roup on BEGIN DIALOG
• managed by broker, no DDL statement
• conversation group identifier
• associated with messages
• used to guarantee exclusive access to messages
• special service instance lock allows multiple
  queue readers
• allows
• grouping of messages per instance
• storing application state by instance id
• you can move conversation to conversation group

36
Routing

• Service Broker instances can be inter-instance
• define endpoints for transmission with CREATE
  ENDPOINT
• define route table via CREATE ROUTE
• specify service name and broker instance
• specify address of next hop
• Service Broker uses decision tree to create
  dialog
• look in local database instance
• look for service and broker instance in route
  table (sys.routes)
• look for default route in route table
• look in other databases (same instance)

37
HTTP Endpoints
38
SQL Server directly leverages HTTP

• Uses HTTP kernel protocol stack
• less layers, better scalability and troughput
• requires Windows 2003 Server or Windows XP SP2
• Can use SSL

39
Web service configuration

• Web service endpoint must be configured
• location, access, and security
• Web service operations must be configured
• specific operation names and/or ad hoc sql
  batches
• CREATE ENDPOINT for HTTP used to configure
• names endpoint
• before FOR SOAP configures endpoint
• after FOR SOAP configures soap operations

40
SOAP Headers

• Some functionality is expressed through SOAP
  Headers
• SESSIONS (must be enabled on endpoint)
• SQL authentication through WS-Security
• LOGIN MIXED must be specified on endpoint
• does not support encryption
• transactions
• event notifications
• "connection string" options

41
Message formats

• SQL Web Services can use different command types
• submit batches of SQL commands
• invoke stored procedures
• stored procedure can return
• output parameters
• resultsets
• XML data types (schema validated or free form)

42
Output Formats

• Procedural code can produce five types of output
• SqlRowSet
• resultset from SELECT - uses DataSet/DiffGram
  format
• SqlXml
• resultset from SELECT FOR XML
• equates to XmlElement in .NET
• SqlRowCount
• "rows affected" - xsunsignedLong
• SqlResultCode
• output return code from SQL stored
  procedures/UDFs
• derived from simpleType int in XML
• SqlMessage
• complex type with SQL Server error info

43
Questions, Comments?