SQL Server 2008 T-SQL enhanceMents

SQL Server 2008 T-SQL enhanceMents
-----------------------------------------------

1. Initialize the varaible inline as part of the variable declaration statement instead of using separate
DECLARE and SET statements
2. Compound Assignment Operators.

3. Table Value Constructor Support through the Values clause. Use a single VALUES clause to construct a set of rows.
A table value constructor can be used to define table expressions such as key derived tables and CTEs, and can be used where

table expressions are allowed (such as in the FROM clause of a SELECT statement or as the source table in a MERGE statement).

4. Enhanced CONVERT function with Style 0 (default) , Style 1 and Style 2

eg :

SELECT

CONVERT(VARCHAR(12) , 0x49747A696B , 2) ,

CONVERT(VARBINARY(5), '49747A696B' , 2);

This code produces the following output:

49747A696B 0x49747A696B

5. New Date and Time Data Types

4 New data types - DATE, TIME, DATETIME2, and DATETIMEOFFSET
new and Enhanced Functions - SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET

6. Large UDTS ( User Defined Types) case of spatial data
In SQL Server 2005, user-defined types (UDTs) in the CLR were limited to 8,000 bytes, but now 2 GB

7. HIERARCHYID Data Type
The new HIERARCHYID data type in SQL Server 2008 is a system-supplied CLR UDT that can be useful for storing and manipulating

hierarchies

8. Table Types

Table types enable you to save a table definition in the database and use it later to define table variables and parameters to

stored procedures and functions. Because table types let you reuse a table definition, they ensure consistency and reduce

chances for errors

To get metadata information about table types in the database, query the view sys.table_types.

9. Table-Valued Parameters
you cannot set variables and parameters of a table type to NULL.
able-valued parameters are treated internally like table variables. Their scope is the batch (procedure, function). They have several

advantages in some cases over temporary tables and other alternative methods:
· They are strongly typed.
· SQL Server does not maintain distribution statistics (histograms) for them; therefore, they do not cause recompilations.
· They are not affected by a transaction rollback.
· They provide a simple programming model

10. MERGE Statement

The new MERGE statement is a standard statement that combines INSERT, UPDATE, and DELETE actions as a single atomic operation based on

conditional logic

11. GROUPING SETS, CUBE, and ROLLUP Subclauses

eg :
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty

FROM dbo.Orders

GROUP BY GROUPING SETS (
( custid, empid, YEAR(orderdate) ),
( custid, YEAR(orderdate) ),
( empid, YEAR(orderdate) ),
() );

CUBE( a, b, c ) is logically equivalent to: GROUPING SETS((a),(b),(c),(a, b),(a, c),(b, c),(a, b, c),())
For n elements. CUBE produces 2^n grouping sets.


ROLLUP( country, region, city )

is logically equivalent to:

GROUPING SETS((country, region, city),(country, region),(country),())

Notice that cases that have no business value, assuming a hierarchy between the elements—such as (city)—were not produced. There might

be multiple cities with the same name in the world, and even within the same country, so there is no business value in aggregating

them.


12. Grouping Sets Algebra

The following code represents a Cartesian product between two GROUPING SETS subclauses:

GROUPING SETS ( (a, b), (c, d) ), GROUPING SETS ( (w, x), (y, z) )

This code is logically equivalent to:

GROUPING SETS ( (a, b, w, x), (a, b, y, z), (c, d, w, x), (c, d, y, z) )

13. GROUPING_ID Function
The GROUPING_ID function lets you identify the grouping set that each result row belongs to

14. Sparse Columns

Sparse columns are columns that are optimized for the storage of NULLs. To define a column as sparse, specify the SPARSE

attribute as part of the column definition. Sparse columns consume no storage for NULLs, even with fixed-length types; however, when a

column is marked as sparse, storage of non-NULL values becomes more expensive than usual.

15. Filtered Indexes and Statistics


SQL Server 2008 introduces filtered indexes and statistics. You can now create a nonclustered index based on a predicate, and

only the subset of rows for which the predicate holds true are stored in the index B-Tree. Similarly, you can manually create

statistics based on a predicate. The optimizer has the logic to figure out when such filtered indexes and statistics are

useful.Well-designed filtered indexes can improve query performance and plan quality because they are smaller than nonfiltered

indexes. Also, filtered statistics—whether created automatically for a filtered index or manually—are more accurate than

nonfiltered statistics because they need to cover only a subset of rows from the table.