sql server ® 2008 ® native client. agenda introduction to sql server native client building...

SQL Server® 2008® Native Client

Agenda

Introduction to SQL Server Native Client

Building High-Performance Data Access Solutions

Going Beyond Relational Data Migrating Applications to SQL Server

2008

SQL Native Client Overview

Single native DLL

• ODBC Driver• OLE DB Provider

Supports SQL Server 2000 Onwards

• New features only available when using SQL Server 2008

Universally available to developers

• Provided with SQL Server 2008 • Downloadable from MSDN

Choosing a Data Access Technology

•ADO.NET – System.Data.SqlClientMicrosoft .NET

Framework

•SQL Server Native Client•ODBC•OLE DB

Native code for SQL Server 2000 - 2008

•Windows Data Access Components•(Formerly MDAC)

Native code for pre SQL Server 2000

•SQL Server 2005 JDBC DriverJava

Agenda




2008

SQL Native Client Performance

High performance SQL Server client access

SQL Server 2008 release• Optimized at the network

protocol level for both ODBC and OLE DB

• Upwards of 10% performance improvement

Table-Valued Parameters

Encapsulate tabular data in client application

Ship to the server in a single command

Operate on the data as a table in Transact-SQL

Improved programming model

Significant performance benefits

Cleaner, more maintainable code

Better scalability for large data volumes

Agenda




2008

Storage Attribute on VARBINARY(MAX) Unstructured data stored directly in

the file system (requires NTFS) Dual Programming Model

Transact-SQL (Same as SQL BLOB) Win32 Streaming APIs with Transact-

SQL transactional semantics Data Consistency Integrated Manageability

Back Up / Restore Administration

Size limit is the file system volume size

SQL Server Security Stack

Store BLOBs in DB + File SystemApplication

BLOB

FILESTREAM Data

Sparse Columns

ID Title FirstName MiddleInitial LastName Phone

1 Keith Harris 555 112233

2 Mr David Pelton

3 Elizabeth A Anderson

4 Josh Pollock

Enable NULL data to consume no physical space

Access sparse columns directly or as XML-encoded name-value pairs

SPARSE SPARSE SPARSE

User-Defined Types

Custom data structures

• Implemented as CLR types

SQL Server 2008 Enhancements

• Remove 8 KB size limit• Enables more application scenarios

SELECT StreetNameFROM StreetsWHERE Streets.StreetGeo.STIntersects(@ms) = 1

Spatial OperationsE.g. find streets that intersect the Microsoft campus

geography data type•Geodetic spatial datageometry data type•Planar spatial data

Spatial Data Types

ORDPATH Data Type

Tree-structured data and containment relationships

• Organization hierarchy, file/folder hierarchy, etc

Modeled as (parent, child) pair

• Application manages the tree structure

Data type to better represent hierarchical relationships

• parent-child relationship, ancestor-descendant relationship• relative order of nodes in the tree • unlimited insertion of new nodes anywhere in tree without re-id of existing nodes

Provides a Huffman-encoded, variable-length, binary representation

• Allows byte-by-byte comparison of whole ORDPATH values • Optimizable• Operations are very fast.

Agenda




2008

Migrating from SQL Server 2005

Migrate only as needed SQL Server 2008

Native Client supports previous versions

SQL Server 2005 Native Client supports SQL Server 2008 New features are

unavailable

SQL Server 2000

SQL Server 2005

SQL Server 2008

SQL Server 2005 Native Client

SQL Server 2008 Native Client

All features supported

New features unsupported

Date and Time Data Types

•A date only data typedate•A time only data typetime•A time zone-aware data typedatetimeoffset

•A datetime type that supports larger fractional seconds and year range than the datetime data typedatetime2

Summary

High-performance data access

Powerful support for relational data and beyond

Easy migration from previous releases and other database platforms

sql server ® 2008 ® native client. agenda introduction to sql server native client building...

Documents