T-SQL Improvements And Data Types

1. T-SQL Improvements And Data Types

2. Module Overview • Table-Valued Parameters • T-SQL Assign and Increment Operators • Row Constructors • Grouping Sets • MERGE statement • Dependency Views • Performance Enhancements

3. Table-Valued Parameters • Inserts into structures with 1-N cardinality problematic • One order -> N order line items • "N" is variable and can be large • Don't want to force a new order for every 20 line items • One database round-trip / line item slows things down • No ARRAY data type in SQL Server • XML composition/decomposition used as an alternative • Table-valued parameters solve this problem

4. Table Types • SQL Server has table variables • DECLARE @t TABLE (id int); • SQL Server 2008 adds strongly typed table variables • CREATE TYPE mytab AS TABLE (id int); • DECLARE @t mytab; • Parameters must use strongly typed table variables

5. Table-Valued Parameters demo

6. Table Variables are Input Only • Declare and initialize TABLE variable DECLARE @t mytab; INSERT @t VALUES (1), (2), (3); EXEC myproc @t; • Procedure must declare variable READONLY CREATE PROCEDURE usetable ( @t mytab READONLY ...) AS INSERT INTO lineitems SELECT * FROM @t; UPDATE @t SET... – no!

7. TVP Implementation and Performance • Table Variables materialized in TEMPDB • Faster than parameter arrays, BCP APIs still fastest

8. T-SQL Syntax Enhancements • Single statement declare and initialize DECLARE @iint = 4; • Compound Assignment Operators SET @i += 1; • Row constructors DECLARE @t TABLE (id int, name varchar(20)); INSERT INTO @t VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue');

9. Row Constructors demo

10. Grouping Sets • Grouping Sets allow multiple GROUP BY clauses in a single SQL statement • Multiple, arbitrary, sets of subtotals • Single read pass for performance • Nested subtotals provide ever better performance • Grouping Sets are an ANSI-standard • COMPUTE BY is deprecated

11. Grouping Sets demo

12. GROUPING SETS, ROLLUP, CUBE • SQL Server 2008 - ANSI-syntax ROLLUP and CUBE • Pre-2008 non-ANSI syntax is deprecated • WITH ROLLUP produces n+1 different groupings of data • where n is the number of columns in GROUP BY • WITH CUBE produces 2^n different groupings • where n is the number of columns in GROUP BY • GROUPING SETS provide a "halfway measure" • Just the number of different groupings you need • Grouping Sets are visible in query plan

13. GROUPING_ID and GROUPING • Grouping Sets can produce non-homogeneous sets • Grouping set includes NULL values for group members • Need to distinguish by grouping and NULL values • GROUPING (column expression) returns 0 or 1 • Is this a group based on column expr. or NULL value? • GROUPING_ID (a,b,c) is a bitmask • GROUPING_ID bits are set based on column expressions a, b, and c

14. MERGE Statement • Multiple set operations in a single SQL statement • Uses multiple sets as input • MERGE target USING source ON ... • Operations can be INSERT, UPDATE, DELETE • Operations based on • WHEN MATCHED • WHEN NOT MATCHED [BY TARGET] • WHEN NOT MATCHED [BY SOURCE] • ANSI SQL 2006 compliant - with extensions

15. MERGE Statement demo

16. More on MERGE • MERGE statement can reference a $action column • Used when MERGE used with OUTPUT clause • Multiple WHEN clauses possible • For MATCHED and NOT MATCHED BY SOURCE • Only one WHEN clause for NOT MATCHED BY TARGET • MERGE can be used with any table source • A MERGE statement causes triggers to be fired once • Rows affected includes total rows affected by all clauses

17. MERGE Performance • MERGE statement is transactional • No explicit transaction required • One Pass Through Tables • At most a full outer join • Matching rows = when matched • Left-outer join rows = when not matched by target • Right-outer join rows = when not matched by source

18. MERGE and Determinism • UPDATE using a JOIN is non-deterministic • If more than one row in source matches ON clause, either/any row can be used for the UPDATE • MERGE is deterministic • If more than one row in source matches ON clause, its an error

19. MERGE Determinism demo

20. Keeping Track of Dependencies • New dependency views replace sp_depends • Views are kept in sync as changes occur • sys.dm_sql_referenced_entities • Lists all named entities that an object references • Example: which objects does this stored procedure use? • sys.dm_sql_referencing_entities • Lists all named entities that use an object • Example: which objects use this table? • Can see references at OBJECT, DATABASE DDL TRIGGER, SERVER DDL TRIGGER level • sys.sql_expression_dependencies replaces sys.sql_dependencies at database level

21. Performance Enhancements • MERGE and GROUPING SETS offer improvements • Less scans through table • Table-valued parameters offer improvements • Less round trips to database • Improvements for data warehouse queries • Earlier predicate filtering • Multiple bitmap iterators per query • Plan Guide Improvements • Easier to create plan guides • Plan guides on DML statements

22. Review • Strongly typed table-valued parameters help the database round trip problem • Grouping Sets allow arbitrary group by clauses for subtotals and totals • MERGE statement allows set-to-set comparison and multiple deterministic operations • ANSI standard compliance with extensions • Object reference tracking makes schema and procedural code versioning less error-prone

23. Resources • MSDN Webcast: New T-SQL Programmability Features in SQL Server 2008 (Event ID: 1032357753)

24. © 2009 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.