SQL Server "Yukon"

1
Databasetoepassingen met SQL Server Yukon
ADO.NET Whidbey Astrid Hackenberg class-a
2
Overview

• Data access enhancements
• Data access models
• Yukon XML

3
Data Access API Enhancements

• Multiple active result sets (MARS)
• Object Persistence FX (ObjectSpaces)
• Query notifications for cache invalidation
• Server cursors (SqlResultSet)
• Asynchronous client access
• Bulk update, paging, and batching
• XML Data type support
• User Defined Type (UDT) support

4
Data access models

• Relational
• Object
• XML (hierarchical)

5
The relational model

• ADO.NET
• Accessing and manipulating data in terms of
  database objects Tables, Columns, Relations
• Can use any data source
• Connected and disconnected scenario
• Optimized for performance
• Querying using SQL

6
ADO.NET DataSet architecture
DataSet
Managed Provider
DataAdapter
DataReader
SQLResultSet
Connection
Command
Parameters
Datastore
7
The object model

• ObjectSpaces
• Accessing and manipulating data in terms of
  domain objects Customer, Order, Address
• Declarative mapping between these objects and
  relational tables
• Binary separation between business logic and data
  access logic
• Querying using OPath
• Higher level layer on top of ADO.NET

8
ObjectSpaces architecture
ObjectSet
businessobject
businessobject
businessobject
businessobject
businessobject
ObjectReader
mappingfiles
ObjectSpace/(ObjectEngine)
ADO.NET data provider
Data store
9
Object versus relational
Customer Table
Customer
CustomerID
ObjID
ContactName
Contact
Street
Street
City
Country
City
Country

OSD ObjectSchema Definition
RSD RelationalSchema Definition
MSD MappingSchema Definition
10
The XML model

• SQLXML
• Accessing and manipulating data in terms of
  hierarchical objects Nodes, Elements, Attributes
• Standards based
• Optimized for integration
• Querying using XQuery

11
ADO.NET SQLXML architecture
XPathDocument
XML Provider
XmlAdapter
XmlBulkLoad
XmlView
XQueryProcessor(Command)
mappingfiles
XmlReader
Xml
SQLServer
12
XML versus relational
Customer
CustomerID
name
Street
City
Country
Order
CustomerId
orderID
OrderDate
ShipCity
XSD XMLSchema Definition
RSD RelationalSchema Definition
MSD MappingSchema Definition
13
Yukon XML Support (Engine)

• XML data type and XML index support
• Unified XML and relational store
• Both SQL and XQuery supported by same industrial
  strength infrastructure
• Leverages existing SQL engine and optimizer
• XQuery with data modification
• XML schema enforcement
• Client access using ADO.NET

14
Yukon support inside ADO.NET

• Support within System.Data
• SqlDataReader
• GetSqlXmlReader(int i), returns SqlXmlReader on
  xml data type column
• DataSet
• New XPathDocument column type
• Full databinding support
• Support within System.Xml
• XML Views and XQuery

15
XML Data Type

• Native SQL type
• Use for column, variable or parameter
• Store un-typed or typed XML instances
• Well-formed and validation checks
• Optional XML Schema enforcement

CREATE TABLE docs (id INT identity, xDoc
XML NOT NULL)
16
XML index

• Create XML index on XML column
• Creates indexes on tags, values paths
• Speeds up queries
• Entire query is optimized
• Indexes are used as available

CREATE XML INDEX idx_xml ON docs (xDoc)
17
Full-Text indexing

• Index and query XML instances
• Markup tags removed
• Syntax same as for other columns
• Use full-text search as filter, then XQuery
  search
• Uses full-text index first
• Uses XML index on tags, values, paths

CREATE FULLTEXT INDEX ON docs (xDoc)
18
Query example
SELECT R.Xquery ('//sec_at_num12') FROM
(SELECT FROM docs WHERE
contains (xDoc, 'Wrd1 Wrd2')) R(X)
19
XQuery

• As simple as XPath
• /customers
• can be much more powerful

ltcustomersgt for cust in document(customers
)/customers, order in document(orders)/orde
rs where (cust/id order/custid and
order/orderdate07/22/03 return ltnamegt
cust/custname lt/namegt lt/customersgt
20
XQuery features

• FLWR FOR / LET / WHERE / RETURN
• Includes XPath 2.0 (/doc_at_id 123)
• Element constructors (lttopicgt)
• Order-preserving operators
• Supports strong typing

21
XML Data modification

• Insert, update, and delete XQuery extensions
• XML sub-tree modification
• Add or delete XML sub-trees
• Update values
• Add a new entry in header

UPDATE docs SET xDocmodify('insert
ltReceivedgtMondaylt/Receivedgt after
/doc/Header')
22
Strengths and weaknesses
Arbitrary queries
Independent data definition
Encapsulation (controls data)
Outstanding
Impossible centralized schema
Not via SQL enforced by DBA
SQLBounded schema
Problematic schema inconsistency
Outstanding
ImpossibleOpen schema
XML Unbounded schema
Impossible Cant see the data!
Impossible Cant see the data!
Outstanding
ObjectsEncapsulated data
23
Putting it all together!
SQL holds the data
XML-InfoSets formessages between services
Objects implementthe biz logic
24
Review

• XML is broadly supported in ADO.NET
• XML in SQL Server
• Provides for more efficient storage and access to
  semi-structured data
• Provides for better integration in Service
  Orientated Architectures
• Complements, but does not replace a good
  relational schema

25
Questions

• ?

26
XML Type System

• Associate XML namespace with XML type
• Query and modify typed XML data

DECLARE _at_x XML('http//www.ms.com/xdb') SET _at_x
'ltpdoc p"http//www.ms.com/xdb"gt SELECT
_at_xquery('namespace p"http//www.ms.com/xdb"
for s in /pdoc_at_pid 123//psec
return lttopicgtdata(s/heading)lt/topicgt')
FROM docs