DATABASE

Transact-SQL in SQL Server 2008 : Row Constructors

7/28/2011 3:51:44 PM

Variable Assignment in DECLARE Statement

In SQL Server 2008, you can now set a variable’s initial value at the same time you declare it. For example, the following line of code declares a variable named @ctr of type int and set its value to 100:

DECLARE @ctr int = 100

Previously, this functionality was only possible with stored procedure parameters. Assigning an initial value to a variable required a separate SET or SELECT statement. This new syntax simply streamlines the process of assigning an initial value to a variable. The value specified can be a constant or a constant expression, as in the following:

DECLARE @start_time datetime = getdate()

You can even assign the initial value via a subquery, as long as the subquery returns only a single value, as in the following example:

declare @max_price money = (select MAX(price) from titles)

The value being assigned to the variable must be of the same type as the variable or be implicitly convertible to that type.

Compound Assignment Operators

Another new feature that streamlines and improves the efficiency of your T-SQL code is compound operators. This is a concept that has been around in many other programming languages for a long time, but has now finally found its way into T-SQL. Compound operators are used when you want to apply an arithmetic operation on a variable and assign the value back into the variable.

For example, the += operator adds the specified value to the variable and then assigns the new value back into the variable. For example,

SET @ctr += 1

is functionally the same as

SET @ctr = @ctr + 1

The compound operators are a quicker to type, and they offer a cleaner piece of finished code. Following is the complete list of compound operators provided in SQL Server 2008:

+=Add and assign
-=Subtract and assign
*=Multiply and assign
/=Divide and assign
%=Modulo and assign
&=Bitwise AND and assign
^=Bitwise XOR and assign
|=Bitwise OR and assign

Row Constructors

SQL Server 2008 provides a new method to insert data to SQL Server tables, referred to as row constructors. Row constructors are a feature that can be used to simplify data insertion, allowing multiple rows of data to be specified in a single DML statement. Row constructors are used to specify a set of row value expressions to be constructed into a data row.

Row constructors can be specified in the VALUES clause of the INSERT statement, in the USING clause of the MERGE statement, and in the definition of a derived table in the FROM clause. The general syntax of the row constructor is as follows:

VALUES ( { expression | DEFAULT | NULL |} [ ,...n ] ) [ ,...n ]

Each column of data defined in the VALUES clause is separated from the next using a comma. Multiple rows (which may also contain multiple columns) are separated from each other using parentheses and a comma. When multiple rows are specified, the corresponding column values must be of the same data type or implicitly convertible data type. The following example shows the row constructor VALUES clause being used within a SELECT statement to define a set of rows and columns with explicit values:

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) )
AS MyTable(a, b);
GO
a b
----------- -----------
1 2
3 4
5 6
7 8
9 10

The VALUES clause is commonly used in this manner to populate temporary tables but can also be used in a view, as shown in Listing 1.

Listing 1. Using the VALUES Clause in a View
create view book_types
as
SELECT type, description
FROM (VALUES ('mod_cook', 'Modern Cooking'),
('trad_cook', 'Traditional Cooking'),
('popular_comp', 'Popular Computing'),
('biography', 'Biography'),
('business', 'Business Development'),
('children', 'Children''s Literature'),
('fiction', 'Fiction'),
('nonfiction', 'NonFiction'),
('psychology', 'Psychology and Self Help'),
('drama', 'Drama and Theater'),
('lit crit', 'Literay Criticism')
) AS type_lookup(type, description)
go

Defining a view in this manner can be useful as a code lookup table:

select top 10
convert(varchar(50), title) as title, description
from titles t
inner join
book_types bt
on t.type = bt.type
order by title_id desc
go

title description
---------------------------------------------------------------------------
Sushi, Anyone? Traditional Cooking
Fifty Years in Buckingham Palace Kitchens Traditional Cooking
Onions, Leeks, and Garlic: Cooking Secrets of the Traditional Cooking
Emotional Security: A New Algorithm Psychology and Self Help
Prolonged Data Deprivation: Four Case Studies Psychology and Self Help
Life Without Fear Psychology and Self Help
Is Anger the Enemy? Psychology and Self Help
Computer Phobic AND Non-Phobic Individuals: Behavi Psychology and Self Help
Net Etiquette Popular Computing
Secrets of Silicon Valley Popular Computing

The advantage of this approach is that unlike a permanent code table, the view with the VALUES clause doesn’t really take up any space; it’s materialized only when it’s referenced. Maintaining it involves simply dropping and re-creating the view rather than having to perform inserts, updates, and deletes as you would for a permanent table.

