Transcript Html Overview

SQL Server 2008
TSQL Improvements &
Data Types
Presented by Tarek Ghazali
IT Technical Specialist
Microsoft SQL Server MVP, MCTS
Microsoft Web Development MCP
ITIL V3 Foundation Certified
© 2009 Tarek Ghazali. All rights reserved.
Overview








New Date and Time Data Types
Table-Valued Parameters
T-SQL Assign and Increment Operators
Row Constructors
Grouping Sets
MERGE statement
Dependency Views
Performance Enhancements
New Date and Time Data
Types

Larger Value Space
– Current DATETIME - 1753-9999 Years
– Current DATETIME - 0.00333 Second
Accuracy
– New Date Types - 0001-9999 Years
– New Date/Time Types - Precisions to 100
nanoseconds
New Date and Time Data
Types (Cont.)




SQL Server 2008 extends date/time
support
Variable Precision Saves Space
Separate Date and Time Saves Space
ANSI Compatible
Date/Time Types
Compatibility

New Data Types Use Same T-SQL
Functions
– DATENAME (datepart, date)
– DATEPART (datepart,date)
– DATEDIFF (datepart, startdate, enddate)
– DATEADD (datepart, number, date)

Datepart can also be microsecond,
nanosecond, TZoffset
Date/Time Types
Compatibility (Cont.)
– MONTH
– DAY
– YEAR
– CONVERT extensions
Date Time Library
Extensions

Higher precision date/time uses
– SYSDATETIME
– SYSUTCDATETIME
– SYSDATETIMEOFFSET

Original date/time uses
– GETDATE, GETUTCDATE,
CURRENT_TIMESTAMP
– ISDATE(datetime/smalldatetime)
Date Time Library
Extensions (Cont.)

Special functions for
DATETIMEOFFSET
– SWITCHOFFSET(datetimeoffset,
timezone)
– TODATETIMEOFFSET(datetime,
timezone)
Localization Functions

All Date/Time Types Support
Localization
– SET DATEFIRST
– SET DATEFORMAT
– SET LANGUAGE - affects some date/time
functions
– @@DATEFIRST
– @@LANGUAGE
Date/Time and Strings




Dates are input and output in string
format
String (varchar) can be output through
CAST/CONVERT
CONVERT has extensions for
date/time types
Dates are input in a variety of formats
– Some of language-sensitive, some not
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
Table-Valued Parameters
(Cont.)

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
Common challenge: Passing list of values to SP/FN
Problem: No ARRAY data type
SQL Server 2005
SQL Server 2008
Parsing string of delimited values
@p = '1,2,3,4,5,…'
Shredding XML
temp table outside the SP
Table Value Parameter
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 Variables are Input
Only

Declare and initialize TABLE variable
– DECLARE @t mytab;
– INSERT @t VALUES (1), (2), (3);
– EXEC myproc @t;
Table Variables are Input
Only (Cont.)

Procedure must declare variable
READONLY
– CREATE PROCEDURE usetable (
– @t mytab READONLY ...)
– AS
– INSERT INTO lineitems SELECT * FROM
@t;
– UPDATE @t SET... -- no!
T-SQL Syntax
Enhancements

Single statement declare and initialize
– DECLARE @i int = 4;


Increment Operators: SET @i += 1;
Row constructors
– DECLARE @t TABLE (id int, name
varchar(20));
– INSERT INTO @t VALUES
– (1, 'Fred'), (2, 'Jim'), (3, 'Sue');
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
Common challenge: Many grouping sub-totals required
from the same table
SQL Server 2005
SELECT a, sum(q)
FROM T
GROUP BY a
UNION ALL
SELECT a, b, sum(q)
FROM T
GROUP BY a, b
UNION ALL
SELECT a, b, c, sum(q)
FROM T
GROUP BY a, b, c
SQL Server 2008
SELECT a, b, c, sum(q)
FROM T
GROUP BY
GROUPING SETS
(
(a),
(b),
(a, b, c)
)
GROUPING SETS,
ROLLUP, and 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
GROUPING SETS,
ROLLUP, & CUBE (Cont.)

WITH CUBE produces 2^n different
groupings
– where n is the number of columns in
GROUP BY

GROUPING SETS provide a "halfway
measure"
– More groupings than ROLLUP, but less
than CUBE

Grouping Sets are visible in query plan
GROUPING_ID and
GROUPING

Grouping Sets can produce nonhomogeneous sets
– Grouping set includes NULL values for
group members
– Need to distinguish by grouping and
NULL values
GROUPING_ID and
GROUPING (Cont.)

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
MERGE Statement (Cont.)

Operations based on
– WHEN MATCHED
– WHEN [TARGET] NOT MATCHED
– WHEN SOURCE NOT MATCHED

ANSI SQL 2006 compliant - with
extensions
What is to merge data?
Source
Merged Data
Delete
Update
Insert
MERGE Statement


SQL Server 2005:
Multiple DML Statements:
BEGIN TRAN
SQL Server 2008:
A Single DML Statement
MERGE tbl
USING s ON tbl.ID = s.ID
UPDATE tbl INNER JOIN s
WHEN MATCHED
THEN UPDATE
INSERT tbl LEFT OUTER JOIN
WHEN NOT MATCHED
THEN INSERT
DELETE tbl RIGHT JOIN s
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
COMMIT
More on MERGE

MERGE statement can reference a
$action column
– Used when MERGE used with OUTPUT
clause

Multiple WHEN clauses possible
– For MATCHED and SOURCE NOT
MATCHED
– Only one WHEN clause for TARGET NOT
MATCHED
More on MERGE (Cont.)



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 target not
matched
– Right-outer join rows = when source not
matched
MERGE and Determinism

UPDATE using a JOIN is nondeterministic
– 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
Keeping Track of
Dependencies



New dependency views replace
sp_depends
Dependency 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?
Keeping Track of
Dependencies (Cont.)

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
Performance
Enhancements

MERGE and GROUPING SETS offer
improvements
– Less scans through table

Table-valued parameters offer
improvements
– Less round trips to database
Performance
Enhancements (Cont.)

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



New SQL types increase the utility,
improve storage requirements of date
time type series
Strongly typed table-valued
parameters help the database round
trip problem
Grouping Sets allow arbitrary group by
clauses for subtotals and totals
Review (Cont)

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
Demo
Resources & Questions

Microsoft Resources:
– http://msdn.microsoft.com/sqlserver/
– http://www.microsoft.com/sql/community

Contact me:
– [email protected]

Download Presentation:
– http://www.sqlmvp.com