SQL Server 2008 - Transact-SQL Improvements

SQL Server 2008 introduces many enhancements to Transact-SQL; Here is a list of some of those enhancements and a brief description of those enhancements.

 

Declare Statement

With DECLARE, SQL Server 2008 offers the ability to assign values to a variable in its declaration. You can assign values to most data types, including SQLCLR data types, but not to TEXT, NTEXT, or IMAGE data types. Here’s an example:

DECLARE @MyName varchar(20)=’Michael’

 

GROUP BY

The GROUP BY clause now includes GROUPING SETS, ROLLUP, and CUBE operators that provide varying levels of aggregate information. A new function, GROUPING_ID(), returns more grouping-level information than the existing GROUPING() function.

MERGE Statement

A new MERGE Transact-SQL statement allows you to perform INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. The syntax allows you to perform multiple actions based on the results of that join.

You can selectively merge the contents of two tables. To merge the previous example, MyTable, with the contents of a new table, MyTable2, which contains duplicate data as well as new data, I used the MERGE statement

CREATE TABLE MyTable2

  (ID int, Item varchar(20))

  INSERT INTO MyTable2 VALUES

  (1, ‘Bike’), (2, ‘Van’), (4, ‘Motorcycle’)

  GO

  MERGE MyTable2 AS TargetTable

  USING (SELECT ID, Item FROM MyTable)

  SourceTable

  ON (TargetTable.ID = SourceTable.ID)

  WHEN TARGET NOT MATCHED

  THEN INSERT VALUES (ID, Item)

  WHEN SOURCE NOT MATCHED

  THEN UPDATE SET Item = Item;

  GO

 

Stored Procedure

Stored procedures and functions can now include table-valued parameters that can reference user-defined table types. Using table-valued parameters, you can send multiple rows of data to a SQL Server statement or routine without creating a temporary table.

Row Constructors

Row constructors in Transact-SQL provide a simplified syntax that allows multiple row inserts within a single INSERT statement. Using row constructors, the entire insertion operation executes inside a transaction, implicit in the single INSERT statement itself.

Sometimes called Table Value Constructors, SQL Server 2008’s new Row Constructor lets you insert multiple rows by using a single statement. In the following example, I used a single INSERT statement to insert three rows in a table named MyTable:

CREATE TABLE MyTable

  (ID int, Item varchar(20))

INSERT INTO MyTable VALUES

  (1, ‘Bike’), (2, ‘Car’), (3, ‘Truck’)

 

Compound Operators

Compound Operators provide a concise mechanism to perform logical and arithmetic operations on an operand and assign the result of the operation to that operand, for example, @x += 2 adds 2 to variable @x. In addition, you can now declare a variable and assign its value in the same statement.

SQL Server 2008 supports compound assignments and provides these compound operators: +=, -=, /=, %=, &=, |=, and ^=. The following statement uses the new compound addition operator to add two plus two:

  DECLARE @MyNumber int = 2

  SET @MyNumber += @myNumber

 

References:

1.       http://channel9.msdn.com/Learn/Courses/SQL2008R2TrainingKit/T_SQL

2.       http://www.sqlmag.com/article/tsql3/t-sql-enhancements-in-sql-server-2008.aspx

3.       Demo: Inserting, Updating, and Deleting Data by Using MERGE

4.       Demo: Table-Valued Parameters in SQL Server 2008

5.       Demo: Row Constructors In SQL Server 2008

6.       Demo: Grouping Sets in SQL Server 2008