T-SQL Enhancements in SQL Server 2008

SQL Server 2008’s new statements extend the T-SQL feature set. Let’s take a guided tour of some of the most important statements. Later, in upcoming columns, I’ll point out more T-SQL enhancements you’ll find useful.

1. 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’

2. Compound Assignment Operators

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

3. Row Constructor

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’)

4. MERGE Statement

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

where MyTable2 is the merge target (i.e., the table to be updated with merged data) and MyTable is the merge source. If the value in the ID column is matched, the Item value is updated. Otherwise a new row is inserted in MyTable2.

“SQL Server 2008: Enter the Data Collector,” InstantDoc ID 99420

YOUR SAVVY ASSISTANT - The Missing Link to IT Resources I'm not a T-SQL guru like Itzik Ben-Gan, and I certainly don't know as much as Bill McEvoy and Michael Otey. To be honest, I think T-SQL sounds like the name of some lesser-known Star Wars robot--maybe a friend of C3PO or a servant droid on the sideliens. Lucky for you, we've got a list of helpful T-SQL resources in the online learning path for Michael Otey's article "T-SQL Enhancements in SQL Server 2008." Lucky for me, T-SQL improvements aren't the only changes in SQL Server 2008, so I can point you to the following articles for more information: “New Data Types in SQL Server 2008,” InstantDoc ID 97686 “SQL Server 2008 Boosts Build-in Encryption,” InstantDoc ID 98080 “Compression in SQL Server 2008,” InstantDoc ID 98123 “SQL Server 2008 Business Intelligence Enhancements,” InstantDoc ID 98467 “Familiar SQL Server Tools in a New Wrapper,” InstantDoc ID 98836