Docstoc

SQL Server 2008 T-SQL

Document Sample
SQL Server 2008 T-SQL Powered By Docstoc
					T-SQL Improvements
And Data Types
Module Overview
  •   Table-Valued Parameters
  •   T-SQL Assign and Increment
      Operators
  •   Row Constructors
  •   Grouping Sets
  •   MERGE statement
  •   Dependency Views
  •   Performance Enhancements
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
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
Table-Valued
Parameters
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!
TVP Implementation and
Performance
•   Table Variables materialized in TEMPDB
      − Faster than parameter arrays, BCP APIs still fastest
               4000

               3500

               3000

               2500                                                       Parameter List
        Time




               2000
                                                                          Table-Valued
               1500
                                                                          Parameter
               1000                                                       Bulk Insert
                500

                  0
                      100   200   400   800   1000 2000 4000 8000 10000

                                         Parameters
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');
Row Constructors
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
Grouping Sets
GROUPING
SETS, ROLLUP, CUBEROLLUP and
• SQL Server 2008 - ANSI-syntax
    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
GROUPING_ID and
GROUPING produce non-homogeneous
• Grouping Sets can
    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
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
MERGE Statement
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
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
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
MERGE Determinism
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
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
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
Resources
•   MSDN Webcast: New T-SQL
    Programmability Features in SQL Server
    2008 (Event ID: 1032357753)
© 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.

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:298
posted:3/19/2010
language:English
pages:24
Description: Microsoft Training Kit for SQL Server 2008, Microsoft training kit for ASP.NET MVC