microsoft sql server 2008 new and future t sql programmability l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft SQL Server 2008: New and Future T-SQL Programmability PowerPoint Presentation
Download Presentation
Microsoft SQL Server 2008: New and Future T-SQL Programmability

Loading in 2 Seconds...

play fullscreen
1 / 32

Microsoft SQL Server 2008: New and Future T-SQL Programmability - PowerPoint PPT Presentation


  • 121 Views
  • Uploaded on

BB25. Microsoft SQL Server 2008: New and Future T-SQL Programmability.  Michael Wang Senior Program Manager Lead SQL Server Engine Microsoft Corporation. Agenda. What’s new in SQL Server 2008 What we are thinking for the future. Data type. What’s New In SQL Server 2008. Date & Time.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Microsoft SQL Server 2008: New and Future T-SQL Programmability' - ull


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
microsoft sql server 2008 new and future t sql programmability

BB25

Microsoft SQL Server 2008: New and Future T-SQL Programmability

 Michael Wang

Senior Program Manager Lead

SQL Server Engine

Microsoft Corporation

agenda
Agenda
  • What’s new in SQL Server 2008
  • What we are thinking for the future
what s new in sql server 2008

Data type

What’s New In SQL Server 2008
  • Date & Time
  • SQL language
  • MERGE
  • GROUPING SET
  • Table value constructor support through the VALUES clause
  • Procedure programming
  • Table Value Parameter
  • Declaring and initializing variables
  • Compound assignment operators
  • Others
  • Object dependency
  • Collation
  • Beyond relational
new date and time types why
New Date And Time Types – Why?

CREATE TABLE Employee {

FirstName VARCHAR(10),

LastName VARCHAR(10),

Birthday DATETIME,

}

SELECT CONVERT(VARCHAR(20), Birthday, 101) AS BirthDay FROM Employee

INSERT INTO T (datetime_col) VALUES (‘1541-01-01’)

INSERT INTO T (datetime_col) VALUES (’12:30:29.1176548’)

CREATE TABLE online-purchase-order {

item-id int,

item-name VARCHAR(30),

qty int,

purchase-time datetime,

purchase-timezonevarchar (10),

}

// For value ‘2005-09-08 12:20:19.345 -08:00’

INSERT INTO online-purchase-order VALUES (…., UDF_DT(..), UDF_TZ(..),..)

new date and time types

DATE

New Date And Time Types
  • Large year range (1~9999)
  • Storage saving
  • Easy programming

CREATE TABLE Employee {

FirstName VARCHAR(10),

LastName VARCHAR(10),

Birthday DATE,

}

SELECT Birthday AS BirthDay FROM Employee

  • TIME
  • Large or optional precision (0 ~ 100ns)
  • Easy programming

INSERT INTO T (datetime_col) VALUES (‘1541-01-01’)

  • DATETIME2
  • Large year range
  • Large or optional precision

INSERT INTO T (datetime_col) VALUES (’12:30:29.1176548’)

  • DATETIMEOFFSET
  • Datetime + time zone offset
  • UTC enabled
  • Easy programming

CREATE TABLE online-purchase-order {

item-id int,

item-name VARCHAR(30),

qty int,

purchase-time datetimeoffset,

}

// For value ‘2005-09-08 12:20:19.345 -08:00’

INSERT INTO online-purchase-order VALUES (…., ‘2005-09-08 12:20:19.345 -08:00’ ,..)

new date and time t ypes client provider support
New Date And Time TypesClient Provider Support
  • Full SNAC (ODBC and OLEDB) support in Katmai
  • Full SqlClient/ADO.net support in Orcas
new merge statement scenario
New MERGE StatementScenario
  • OLTP: Merging recent info from external source
  • Data warehouse: Incremental updates of fact

Source Table

(Stock Trading)

Merged Table

(Stock Holding)

Target Table

(Stock Holding)

INSERT

UPDATE

new merge statement what is it
New MERGE Statement What is it
  • Single statement that combines multiple DML operations
  • Operates on a join between source and target
  • SQL-2006 compliant

Pre-SQL 2008

SQL 2008

UPDATE TGT

SET TGT.quantity += SRC.quantity,

TGT.LastTradeDate = SRC.TradeDate

FROM dbo.StockHolding AS TGT

JOIN dbo.StockTrading AS SRC

ON TGT.stock = SRC.stock;

INSERT INTO dbo.StockHolding (stock, lasttradedate, quantity)

SELECT stock, tradedate, quantity

FROM dbo.StockTrading AS SRC

WHERE NOT EXISTS

(SELECT * FROM dbo.StockHolding AS TGT

WHERE TGT.stock = SRC.stock);

MERGE INTO dbo.StockHolding AS TGT

USINGdbo.StockTrading AS SRC

ONTGT.stock = SRC.stock

