What's New in T-SQL - Pt. 2

Well, like all good things, I have the best intentions of posting daily, and what happens? Life, work, the real world invades. Sorry to be so long adding and improving the blog here.

Let's look at some of the other new features that are now part of T-SQL.

SQL Server 2008 introduces the concept of row constructors. This allows us to create a table or table data type variable and then populate it with multiple sets of data in the same statement.

DECLARE @Product TABLE
(
    ProductID INT,
    ProductName NVARCHAR(64),
    ProductType INT,
    ProductCategory INT
);

--Old way
INSERT INTO @Product (ProductID, ProductName, ProductType, ProductCategory)
VALUES (1,'Widget1',1,1);
INSERT INTO @Product (ProductID, ProductName, ProductType, ProductCategory)
VALUES (2,'Widget2',1,2);
INSERT INTO @Product (ProductID, ProductName, ProductType, ProductCategory)
VALUES (3,'Widget3',2,3);

--New way
INSERT INTO @Product (ProductID, ProductName, ProductType, ProductCategory)
VALUES (4,'Gadget1',3,1), (5,'Gadget2',3,2), (6,'Gadget3',4,1);

Notice the difference! We can insert multiple rows of data in a single insert statement now. You can also use row constructors as pseudo tables if you specify a table name and column names for the set of values in the VALUE clause, as below.

SELECT * FROM (
VALUES(1,'Widget1',1,1),(2,'Widget2',1,2),(3,'Widget3',2,3)
)
Product (ProductID, ProductName, ProductType, ProductCategory);


Output:
ProductID   ProductName ProductType ProductCategory
----------- ----------- ----------- ---------------
1           Widget1     1           1
2           Widget2     1           2
3           Widget3     2           3

(3 row(s) affected)

Another new feature introduced to T-SQL by SQL Server 2008 is Single Statement Variable Declaration and Assignment. This feature allows to declare a variable and assign it's value in the same statement. Similar to other .Net languages.

--assign when declared
DECLARE @myInt INT=1, @MyName NVARCHAR(64)=N'Dan';

--assign from a query return value
DECLARE @myTable TABLE(
    ID INT,
    Name NVARCHAR(64)
);
INSERT INTO @myTable(ID, Name)VALUES(1, 'Dan'),(2, 'Bob');
DECLARE @yourName NVARCHAR(64)=
    (SELECT Name FROM @myTable WHERE ID=2);

One thing to note is that the order of precedence is not enforced when the DECLARE statement is processing. Therefore you cannot use a variable value from the same line to assign to another variable.

The last item for today's post will be Compound Assignment Operators.  Just like C++ and C#, T-SQL now has the compound assignment operators.

Here is a table describing the operators and what they do.

OPERATOR                          DESCRIPTION 
+=                                Add and assign 
-=                                Subtract and assign 
*=                                Multiply and assign 
/=                                Divide and assign 
%=                                Modulo and assign 
&=                                Bitwise AND and assign 
|=                                Bitwise logical OR and assign 
^=                                Bitwise exclusive OR and assign

Compound operators work on the value already assigned to the variable and then use the operator to apply the right-side value to the current variable value.

--assign when declared
DECLARE @myInt INT=1, @MyName NVARCHAR(64)=N'Dan';

--assign from a query return value
DECLARE @myTable TABLE(
    ID INT,
    Name NVARCHAR(64)
);
INSERT INTO @myTable(ID, Name)VALUES(1, 'Dan'),(2, 'Bob');
DECLARE @yourName NVARCHAR(64)=
    (SELECT Name FROM @myTable WHERE ID=2);

Result1     Result2
----------- -------
20          1

(1 row(s) affected)


--Bitwise example
DECLARE @x TINYINT=10,@y TINYINT=2;
SET @x^=@y;  --Exclusive OR of @x by @y
SELECT @x AS Result;

Result
------
8

(1 row(s) affected)

 

Well, that will rap things up for today. I will endeavor to do better at posting daily. Feel free to leave your comments, whether they are suggestions, requests, or just feedback, they are always welcome.