SQL Server 2005 - CLR, XML and T-SQL enhancements

Add to Favourites

Post to:

Description
An Overview of SQL Server 2005

Comments

Presentation Transcript

SQL Server 2005:CLR, XML and T-SQL enhancements : SQL Server 2005: CLR, XML and T-SQL enhancements Marcus Perryman marcpe@microsoft.com http://blogs.msdn.com/marcpe

Agenda : Agenda Introduction to SQL 2005 T-SQL – getting the CRUD done! Exceptions, snapshots, DDL and more CLR – for the stuff T-SQL just hates What does .NET CLR bring to SQL Server A little about ADO.NET XML – bringing native XML support into the database

SQL 2005 : SQL 2005 5 years in development Revolution vs Evolution Key highlights Not just a database! Analysis, reporting, notification, ETL – even more with SQL 2005 Tight integration with Visual Studio 2005 Performance, integrity and security Compatibility

SQL Server 2005 : SQL Server 2005

Timelines, Product Lines : Timelines, Product Lines Don’t forget Launch 7th November, 2005 September Community Technical Preview

Slide6 : Demo The tools

Many T-SQL Enhancements : Many T-SQL Enhancements Exception handling Snapshot Isolation DDL Triggers Lots of T-SQL Language extras… TOP, TABLESAMPLE, RANK, COMMON TABLE EXPRESSIONS, PIVOT/UNPIVOT… And more… Statement level recompile, set operators, relational bulk file handling, online index rebuild, etc.

Exception Handling : Exception Handling Eliminate “if @@error goto” code In catch block Can obtain error information Can raise original/different exception Txn might be “doomed” BEGIN TRY END TRY BEGIN CATCH END CATCH

Slide9 : Demo Exception Handling

Snapshot Isolation : Snapshot Isolation Additional transaction isolation level in 2005 Concurrency via versioning not locking Allows: Reads concurrent with in-flight writes Writes with in-flight writes: detects conflicts Good for: Read-mostly apps, migrations…

Snapshot Isolation : Snapshot Isolation Tx1: Seq = 1000 Tx2: Seq = 1001 read commit commit

DML with Output : DML with Output Now possible to capture the “output” of an INSERT, UPDATE or DELETE statement Can output to a table variable or table Uses “logical” tables similarly to triggers DELETED, INSERTED delete myTable output deleted.* …

Slide14 : Demo DML with Output

Common Table Expressions : Common Table Expressions SQL 99 syntax for “aliasing” a temporary named resultset Some similarities to temporary tables Some similarities to derived tables Query used has limitations Same as for creating a view, can’t nest CTE’s Can refer to itself (recursive CTE) [ WITH [ ,...n ] ] ::= expression_name [ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition )

Slide16 : Demo Common Table Expressions

Slide17 :

PIVOT/UNPIVOT : PIVOT/UNPIVOT Common requirement to produce a cross-tab Customer Sales Year Fred 10 2000 Bob 20 2000 Fred 30 2001 Customer 2000 2001 Fred 10 30 Bob 20 NULL Grouping and aggregation

Ranking : Ranking select row_number() over (order by...) select rank() over (order by…) select dense_rank() over (order by…) select ntile(N) over (order by…) Subtle differences in the functionality ROW_NUMBER RANK – tied entries have same rank, next is +N DENSE_RANK – as for RANK, next is +1 NTILE – divides into N groups

Slide20 : Demo Pivot and Ranking…

CLR – for the stuff T-SQL just hates : CLR – for the stuff T-SQL just hates

SQL Server Development Today : SQL Server Development Today SQL Server Relational Engine Storage Engine Scheduler User Code (Procedures, Functions, Triggers) T-SQL … Purpose? Libraries? Tools? Skills? ? Extended sp? COM objects?

The .NET Development Platform : The .NET Development Platform

SQL Server 2005 Development : SQL Server 2005 Development SQL Server 2005 Relational Engine Storage Engine Scheduler User Code (Procedures, Functions, Triggers) T-SQL …

