DaveWentzel.com            All Things Data

Row Constructors

Starting with SQL Server 2008 we now have row constructors.  "Row constructors"....wow...just using that phrase in a sentence at your job should be worth at least a 5% raise.  Think of row constructors as a string of VALUES clauses that will INSERT multiple rows into a table.  Here's an example.  But, there's an even cooler use.

I often find myself creating views of "allowable values" or constants similar to this...

SELECT 'N' AS StatusCode, 'NONE' AS Description

UNION ALL

SELECT 'P','PENDING'

UNION ALL

SELECT 'A','ACCEPTED'

UNION ALL

SELECT 'R','REJECTED'

Note that this is similar to what we would have done in earlier versions of SQL Server to INSERT multiple rows into a table in one shot using INSERT...SELECT syntax.  Well, if I can use row constructor syntax to insert rows into a table, then I can use it to make my little allowable values views too.  

 

SELECT AVL.StatusCode, AVL.Description
FROM 
(
VALUES 
('N', 'NONE')
,('P','PENDING')
,('A','ACCEPTED')
,('R','REJECTED')
,('I','INCORPORATED')
,('J','INCLUDED')
,('S','OTHER1')
,('T','OTHER2')
,('U','OTHER3')
) AS AVL (StatusCode, Description);
Tags: 

Add new comment