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.
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’
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.
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’)
MERGE MyTable2 AS TargetTable
USING (SELECT ID, Item FROM MyTable)
ON (TargetTable.ID = SourceTable.ID)
WHEN TARGET NOT MATCHED
THEN INSERT VALUES (ID, Item)
WHEN SOURCE NOT MATCHED
THEN UPDATE SET Item = Item;
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 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 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