如何在创建表并拥有数据后使用NEWSEQUENTIALID()

Question

有人能帮我一下吗?我有一个表不是唯一的(因为我正在翻找前辈的旧数据库)

我想把它赋值给fruit表中的"ID"字段。

我想继续并获得NEWSEQUENTIALID()设置，这样我就可以看到我正在使用的所有内容。

Answer 1

假设ID的类型为uniqueidentifier，则可以创建另一个默认使用顺序标识符的列。这将填充该列中的值。之后，您可以将这些值复制到id列，然后删除tmp列。输入所有数据后，为id列指定默认值。查看下面的SQL脚本:

——创建一个新列，默认为顺序id

USE [BASKET]
ALTER TABLE [FRUITS]
ADD  [TMPID] UNIQUEIDENTIFIER NOT NULL  CONSTRAINT DF_TMPID DEFAULT NEWSEQUENTIALID()
GO

——用新创建的值更新现有的id列值

UPDATE [FRUITS] SET ID = TMPID GO

——删除约束

ALTER TABLE [FRUITS] DROP CONSTRAINT DF_TMPID GO

——删除temp列

ALTER TABLE [FRUITS] DROP COLUMN TMPID GO 

——指定新插入的默认值

ALTER TABLE [FRUITS]  ADD DEFAULT NEWSEQUENTIALID() FOR ID

——或

ALTER TABLE [FRUITS] ADD CONSTRAINT DF_ROWGUID DEFAULT NEWSEQUENTIALID()  FOR ID;

Answer 2

CREATE PROCEDURE [dbo].[uspGetSequentialGuid] 
AS
DECLARE @SequentialGuids as Table ( SequentialGuid uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY,InitDate  datetime  )
BEGIN
INSERT INTO @SequentialGuids(InitDate) Values(GETDATE());
END
SELECT   SequentialGuid from @SequentialGuids
GO

Answer 3

CREATE PROCEDURE [dbo].[uspGetSequentialGuids](@RequiredGuids as int) 
AS
DECLARE @SequentialGuids as Table ( SequentialGuid uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY,InitDate  datetime  )
Declare @Counter int
SET @Counter = 0
While @Counter < @RequiredGuids
BEGIN
INSERT INTO @SequentialGuids(InitDate) Values(GETDATE());
SET @Counter = @Counter + 1
END
SELECT   SequentialGuid from @SequentialGuids
GO