WHEN MATCHED AND (t.quantity + s.quantity = 0) THEN

DELETE

WHEN MATCHED THEN

UPDATE SET t.LastTradeDate = s.TradeDate, t.quantity += s.quantity

WHEN NOT MATCHED THEN

INSERT VALUES

(s.Stock,s.TradeDate,s.Quantity)

new merge statement some key points
New MERGE Statement Some key points
  • Existing triggers & constraints continue to work
  • DELETE is SQL Server extension
  • Using primary key or indexed column in ON predicate for better performance
  • Target table can NOT be remote
  • Table hints are applicable
  • Autoparameterization is not supported in MERGE
  • Main use cases
    • Applying property (custom metadata) changes for entities
    • Tracking inventory
    • OLTP UPSERT
new grouping sets clause
New GROUPING Sets Clause
  • Define multiple groupings in the same query
  • Produces a single result set that is equivalent to a UNION ALL of differently grouped rows
  • SQL 2006 standard compatiable

Pre-SQL 2008

SQL 2008

SELECT customerType,Null as TerritoryID,MAX(ModifiedDate)

FROM Sales.CustomerGROUP BYcustomerType

UNION ALL

SELECT Null as customerType,TerritoryID,MAX(ModifiedDate)

FROM Sales.CustomerGROUP BY TerritoryID order by TerritoryID

SELECT customerType,TerritoryID,MAX(ModifiedDate)

FROM Sales.Customer

GROUP BY GROUPING SETS ((customerType), (TerritoryID)) order by customerType

table value constructor support through the values clause
Table Value Constructor Support Through The VALUES Clause
  • Use VALUES clause to construct a set of rows
  • Insert multiple rows based on values in a single INSERT statement
  • SQL 2006 standard compatible

Multi-Row Insert

Define table expressions

INSERT INTO dbo.Customers(custid, companyname, phone, address)

VALUES

  (1, 'cust 1', '(111) 111-1111', 'address 1'),

  (2, 'cust 2', '(222) 222-2222', 'address 2'),

  (3, 'cust 3', '(333) 333-3333', 'address 3'),

  (4, 'cust 4', '(444) 444-4444', 'address 4'),

  (5, 'cust 5', '(555) 555-5555', 'address 5');

SELECT *

FROM

(VALUES

 (1, 'cust 1', '(111) 111-1111', 'address 1'),

 (2, 'cust 2', '(222) 222-2222', 'address 2'),

 (3, 'cust 3', '(333) 333-3333', 'address 3'),

 (4, 'cust 4', '(444) 444-4444', 'address 4'),

 (5, 'cust 5', '(555) 555-5555', 'address 5')

  ) AS C(custid, companyname, phone, address);

new table types and tvp why table types
I don’t want to repeat the same code again and again..New Table Types And TVPWhy Table Types

DECLARE @NewCustomer TABLE

(

[CustomerID] int NULL,

[FirstName] varchar(50) NOT NULL,

[LastName] varchar(50) NOT NULL,

[CompanyName] [nvarchar](128) NULL

)

new table types and tvp new table types
New Table Types And TVPNew Table Types
  • User-defined Table Types
    • A new user defined type
    • Aligned with inline table definition for table variables
    • Can be used for declaring table variables
    • Can define indexes and constraints
  • Benefits
    • Usability, Type Matching, Precise Typing

CREATE TYPE myT AS table (a int, b varchar(100))

new table types and tvp how to work on tablur data pre 2008
New Table Types And TVPHow to work on tablur data pre-2008
  • Using local temporary tables
    • Increasing the disk I/O
    • Being prone to locking and blocking
    • Manually dropping the temporary table
    • Frequent stored procedures re-compilations
  • Using multiple parameters
    • Multiple round trips
    • Stored procedure multiple execution
    • Inefficient code
new table types and tvp tvp table value parameter
New Table Types And TVPTVP – Table Value Parameter
  • Input parameters of Table type on SPs/Functions
  • Optimized to scale and perform better for large data
  • Behaves like BCP inside server
  • a simple programming model
  • Strongly typed
  • Reduce client/server round trips
  • Do not cause a statement to recompile

CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100),qty INT);

CREATE PROCEDURE myProc(@tvpmyTableType READONLY) AS

UPDATE Inventory SET

qty += s.qty

FROM Inventory AS i INNER JOIN @tvp AS tvp

ON i.id = tvp.id

GO

new table types and tvp tvp client stack support
New Table Types And TVPTVP Client Stack Support
  • Fully supported in ADO.Net 3
      • New Parameter type: SqlDbType.Structured
      • Parameters can be passed in multiple ways
        • DataTable
        • Ienumerable<SqlDataRecord> (fully streamed)
        • DbDataReader
  • Supported in ODBC/OLEDB stacks
      • New Parameter Type SQL_SS_Table
      • Familiar Parameter Binding: SQLBindParameter