The primary use of row constructors is to insert multiple rows of data in a single INSERT statement. Essentially, if you have multiple rows to insert, you can specify multiple rows in the VALUES clause. The maximum number of rows that can be specified in the VALUES clause is 1000. The following example shows how to use the row constructor VALUES clause in a single INSERT statement to insert five rows:

insert sales (stor_id, ord_num, ord_date, qty, payterms, title_id)
VALUES ('6380', '1234', '3/26/2010', 50, 'Net 30', 'BU1032'),
('6380', '1234', '3/26/2010', 150, 'Net 30', 'PS2091'),
('6380', '1234', '3/26/2010', 25, 'Net 30', 'CH2480'),
('6380', '1234', '3/26/2010', 30, 'Net 30', 'FI2046'),
('6380', '1234', '3/26/2010', 10, 'Net 30', 'FI6318')

As you can see, this new syntax is much more concise and simple than having to issue five individual INSERT statements as you would have had to do in versions of SQL Server prior to SQL Server 2008.

The VALUES clause can also be used in the MERGE statement as the source table. Listing 2 uses the VALUES clause to define five rows as the source data to perform INSERT/UPDATE operations on the store_inventory table .

Listing 2. Using the VALUES Clause in a MERGE Statement
MERGE
INTO store_inventory as s
USING
(VALUES
('A011', 'CH3348', 41 , getdate()),
('A011', 'CH2480', 125 , getdate()),
('A011', 'FI0392', 1100 , getdate()),
('A011', 'FI2046', 1476 , getdate()),
('A011', 'FI1872', 520 , getdate())
) as i (stor_id, title_id, qty, update_dt)
ON s.stor_id = i.stor_id
and s.title_id = i.title_id
WHEN MATCHED and s.qty <> i.qty
THEN UPDATE
SET s.qty = i.qty,
update_dt = getdate()
WHEN NOT MATCHED
THEN INSERT (stor_id, title_id, qty, update_dt)
VALUES (i.stor_id, i.title_id, i.qty, getdate())
OUTPUT $action,
isnull(inserted.title_id, '') as src_titleid,
isnull(str(inserted.qty, 5), '') as src_qty,
isnull(deleted.title_id, '') as tgt_titleid,
isnull(str(deleted.qty, 5), '') as tgt_qty
;
go

$action src_titleid src_qty tgt_titleid tgt_qty
---------- ----------- ------- ----------- -------
INSERT CH2480 125
UPDATE CH3348 41 CH3348 24
UPDATE FI0392 1100 FI0392 1176
UPDATE FI1872 520 FI1872 540
INSERT FI2046 1476
Other  
 
Most View
What To Do When Your PC Is Slow (Part 2)
Lenovo Yoga 13 Convertible Review – High Performance Laptop
Arctic Accelero Hybrid - Liquid Along With Air Cooling System For Graphics Card (Part 1)
The Zync Z1000 - Yet Another Budget Tablet
Switching To A Stand-Up Desk (Part 2)
SQL Server 2012 : Exploring SQL CLR - Your First SQL CLR Stored Procedure, CLR Stored Procedures and Server-Side Data Access
Programming Windows Services with Microsoft Visual Basic 2008 : Service Notification
Samsung Galaxy Note 10.1 - Distinguishes Itself In A Crowded Market
ASP.NET State Management : The View State of a Page (part 3) - Changes in the ASP.NET View State
Toshiba Qosmio X870-11Q – An Impressive Desktop-Replacement Laptop
Top 10
Using Exchange Server 2010 Antispam Tools (part 6) - Sender Reputation
Using Exchange Server 2010 Antispam Tools (part 5) - Sender Filtering, Sender ID
Using Exchange Server 2010 Antispam Tools (part 4) - IP Block and Allow Lists, Recipient Filtering , Tarpitting
Using Exchange Server 2010 Antispam Tools (part 3) - IP Block and IP Allow Providers
Using Exchange Server 2010 Antispam Tools (part 2) - Content Filtering
Using Exchange Server 2010 Antispam Tools (part 1) - Enabling Antispam Agents for Hub Transport Servers , Enabling Automatic Updates for the Antispam Signatures
Windows Server 2012 : Implementing Group Policy preferences (part 4) - Windows Settings extensions,Control Panel Settings extensions
Windows Server 2012 : Implementing Group Policy preferences (part 3) - Understanding preferences - Item-level targeting, Configuring a preference item
Windows Server 2012 : Implementing Group Policy preferences (part 2) - Understanding preferences - Common options, Using environment variables
Windows Server 2012 : Implementing Group Policy preferences (part 1) - Understanding preferences - Preference categories, Configuring preferences