T-SQL Improvements And Data Types in ms sql 2008

T-SQL Improvements And Data Types in ms sql 2008: by Aamir Hasan AspWeblogs

Microsoft SQL Server 2008 is a new version released in the first half of 2008 introducing new properties and capabilities to SQL Server product family. All these new and enhanced capabilities can be defined as the classic words like secure, reliable, scalable and manageable. SQL Server 2008 is secure. It is reliable. SQL2008 is scalable and is more manageable when compared to previous releases. Now we will have a look at the features that are making MS SQL Server 2008 more secure, more reliable, more scalable, etc. in details.
Microsoft SQL Server 2008 provides T-SQL enhancements that improve performance and reliability. Itzik discusses composable DML, the ability to declare and initialize variables in the same statement, compound assignment operators, and more reliable object dependency information.

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-Valued Parameters

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;
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 @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');


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, 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

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

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]


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


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

"