The T-SQL Cookbook: What's Cool in Microsoft SQL Server 2008 R2 and New in SQL Server Code-Named “Denali”

1. DBI318 The T-SQL Cookbook: What's Cool in Microsoft SQL Server 2008 R2 and New in SQL Server Code-Named “Denali” Tobias Ternström Senior Program Manager Lead Microsoft Corporation

2. Where do I work? Project Server External Apps Excel Content Management Server Applications and Components MOM Visual Studio Dynamics MS CRM Outlook Commerce Server LCS Exchange Biztalk Server Oslo Sharepoint DPM SQL Replication SQL Analysis Services SQL Management Tools SQL Reporting Services SQL Integration Services SQL Server RDBMS Team Connectivity MDAC ODBC, OLE-DB SNAC ODBC, OLE-DB JDBC Ado.Net SQL Client EDM App PlatformT-SQL Language, Data-Tier Application Model, Types, Libraries SQL Engine Storage & AdminStorage Engine, DR etc. Data ProcessingQuery Processor High AvailabilityMirroring, Clustering, etc.

3. Overview • Some reminders about what's cool in Microsoft SQL Server 2008 R2 • What is new in SQL Server Code-Named “Denali”

4. Reminders about Important T-SQL Features in SQL Server 2008 • DATE, TIME, DATETIME2, DATETIMEOFFSET • MERGE • GROUPING SETS • Spatial support through GEOMETRY & GEOGRAPHY types • Table Valued Parameters (TVPs)

5. Over to SQL Server Code-named Denali • New Query & Schema Constructs • Improved Error Handling • Improvements to Dynamic SQL • Additional Scalar Functions • Robust Result set Metadata Discovery

6. New Query & Schema Constructs • Support for Simplified Paging • SELECT...ORDER BY ... OFFSET <expr> ROWSFETCH NEXT <expr>ROWS ONLY • Support for UTF-16, introducing _SC collations • Common Unicode characters occupy 16-bits each (NCHAR / NVARCHAR) • Rarer Unicode characters occupy 2 x 16-bits each • “Supplementary Characters”, “Surrogate Pairs” • Ancient scripts; Music Notation; Math Symbols etc.

7. New Query & Schema Constructs Sequence Generators • New Database Object, similar to the IDENTITY property • Separates number-generation from column and table • ANSI standard compliant implementation CREATE SEQUENCE MySchema.IdSequenceAS INTSTART WITH10000INCREMENT BY 1;GOINSERT INTO Employees (EmployeeId, Name) VALUES (NEXT VALUE FORMySchema.IdSequence,'Jane'); INSERT INTO Contractors (ContractorId, Name) VALUES (NEXT VALUE FOR MySchema.IdSequence, 'John');

8. New Query & Schema Constructs Sequence Generators CREATE SEQUENCE [ schema_name . ] sequence_name [ AS { <built_in_integer_type> | <user-defined_integer_type> } ] [ START WITH <constant> ] [ INCREMENT BY <constant> ] [ MINVALUE <constant> | NO MINVALUE ] [ MAXVALUE <constant> | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE [<constant> ] | NO CACHE ]

9. SELECT NEXT VALUE FOR MySequence ,…. FROM MyTable; • Generates a Predictablenumber of values • Achieved by restricting where in a statement (and in which statements) NEXT VALUE FOR can be used • Semantically correct support for generating values based on an orderthrough the OVER-clause (extension to ANSI)SELECT NEXT VALUE FOR MySeqOVER (ORDER BY OrderDate ASC) AS OrderId ,OrderDate ,CustomerId FROM Orders; • Supported in DEFAULT constraints • Supports fetching a range using sp_sequence_get_range

10. SQL Server Code-named Denali • New Query & Schema Constructs • Improved Error Handling • Improvements to Dynamic SQL • Additional Scalar Functions • Robust Result set Metadata Discovery

11. The Error Handling Quiz • What is returned to the client?a) Error b) Error & Hello • RAISERROR(‘Error‘, 10, 1);PRINT ‘Hello‘; • RAISERROR(‘Error‘, 16, 1);PRINT ‘Hello‘; • RAISERROR(‘Error‘, 20, 1);PRINT ‘Hello‘; • RAISERROR(‘Error‘, 20, 1) WITH LOG;PRINT ‘Hello‘; • CREATE PROC sp1 AS RAISERROR(‘Error‘, 11, 1); PRINT ‘Hello‘; • CREATE PROC sp2 AS RAISERROR(‘Error‘, 10, 1); PRINT ‘Hello‘; • EXEC sp1; • EXEC sp2; • BEGIN TRY EXEC sp1; END TRY BEGIN CATCH END CATCH • BEGIN TRY EXEC sp2; END TRY BEGIN CATCH END CATCH

12. Error Actions != Severity Levels • Error actions (independent of the severity level) • None • Statement abort • Msg 241, Level 16, State 1, Line 1 • Conversion failed when converting date and/or time from character string. • Scope Abort • Batch abort • Transaction abort • Msg 242, Level 16, State 3, Line 2 • The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. • Connection abort

13. What is in Denali? • Introducing THROW which… • always THROWs • is even spelled correctly! • THROW • THROW <number>, <message>, <state>; • Batch aborts if not in SET XACT_ABORT ON where it Transaction-aborts • Does not automatically use sys.messages • And re-THROW • BEGIN CATCH …; THROW;END CATCH

14. SQL Server Code-named Denali • New Query & Schema Constructs • Improved Error Handling • Improvements to Dynamic SQL • Additional Scalar Functions • Robust Result set Metadata Discovery

