In a stored procedure (SQL Server 2008 R2 SP2) is it possible to return a NewSequentialID() without a temp table variable?

1.3k views Asked by At

In a stored procedure (using SQL Server 2008 R2 SP2) is it possible to return a NewSequentialID() without a temp table variable?

I can successfully obtain the NewSequentialID() by using a temp table:

Getting Value of NEWSEQUENTIALID() on Insert

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 a temp table method?
  • Does anyone know of a reason why Microsoft implemented a difference between NEWSEQUENTIALID() to work like NEWID()?
  • Anyone know if there's a chance Microsoft will update NEWSEQUENTIALID() to work like NEWID()?

Geo

UPDATE -- I'm not sure why Microsoft choose to implement the method in this manner, since they state that, "NEWSEQUENTIALID is a wrapper over the Windows UuidCreateSequential function"... but it appears that there is no non-temp-variable table method. (At least as of yet.)
Thanks for everyone's comments / answers. [Moderator Note:] I'm not sure what to do with a question when the answer is "not possible". So I'm going to give @marc_s credit for detailing a workaround.

2

There are 2 answers

1
marc_s On BEST ANSWER

For now - newsequentialid() can only be used as a default constraint on a column. That's what the error message pretty clearly says, too.

So in order to get your sequential GUID's - you must have a table. No other way to do this. And no other way in SQL Server 2012, either.

I have no idea nor any information as to why there's such a difference, and why Microsoft chose to implement it this way....

Update:

OK, so you need to get that value that is being inserted into your table - how about using the OUTPUT clause?

Something like:

DECLARE @NewIDs TABLE (NewSeqID UNIQUEIDENTIFIER)

INSERT INTO dbo.YourTable(list-of-columns)
OUTPUT INSERTED.NewSeqID INTO @NewIDs(NewSeqID)
VALUES (.........)

This way, the output from the INSERT operation - the newly created sequential GUIDs - is being stored into that table variable, and you can use that, return it, slice it - whatever you like!

0
soynerdito On

the official Microsoft saying is:

NEWSEQUENTIALID() can only be used with DEFAULT constraints on table columns of type uniqueidentifier. For example: CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID())

described here http://msdn.microsoft.com/en-us/library/ms189786.aspx

NewID generates a random number and the other is the next sequential number.