t sql delighters
T-SQL Delighters …
  • Compound Assignment operators: +=, -=, *=, /=
  • Variable initialization during declaration
  • CAST/CONVERT binary data to hex string literals (i.e. 0xA1BEFE)

UPDATE Inventory SET quantity +=s.quantity

FROM Inventory AS i INNER JOIN Sales AS s ON i.id = s.id

DECLAER @v int = 5;

DECLARE @v1 varchar(10) = ‘xxxxx’;

select ...... from t1, t2 where convert(char(4), t1.col1_of_type_binary,1) = t2.col1_of_type_char

object dependencies
Object Dependencies
  • sys.sql_expression_dependencies
    • New catalog view; replaces sys.sql_dependencies
    • Tracks both schema-bound and non-schema-bound dependencies
    • Tracks cross-database and cross-server references (by name)
  • sys.dm_sql_referenced_entities
    • New dynamic management function; replaces sp_depends
    • Returns a row for each entity referenced by a given entity
    • For example, show me all objects referenced in stored procedure p1
  • sys.dm_sql_referencing_entities
    • New dynamic management function; replaces sp_depends
    • Returns a row for each entity that references a given entity
    • For example, show me all objects that would be broken if I drop table t1
object dependencies find all dependent objects of a procedure

CREATE PROCEDURE p1 @a INT, @b myUDT OUTPUT

AS

DECLARE @x INT, @y INT;

SELECT a, @x = s.foo(b), @y = MAX(c) FROM t1 WHERE a = @a;

EXEC p2;

SET @b = CAST (@x, @y) AS myUDT;

GO

SELECT <see column list below> FROM sys.sql_expression_dependencies

WHERE referencing_id = OBJECT_ID(‘p1’);

Object DependenciesFind all dependent objects of a procedure
object dependencies find all objects that depend on a table
Object DependenciesFind all objects that depend on a table

USE db1

CREATE PROCEDURE dbo.p2

SELECT * FROM t1; …

CREATE PROCEDURE p3

UPDATE dbo.t1…

CREATE VIEW v1

WITH SCHEMABINDING

SELECT t1.*, t2.*

FROM dbo.t1 INNER JOIN dbo.t2 …

CREATE FUNCTION s.foo (@x INT)

RETURNS TABLE AS

BEGIN

SELECT * FROM t1 WHERE a < @x;

END

USE db2

CREATE PROCEDURE p4

-- cross db dependency doesn’t

-- show up as a referencing entity

SELECT * FROM db1..t1;

SELECT referencing_schema_name, referencing_entity_name, referencing_id, is_caller_dependent FROM sys.dm_sql_referencing_entities(‘dbo.t1’);

new collations
New Collations
  • Align with Windows Vista® collations
  • Adding Windows new collations in SQL Server 2008
  • Adding new versions to existing Windows collations (*_100_*)
  • Adding new versions to existing Windows collations with SIGNIFICANT CHANGES
    • Chinese_Taiwan_Stroke_100 and Chinese_Taiwan_Bopomofo_100 will now assign culture-correct weight for each character, specifically the Ext. A + B characters
beyond relational a better store for semi structured data
Beyond RelationalA Better Store for Semi-structured Data
  • // Create a Filtered Indexes
  • // Sparse column
  • Create Table Products(Id int, Type nvarchar(16)…, Resolution intSPARSE, ZoomLengthintSPARSE);
  • // Filtered Indices
  • Create Index ZoomIdx on Products(ZoomLength) where Type = ‘Camera’;
  • // HierarchyID
  • CREATE TABLE [dbo].[Folder]
  • (
  • [FolderNode] HIERARCHYID NOT NULL UNIQUE,
  • [Level] AS [FolderNode].GetLevel() PERSISTED,
  • [Description] NVARCHAR(50) NOT NULL
  • );

2

3

1

4

  • BB07 - SQL Server 2008: Developing for Beyond-Relational Data

5

1

2

3

4

5

spatial goes mainstream
Spatial Goes Mainstream
  • Extends SQL Server with types, operations, and indexing to enable working with spatial geometry
  • Simplifies storage of location data
  • Improves SQL Server as platform for geo-spatial independent software vendors (ISVs)
  • Standards-based data and programming model
  • Based on large UDTs

Planar

Geodetic

BB24 - Microsoft SQL Server 2008: Deep Dive into Spatial Data

what we are thinking for v next
What We Are Thinking For V-Next
  • Our Vision - Best Platform for developing data-tier applications with maximum productivity in terms of ease of use, simplicity, time to solution, TCO
  • Our Focus
evals recordings
Evals & Recordings

Please fill out your evaluation for this session at:

This session will be available as a recording at:

www.microsoftpdc.com

slide31

© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.