Practical uses of new T-SQL functionality in SQL Server 2012 & Windows Azure SQL Database

1. Practical uses of new T-SQL functionality in SQL Server 2012 & Windows Azure SQL Database Tobias TernströmLead Program ManagerSQL Database Systems

2. Overview • Key generation • Exceptions & Messages • Calculations & Aggregations • Common tasks • Updates to Windows Azure SQL Database

3. Key generation • Use cases • Unique keys across multiple tablesCustomers & Employees  Contacts • Client-side/distributed-system key generationPrep files with related items for import • Options • GUIDs • Table to manage the keys • NEW:Sequences

4. CREATE SEQUENCE [ schema_name . ] sequence_name [ AS { <data_type>} ] [ STARTWITH<constant> ] [ INCREMENTBY<constant> ] [ MINVALUE<constant> | NO MINVALUE ] [ MAXVALUE<constant> | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE[ <constant> ] | NOCACHE ] NEXT VALUE FOR [ schema_name . ] sequence_name [ OVER (<over_order_by_clause>) ] EXEC sp_sequence_get_range… Sequence Generators • New Database Object, similar to the IDENTITY property • Separates number-generation from table/column • ANSI SQL standard compliant implementation

5. GUIDs Separate table Sequences • Use any data type for keys(for ex. strings) • Can guarantee a continuous range of values (no wholes) • Including ranges • Serializes number generation across transactions • Supports all integer types, starting from TINYINT up to DECIMAL(38, 0) • Supports range reservation • Caching “knob” for performance tuning • Guaranteed to be globally unique • Can be made sequential • Caveats! • Really hard to guess…(when not sequential) Pros • Cannot guarantee a continuous range of values • E.g. can “lose” values because of dirty shutdowns or rollbacks • Large; requires 16 bytes storage • Many systems don’t natively support GUIDs • Causes fragmentation • Sequential generation cannot be guaranteed even with NEWSEQUENTIALID() • Access to the identifier is serialized across transactions • Performance issue • Not native RDBMS functionality, more code Cons

6. Exceptions & Messages • Use cases: • Send progress updates to the application • Log events • Raise & handle exceptions • Options: • RAISERROR • xp_logevent • Logging table • NEW:THROW

7. New error handling with THROW • THROW <number>, <message>, <state>; • always aborts the batch • Batch aborts if not in SET XACT_ABORT ON where it Transaction-aborts • Does not automatically use sys.messages • is even spelled correctly! • re-THROW • BEGIN CATCH …;THROW;END CATCH

8. RAISERROR THROW xp_logevent Logging table • Easy to use • Similar behavior to other programming languages • Re-throw capabilities • Logs events without sending a message to the client • Flexible, supports the attributes you specify • Flexible • Well known Pros • Less flexible than RAISERROR Cons • Multiple behaviors, easy to get it wrong • Not supported on SQL Database • Transactions need to be managed so log records are not rolled back

9. Calculations & Aggregations • Use cases: • Include aggregations in detailsCumulative sum, averages etc. • Reference other rows in a calculationFinding gaps, trends, etc. • Options: • Imperative programming patterns • Correlated sub queries • NEW: Window Functions

10. Calculations across rows • Referencing same row is simple • Transaction cost is $1.5/tran., what was the total amount? • Amount + 1.5 AS TotalAmount • What about other rows? • Current bank balance? • Current account balance? • Number of days since last tran.? • Amount of previous transaction?

11. Window Functions - Different frame sizes Calculate the average amount of the last 3 transactions 120.000 310.000 350.000 341.666 The FRAME The FRAME The FRAME Window FRAME The FRAME The FRAME The FRAME The FRAME Window PARTITION 191.666 56.666 108.333 100.000 1758.333 1700.000

12. Window Functions Multiple partitions, e.g. for calculations for different accounts 120 215 465 540 The FRAME Window FRAME 665 Window PARTITIONs Window PARTITIONs 840 500 550 575 700

13. Window Functions • Aggregation • AVG, CHECKSUM_AGG, COUNT, COUNT_BIG, MIN, MAX, SUM, STDEV, STDEVP, VAR, VARP • User-defined CLR Aggregates (excl. windowing) • Analytical • ROW_NUMBER, NTILE, RANK, DENSE_RANK, CUME_DIST,PERCENT_RANK • Distribution • PERCENTILE_CONT, PERCENTILE_DISC • Offset • LAG, LEAD, FIRST_VALUE,LAST_VALUE

14. Imperative approach Correlated sub queries Window Functions • Follows the SQL set based paradigm • Improved optimization • Easy to write • Following the SQL set based paradigm • Approach that is commonly used in other programming languages Pros • Not always the optimal solution • Not all queries can be easily rewritten to leverage window functions • Not set based • Only individual queries are optimized, not the operation as a whole • Many lines of code  Higher risk for bugs • Bad performance due to plan with N2 complexity solution • Non-trivial to write Cons

15. Common tasks • Use cases: • Transform data to and from strings • Constructing date instances • Handle conversion errors • Paging • Options: • Write yourself • NEW: Additional scalar functions

16. New Scalar Functions • New conversion functions for all types: • TRY_CONVERT(data_type[(length)], expression [,style]) • TRY_CAST(expression ASdata_type[(length)]) • New conversion functions to and from strings: • FORMAT(value, format [,culture]) • PARSE(string_valueASdata_type [USINGculture]) • TRY_PARSE(string_valueASdata_type [USINGculture]) • Other functions: • IIF(boolean_expr, true_value, false_value) • CHOOSE(index, val1, val2,... [,valN]) • CONCAT(val1, val2,… [,val N])

17. New 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, milliseconds) • SMALLDATETIMEFROMPARTS(year, month, day, hour, minutes) • DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)

18. Query Constructs / Dynamic SQL enhancements SELECT ... • ORDER BY ... • OFFSET<expr> ROWSFETCH NEXT<expr> ROWS ONLY • OFFSET / FETCH • Support for paging result sets • Enforce contract for Result Sets for EXEC 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 ]}

19. Coming • KILL <spid> • WITH XMLNAMESPACES • ORIGINAL_LOGIN, HOST_NAME, HOST_ID • DDL Triggers: EVENTDATA() • sys.column_type_usages, sys.parameter_type_usages Updates to Windows Azure SQL Database This year • Recursive Triggers • Common Default Options on CREATE TABLE & INDEX • Support for SQL Database as a Linked Server • From SQL Server • DBCC SHOW_STATISTICS • FW Rules @ DB Level

20. Questions?