Let’s look at a scenario you’ll need to be able to handle if you hope to pass the 70-433.  This scenario requires you to understand the IDENTITY property for a column as well as how to return the identities created for rows inserted.

Let’s start by creating a table to store widgets.  We want an ID for each widget as well as a name.  Since we want to use the ID column as an IDENTITY, we have to make the ID an integer.  My usual setup for IDENTITY columns is start at 1, and increment each new row by one.  That way, each ID will emulate natural numbers.

This would be the table I’d create to solve the problem.

CREATE TABLE widget (
     widgetID INT IDENTITY(1,1)
   , name VARCHAR(25)
)

A couple things I’d like to point out here, I name my tables as singular form nouns.  I name views with the plural form.  I’m also a fan of camel backed notation.  I start with a lower case letter, and the second word is upper case.  Acronyms are all uppercase.

Reserved words are always UPPER CASE.

Now we have our table.  How can we insert 5 rows and get the ID for all 5 rows inserted?

SELECT SCOPE_IDENTITY()?

SCOPE_IDENTITY() only gives you the last value inserted into the IDENTITY column of a table.

INSERT INTO widget
SELECT 'Item One' UNION
SELECT 'Item Two' UNION
SELECT 'Item Three' UNION
SELECT 'Item Four' UNION
SELECT 'Item Five' 

SELECT SCOPE_IDENTITY()

This only gives you 5.  We want to see all the identities inserted into the table.

Enter the OUTPUT clause

While not nearly as impressive as Bruce Lee entering the fight.  The OUTPUT clause is the perfect fighter tool for this job.

INSERT INTO widget
OUTPUT inserted.widgetID
SELECT 'Item One' UNION
SELECT 'Item Two' UNION
SELECT 'Item Three' UNION
SELECT 'Item Four' UNION
SELECT 'Item Five'

Gives the results:

widgetID
6
7
8
9
10

And that’s the solution we were looking for.

If you’re a trivia buff, you probably already know this, but Enter the Dragon was the first movie Jackie Chan was in.  Check out the scene where Bruce heads underground and start fighting off tons of minions.  Watch it slowly.  You’ll see Jackie get his neck snapped.  He’s on screen for all of five seconds, so look sharp.