15. Improvements to Dynamic SQL • Support for defining a contract for returned result set(s) EXECUTE<proc|clrproc|remoteproc|function> [WITH <execute_option>[,...n ]] { RESULT SETS {UNDEFINED|NONE|(<result_sets_definition>)} }  <result_sets_definition> ::= { <result_set_definition> |AS OBJECT [<object_location>.]{table_name| view_name | tvf}|AS TYPE [schema_name.]table_type_name| AS FOR XML | (…) [,...n ]} EXEC(@SQL) WITH RESULT SETS ((ObjectIDBIGINT,NameNVARCHAR(100)) );EXEC(@SQL) WITH RESULT SETS NONE;

16. Enforcing the contract

17. SQL Server Code-named Denali • New Query & Schema Constructs • Improved Error Handling • Improvements to Dynamic SQL • Additional Scalar Functions • Robust Result set Metadata Discovery

18. Additional Scalar Functions • New conversion function for all types: • TRY_CONVERT(data_type[(length)], expression [,style]) • New conversion functions to and from strings: • FORMAT(value, format [,culture]) • PARSE(string_value AS data_type [USING culture]) • TRY_PARSE(string_value AS data_type [,USING culture]) • Other functions: • IIF(booleanexpr, true_value, false_value) • CHOOSE(index,val1,val2 [,valN]) • CONCAT(val1, val2…[,valn])

19. Additional Scalar Functions • New date & time related functions: • EOMONTH(date [, months_to_add]) • DATEFROMPARTS(year, month, day) • TIMEFROMPARTS(hour, minutes, seconds, fractions, scale) • DATETIME2FROMPARTS(year, month, day ,hour, minutes, seconds, fractions, scale) • DATETIMEFROMPARTS (year, month, day, hour, minutes, seconds, miliseconds) • SMALLDATETIMEFROMPARTS(year, month, day, hour, minutes)

20. SQL Server Code-named Denali • New Query & Schema Constructs • Improved Error Handling • Improvements to Dynamic SQL • Additional Scalar Functions • Robust Result set Metadata Discovery

21. Robust Result set Metadata Discovery= “Finally replacing SET FMTONLY ON” • SET FMTONLY ON;IF(…) /*Q1*/ SELECT a{int} FROM t1; ELSE /*Q2*/ SELECT b{int} FROM t2;SET FMTONLY OFF; • EXEC sp_describe_first_result_set @tsql= N' IF(…) /*Q1*/ SELECT a{int} FROM t1; ELSE /*Q2*/ SELECT b{int} FROM t2;'; • What is the big difference??

22. Robust Result set Metadata Discovery • Current option is to use SET FMTONLY ON • SET FMTONLY ON sadly is very broken... • What will this batch return if you ask for its result set metadata using FMTONLY? • IF (...) SELECT a, b FROM t1;ELSE SELECT c, d FROM t2;

23. Robust Result set Metadata Discovery • Replaced by ... • sp_describe_first_result_set • sp_describe_undeclared_parameters • Jedi mind trick! • dm_exec_describe_first_result_set • dm_exec_describe_first_result_set_for_object

24. Robust Metadata Discovery for Result Sets – The Guarantee The semantics of sp_describe_first_result_set provide a guarantee that if the procedure returns the first result-set metadata for some batch B, and that batch is subsequently executed with no relevant schema changes on the server, other than creating a temporary table or table variable in the batch B, between the time sp_describe_first_result_set is called and the time the result set is returned during execution (including schema changes made by the batch B), then either the batch raises an optimization-time or run-time error, the batch returns no result-set or the first result-set returned by the batch will have the same metadata that sp_describe_first_result_set described (the name, nullability and data type can differ as described under section “d” above). In case sp_describe_first_result_setreturnes an empty result-set, the guarantee is that the execution of the batch will return no result-sets.

25. … Simplified • sp_describe_first_result_setanalyzesbatch B and returns result set metadata M. • When batch B is executed, • a result set with metadata M is returned, • no result set is returned, or • an error is returned.

26. Example

27. Questions?

28. Required Slide Speakers, please list the Breakout Sessions, Interactive Discussions, Labs, Demo Stations and Certification Exam that relate to your session. Also indicate when they can find you staffing in the TLC. Related Content • DBI307, Monday @ 4:45PM, Welly Lee Automating Database Migration to Microsoft SQL Server • DBI310, Wednesday @ 10:15AM, Itzik Ben-GanEfficient T-SQL Querying with ItzikBen-Gan • DEV314, Wednesday @ 5PM, Aditya Unnithan & Sarah McDevittMicrosoft SQL Server Developer Tools, Code-Named "Juneau" and the ADO.NET Entity Framework: Best Friends Forever • DBI306, Thursday @ 8:30AM, Rick Negrin & Adrian BethuneUsing Contained Databases and DACs to Build Applications in Microsoft SQL Server Code-Named "Denali" and SQL Azure

29. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Database Platform (DAT) Resources • Visit the updated website for SQL Server® Code Name “Denali” on www.microsoft.com/sqlserverand sign to be notified when the next CTP is available • Follow the @SQLServer Twitter account to watch for updates • Try the new SQL Server Mission Critical BareMetal Hand’s on-Labs • Visit the SQL Server Product Demo Stations in the DBI Track section of the Expo/TLC Hall. Bring your questions, ideas and conversations!

30. Resources • Connect. Share. Discuss. http://northamerica.msteched.com Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers http://microsoft.com/technet http://microsoft.com/msdn

31. Complete an evaluation on CommNet and enter to win!

33. © 2011 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.