Is it possible to create some GUID NOT ONLY in default clause of a table-CodePudding

I want to generates some GUID with newsequentialid() functions instaed of newid()

CREATE TABLE AssetPoints 
(
    Id int IDENTITY(1,1) PRIMARY KEY, 
    AssetOwner uniqueidentifier,
    assetValue int, 
    RV rowversion
);  
GO   

declare @i int = 0
while (@i < 10)
begin
    INSERT INTO AssetPoints (AssetOwner, assetValue) VALUES (newsequentialid(), 1000   @i);
    set @i = @i   1
end
GO

but got 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 it possible to create some GUID in sequential order use newsequentialid()? Or use newsequentialid() NOT ONLY in default clause of a table?

CodePudding user response：

or used newsequentialid() NOT ONLY in default clause of a table ?

If you want, say, 1000 sequential guids perhaps for a temporary reason, or in a scenario where you don't really have a permanent table to put them in then you can still abide by SQLServer's "only in a column default" insistence by making a table variable with that default, and inserting to it (omitting the guid column so it generates defaults):

DECLARE @t TABLE(g UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(), x CHAR(1));
INSERT INTO @t(x) SELECT TOP 1000 'x' FROM some_big_table
SELECT g FROM @t

You could then use these 1000 guids for whatever you need.. Perhaps you want to insert them in some table that needs a guid but doesn't generate its own (or generates random ones) etc, so you can do like INSERT INTO PersonTest SELECT g, 'John', Smith' FROM @t ...

_{There are other methods for generating an arbitrary 1000 rows; I just picked on a simple one here of selecting 1000 rows from some big table with more than 1000 rows. If you don't have a table with more than 1000 rows, look at other ways}

CodePudding user response：

The error is telling you the problem. You don't define the NEWSEQUENTIALID() in the INSERT, you define the column with a default value of NEWSEQUENTIALID().

This is also noted in the documentation:

NEWSEQUENTIALID() can only be used with DEFAULT constraints on table columns of type uniqueidentifier.

...

NEWSEQUENTIALID cannot be referenced in queries.

What you should be doing, is something like this:

CREATE TABLE dbo.AssetPoints (Id int IDENTITY(1, 1)
                                  CONSTRAINT PK_AssetPoints PRIMARY KEY, --Always name your constraints
                              AssetOwner uniqueidentifier
                                  CONSTRAINT DF_AssetOwner --Always name your constraints
                                      DEFAULT NEWSEQUENTIALID(),
                              assetValue int,
                              RV rowversion);
GO


INSERT INTO dbo.AssetPoints (assetValue)
VALUES (1000),
       (1001),
       (1002);
GO

SELECT *
FROM dbo.AssetPoints;
GO