THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

2008: Initializing Table Data with Row Constructors

Well, I am just discovering this feature, mostly because I never saw it demoed at any of the sessions I have attended so far on 2008.  Not that it was kept particularly hidden, I have seen the title before, but I hadn't tried it out, or seen the depth that they have "finally" implemented.

Tonight, I am working on my chapter where I create some tables as part of a big example, and I had the code from the 2005 version of the book (and I add the primary key to the table later in the book, as well as other constraints, so don't judge me!):

CREATE TABLE Inventory.MovieRating (
       MovieRatingId             int NOT NULL,
       Code                           varchar(20) NOT NULL,
       Description                 varchar(200) NULL,
       AllowYouthRentalFlag bit NOT NULL
)
GO

INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (0, 'UR','Unrated',1)
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUE   (1, 'G','General Audiences',1),
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (2, 'PG','Parental Guidance',1),
INSERT INTO Inventory.MovieRating(MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (3, 'PG-13','Parental Guidance for Children Under 13',1),
INSERT INTO Inventory.MovieRating(MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (4, 'R','Restricted, No Children Under 17 without Parent',0)

(Another variety is to use:

INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
SELECT 0, 'UR','Unrated',1
UNION ALL
SELECT 1, 'G','General Audiences',1
UNION ALL
SELECT 3, 'PG-13','Parental Guidance for Children Under 13',1
UNION ALL
SELECT 4, 'R','Restricted, No Children Under 17 without Parent',0

But that is not that much better (certainly a little better).  I felt for the book that using VALUES was the more "proper" way to do it.  However, now, in the 2008 edition, I obviously have to change all of the code to use the latest and greatest syntax, so I use row constructors, and this turns into:

INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag)
VALUES (0, 'UR','Unrated',1),
       (1, 'G','General Audiences',1),
       (2, 'PG','Parental Guidance',1),
       (3, 'PG-13','Parental Guidance for Children Under 13',1),
       (4, 'R','Restricted, No Children Under 17 without Parent',0)

And it just strikes me at how...simple this is, and how readable this is.  I know I have typed the UNION ALL stuff hundreds, possibly thousands (well hundreds more like) of times in the forums over the past year and a half dummying up data for someone who was asking for help but couldn't take the time to do it for us.

Published Wednesday, December 05, 2007 8:50 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

Louis,

I was asked this same exact question by a developer at work who showed me some MySQL code the other day, I told him to use UNION ALL

Next year I can tell him that he can use it (I doubt that he will rewrite his app  :-()

December 5, 2007 9:06 PM
 

Linchi Shea said:

This is indeed a very useful feature. It's used widely in DB2.

December 5, 2007 9:33 PM
 

Peter DeBetta's SQL Programming Blog said:

My esteemed colleague Louis Davidson just wrote about a new feature in SQL Server 2008 known as row constructors

December 5, 2007 10:32 PM
 

Peter W. DeBetta said:

I just posted some follow up on my blog (see trackback above for direct link)...

December 5, 2007 10:34 PM
 

Denis Gobo said:

I decided to do a quick post In addition to the posts by Peter and Louis . And yes I copied Louis' table

December 6, 2007 11:55 AM
 

Paul Nielsen said:

Hi Louis, I agee 100%. Even though this seems to be a small detail, it impacts much of our experience as database developers. This, along with the ability declare and initialize a variable may turn out to be my favorite new features, small though they be.

December 6, 2007 1:27 PM
 

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM
 

Rob Gray said:

I'm just finding out about this awesome syntax.  Great!

December 6, 2012 8:39 PM
 

Gul said:

GREATE FEATURE IN 2008

January 4, 2013 12:55 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement