Getting Value of NEWSEQUENTIALID() on Insert

By: Brian Dobberteen

Recently, I was writing a stored procedure that, at one point, performs an insert into a table that has a GUID (uniqueidentifier) primary key. The default value for this column is set to NEWSEQUENTIALID().

Further along in the stored procedure, I needed the value of this newly-inserted GUID to use in another INSERT statement. Fortunately, T-SQL provides us with precisely what we need – the OUTPUT clause.

A quick demo:

The table DDL:

Example portion of sproc:

Thanks to the bit highlighted above on lines 7 and 15, we now have a reference to the newly inserted GUID and can use it as needed.

Additionally, the use of newsequentialid() here lessens the impact of using a GUID clustered index.


2 Responses to “Getting Value of NEWSEQUENTIALID() on Insert”

  • George B Says:

    Perhaps I’m old school, but I try to refrain from using temp tables unless absolutely necessary… though this might be a case where it is absolutely necessary…

    IF I try:
    DECLARE @NewSequentialID UNIQUEIDENTIFIER;
    SET @NewSequentialID = NEWID()
    … it works as expected.

    IF I try:
    DECLARE @NewSequentialID UNIQUEIDENTIFIER;
    SET @NewSequentialID = NEWSEQUENTIALID()
    … I receive the following error:
    The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type ‘uniqueidentifier’ in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

    Is the ONLY solution to use your temp table method?… Any chance Microsoft will update NEWSEQUENTIALID() to work like NEWID(), or do you know of a reason why they can’t?

    George

    NOTE: Using MSSQL 2008 R2 SP2

  • brian Says:

    I would speculate that newsequentialid() cannot be called in the same way as newid() because without a table, how would the DB engine know which GUID would be the next in sequence?

Leave a Reply