4

I am adding a new GUID/Uniqueidentifier column to my table.

ALTER TABLE table_name
ADD VersionNumber UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWSEQUENTIALID()
GO

And when ever a record is updated in the table, I would want to update this column "VersionNumber". So I create a new trigger

CREATE TRIGGER [DBO].[TR_TABLE_NAMWE]
ON [DBO].[TABLE_NAME]
AFTER UPDATE
AS 
BEGIN 
    UPDATE TABLE_NAME
    SET VERSIONNUMBER=NEWSEQUENTIALID()
    FROM TABLE_NAME D
    JOIN INSERTED I ON D.ID=I.ID/* some ID which is used to join*/
END
GO

But just realized that NEWSEQUENTIALID() can only be used with CREATE TABLE or ALTER TABLE. I got this 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 there a workaround for this ?

Edit1: Changing NEWSEQUENTIALID() to NEWID() in the trigger solves this, but I am indexing this column and using NEWID() would be sub-optimal

ram
  • 11,468
  • 16
  • 63
  • 89

1 Answers1

10

As you say its only available under certain conditions, you could do something nasty like:

DECLARE @T TABLE (G UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID())
INSERT @T OUTPUT INSERTED.G VALUES (DEFAULT)

Does it have to be a GUID? if you use a rowversion you get the same functionality without needing a trigger as well as better indexing performance.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • 1
    +1 it's unfortunate, indeed - but `NewSequentialId()` is only available as a DEFAULT value for a column - seems to be the same in 2008 R2, too :-( – marc_s May 10 '10 at 14:50
  • 2
    Microsoft hasn't touched this problem in 8 years; it's abismal. SSMS 2012 still throws errors about validating the column whenever you try to modify a table that has newsequentialid() as its default. These workarounds involving creating temporary tables just to get a unique id are unacceptable, although this is probably the best workaround so far. – Triynko Nov 02 '13 at 00:42