Row Constructor, Declare - Initialization and Assignment features of SQL Server 2008

Hi,

Today we will discuss a few new features of SQL Server 2008.

Declare and Initialize variable + compound assignment operators
Row Constructors

Let's start with Declare and Initialize variables part:

SQL Server 2008 allows you to declare and variable and initialise it at the same time without writing SET statement. It enables to initialize variable inline as part of the variable declaration statement instead of using separate DECLARE and SET statments.

Let's look at a few examples:

Declare @TDate Date = getdate()
select @TDate

output = 2009-07-13

Declare @TTime Time = getdate()
select @TTime

output = 09:23:38.1900000

Compound assignment operators help to assign a value to a column without using the traditional way set @var = @var + 1. The various operators are:

+= (Plus Equals)
-= (Minus Equals)
*= (Multiply Equals)
/= (Divide Equals)
%= (Modulo Equals)

These can be used wherever assignment or any simple calculation needs to be done using a numeric variable. Let's look at a few examples:

Declare @intval int=0
set @intval+=10
select @intval

output = 10

DECLARE @price AS MONEY = 10.00;
SET @price += 2.00;
SELECT @price;

output = 12.00

DECLARE @cost AS MONEY = 10.00;
SET @cost -= 2.00;
SELECT @cost;

output = 8.00

Declare @minvar int=1
Declare @maxar int=10
while (@minvar <= @maxar) BEGIN print 'IN LOOP' Set @minvar+=1 END 
output =
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP


--An example of fabonacci series:

Declare @output numeric(20,2)=1;
Declare @maxar int=4
while (@maxar>1)
BEGIN
print 'IN LOOP'
Set @output*=@maxar
set @maxar-=1
END
select @output

output = 24.00 

Row Constructor

Now Let's look at Row constructor or Table value constructor. SQL Server 2008 introduces support for table value constructors through the values clause. We can now insert muliple rows in a table using single VALUES clause.

If a developer of DBA ask the following question:

I don’t want to write INSERT INTO statement for every row. Please help me what to do?

The answer is: USE ROW CONSTRUCTORS

You can avoid INSERT statements for each row. Internally, these are stored in a “constant table” that contains the rows. Only one query is executed. This can speed up some operations, but you should measure for your application to see if this works well for you.

Let's look at an example to understand this feature.

USE AdventureWorks;

IF OBJECT_ID('dbo.Cust', 'U') IS NOT NULL
DROP TABLE dbo.Cust;

CREATE TABLE dbo.Cust
(
custid INT NOT NULL,
companyname VARCHAR(25) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
CONSTRAINT PK_Cust PRIMARY KEY(custid)
);

INSERT INTO dbo.Cust(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

Select * from dbo.Cust


Output =
custid companyname phone address
1 cust 1 (111) 111-1111 address 1
2 cust 2 (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
4 cust 4 (444) 444-4444 address 4
5 cust 5 (555) 555-5555 address 5

One thing to mention here is that even though no explicit transaction is defined here, this INSERT statement is considered as an atomic operation. So if any row fails to enter the table, the entire INSERT operation will fail.

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). The following example demonstrates using the VALUES clause to define a derived table in the context of an outer SELECT statement:

SELECT *
FROM
(VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5')
) AS C(custid, companyname, phone, address);

The outer query can operate on this table expression like any other table expression, including joins, filtering, grouping, and so on.

Hope it must be clear.

Do let me know if there is any question.

Enjoy SQL coding