TPOV

I’ve been an MCDBA for quite some years now, and since we always had the idea that T-SQL is T-SQL in no matter what SQL Server version, I didn’t pay much attention to all the changes in SQL Server 2005 and SQL Server 2008 T-SQL.

Therefore, it wasn’t ‘till yesterday that I found out about the new OUTPUT Clause in SQL Server 2005 T-SQL.

I was programming some stuff in C# when I noticed that some SQL command’s weren’t behaving as I expected. I was used to inserting data in a table by using the INSERT command, and then fetching the @@IDENTITY value to get to the value of the Identity column in that table;

SqlCommand command1 = new SqlCommand("INSERT INTO people (name, email) VALUES ('tino', 'tino@tino.nl')", sqlConn);

if (command1.ExecuteNonQuery() == 1)

{

 SqlCommand command2 = new SqlCommand("SELECT @@IDENTITY", sqlConn);

 long peopleId = (long)command2.ExecuteScalar();

}

Looks nice, but it the @@IDENTITY value will only be returned in the same context as the original query, otherwise it will return NULL. In my particular case it just didn’t work.

The solution was on my system for over 4 years… It was in the SQL Server books online. It was the OUTPUT Clause.

Now see my new command;

SqlCommand command1 = new SqlCommand("INSERT INTO people (name, email) OUTPUT INSERTED.peopleid VALUES ('tino', 'tino@tino.nl')", sqlConn);

long peopleId = (long)command1.ExecuteScalar();

More information on the OUTPUT Clause can be found here; OUTPUT Clause (SQL 2005) OUTPUT Clause (SQL 2008)