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

1. BB25 Microsoft SQL Server 2008: New and Future T-SQL Programmability  Michael Wang Senior Program Manager Lead SQL Server Engine Microsoft Corporation

2. Agenda • What’s new in SQL Server 2008 • What we are thinking for the future

3. 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

4. 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(..),..)

5. 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’ ,..)

6. New Date And Time TypesClient Provider Support • Full SNAC (ODBC and OLEDB) support in Katmai • Full SqlClient/ADO.net support in Orcas

7. demo New Date & Time manipulation

8. 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

9. 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)

10. 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

11. demo New MERGE statement

12. 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

13. 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);

14. 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 )

15. 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))

16. demo New Table Types

17. 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

18. 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

19. 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

20. demo New Table Value Parameter

21. 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

22. 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

23. 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

24. 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’);

25. 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

26. 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

27. 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

28. 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

29. Q & A ?

30. Evals & Recordings Please fill out your evaluation for this session at: This session will be available as a recording at: www.microsoftpdc.com

31. © 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.