One thing I learned years ago in C programming was the elegance of using operators and simplifying expressions. This sometimes involved doing two things in a single statement, if it makes sense. For example,

x=y++;

Those of you that have done some programming will recognize this as incrementing y and assigning a value to x at the same time. Well not the same time, but in one line.

I knew that you could some things like this in T-SQL, but it didn’t trigger in my mind when someone asked about building their own sequencing table. This person wanted to update a table with an incremented value and return the value to the calling program without causing any locks/blocks.

Paul White posted this fantastic piece of code that illustrates this:

UPDATE  dbo.GPK

SET     @NewID = next_value = next_value + 1

WHERE   table_name = @table_name;

In this code the GPK table is being updated, with the next_value column being incremented. At the same time, @NewID, a variable that is an output parameter for a stored proc in this example, is assigned the value from next_value.

The value assigned is the incremented value, so if next_Value contains a 1 before this is run, @NewID will get 2. The increment/update occurs first, with the new value being assigned to the variable.

I think this is really cool, and it’s something I need to remember for future T-SQL problems.