What can we do with CLR code? : What can we do with CLR code? Common T-SQL objects can be implemented in CLR code User defined functions (and table valued functions) Stored procedures Triggers Additional objects can be implemented in CLR code User Defined Types User Defined Aggregates (MAX, MIN, SUM … )

When do we use CLR code? : When do we use CLR code? T-SQL code is great for CRUD operations CLR code is great for; General purpose Library support (.NET Framework) Tool support (Visual Studio 2005) Safety and (by scenario) performance “For the dirty jobs T-SQL doesn’t want to do”

Where do we use CLR code? : Where do we use CLR code? Round trip Round trip “Distance” between the code and the data Scale up/out possibilities of different tiers Abstraction of the database technology Security requirements Set-based versus procedural code Possibilities for using shared code libraries in multiple tiers

CLR Code Inside SQL Server : CLR Code Inside SQL Server Demo

ADO.NET 2.0 Features : ADO.NET 2.0 Features SQL 2005 User Defined Type support Query notifications Asynchronous support Multiple active result sets Client statistics Factory APIs DataSet & DataTable enhancements Binary serialization, stand-alone DataTables Bulk Load API

XML Data types : XML Data types

XML Data Type Basics : XML Data Type Basics SQL 2005 has a new native data type – XML Columns, Variables, Parameters etc. Stores typed/untyped XML Typed XML can be documents/fragments Stored in the database as a BLOB (2GB) Encoded as UTF-16 (usually!) Directly comparing instances?

XML Data Type – “Operators” : XML Data Type – “Operators” myXml.exist(query) Returns a 0, 1 or NULL myXml.value(query) Returns a single, scalar type myXml.query(query) Returns another XML datatype instance myXml.modify(expression) Update XML data inplace myXml.nodes(query) Later!

Querying – XQuery Language : Query XML data type with the XQuery language XQuery has similarities with XPath plus; Iteration Sorting Returning new XML of specified “shape” XQuery is a W3C Working Draft SQL Support based on July 2004 Draft Querying – XQuery Language

Querying – XQuery Language : Querying – XQuery Language declare default element namespace = “urn:demos-r-us”; declare namespace myns = “urn:more-demos-r-us”; for $item in //order/item where $item/@price > 1.0 order by $item/@price return {data($item/@product)}

Indexing the XML Data Type : Indexing the XML Data Type With no index for this query we must shred the XML at runtime to examine @price Two categories for XML Primary XML Index Secondary XML Indexes … where $item/@price > 1.0 …

Indexing the XML Data Type : Indexing the XML Data Type Primary XML Index Requires clustered index on PK of base table Persistent, shredded index of the XML Element/Attribute names, values, types, document structure Secondary XML Indexes PATH index (path,value) Useful for path expressions used in exist methods VALUE index (value, path) Useful for wildcards /book[@* = “novel”] PROPERTY index (PK, path, value) Useful when multiple properties are retrieved within a select statement

FOR XML Enhancements : FOR XML Enhancements FOR XML Enhancements in SQL 2005 Producing an XML document rather than just fragments Producing an instance of the XML data type Producing an inline XML Schema Customising output with FOR XML PATH … select * from customers FOR XML …

OPENXML & Bulk Load : OPENXML & Bulk Load Existing OPENXML function extended; XML datatype instance can be used as the output column XML datatype instance can be used as the overflow column sp_xml_preparedocument accepts instance of the XML data type Loading/Updating XML into the database OPENROWSET enhanced to enable loading of XML data

SQL Server 2005 Resources : SQL Server 2005 Resources Web Sites Product site http://www.microsoft.com/sql/2005/default.asp MSDN Centre http://msdn.microsoft.com/sql SQL Team http://www.sqlteam.com SQL Junkies http://www.sqljunkies.com SQL Server 2005 Express Edition http://lab.msdn.microsoft.com/express/sql/default.aspx Webcasts http://msdn.microsoft.com/sql/2005webcasts Books http://www.microsoft.com/sql/2005/samplechapters.asp User Groups http://msdn.microsoft.com/usergroups/find.asp Newsgroups Microsoft.public.sqlserver.*