SQL SCOPE_IDENTITY() 用于 GUID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1509947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SCOPE_IDENTITY() for GUIDs?
提问by bplus
Can anyone tell me if there is an equivalent of SCOPE_IDENTITY()when using GUIDs as a primary key in SQL Server?
谁能告诉我SCOPE_IDENTITY()在 SQL Server 中使用 GUID 作为主键时是否有等价物？
I don't want to create the GUID first and save as a variable as we're using sequential GUIDs as our primary keys.
我不想先创建 GUID 并保存为变量，因为我们使用顺序 GUID 作为我们的主键。
Any idea on what the best way to retrieve the last inserted GUID primary key?
关于检索最后插入的 GUID 主键的最佳方法有什么想法吗？
回答by Rob Garrison
You can get the GUID back by using OUTPUT. This works when you're inserting multiple records also.
您可以使用 OUTPUT 取回 GUID。这在您插入多条记录时也有效。
CREATE TABLE dbo.GuidPk (
ColGuid uniqueidentifier NOT NULL DEFAULT NewSequentialID(),
Col2 int NOT NULL
)
GO
DECLARE @op TABLE (
ColGuid uniqueidentifier
)
INSERT INTO dbo.GuidPk (
Col2
)
OUTPUT inserted.ColGuid
INTO @op
VALUES (1)
SELECT * FROM @op
SELECT * FROM dbo.GuidPk
Reference: Exploring SQL 2005's OUTPUT Clause
回答by Daniel
There is no SCOPE_IDENTITY() equivalent when using GUIDs as primary keys, but you can use the OUTPUT clause to achieve a similar result. You don't need to use a table variable for output.
使用 GUID 作为主键时，没有 SCOPE_IDENTITY() 等效项，但您可以使用 OUTPUT 子句来实现类似的结果。您不需要使用表变量进行输出。
CREATE TABLE dbo.GuidTest (
GuidColumn uniqueidentifier NOT NULL DEFAULT NewSequentialID(),
IntColumn int NOT NULL
)
GO
INSERT INTO GuidTest(IntColumn)
OUTPUT inserted.GuidColumn
VALUES(1)
The example above is useful if you want to read the value from a .Net client. To read the value from .Net you would just use the ExecuteScalar method.
如果您想从 .Net 客户端读取值，上面的示例很有用。要从 .Net 读取值，您只需使用 ExecuteScalar 方法。
...
string sql = "INSERT INTO GuidTest(IntColumn) OUTPUT inserted.GuidColumn VALUES(1)";
SqlCommand cmd = new SqlCommand(sql, conn);
Guid guid = (Guid)cmd.ExecuteScalar();
...
回答by anishMarokey
you want to use NEWID()
你想使用 NEWID()
declare @id uniqueidentifier
set @id = NEWID()
INSERT INTO [dbo].[tbl1]
([id])
VALUES
(@id)
select @id
but clustered index problem are there in GUID . read this one tooNEWSEQUENTIALID().These are my ideas ,think before use GUID as primary Key. :)
但聚集索引问题存在于 GUID 中。也读这个NEWSEQUENTIALID()。这些是我的想法，在使用 GUID 作为主键之前先考虑一下。:)
回答by Joe
CREATE TABLE TestTable(KEY uniqueidentifier, ID VARCHAR(100), Name VARCHAR(100), Value tinyint);
Declare @id uniqueidentifier ;
DECLARE @TmpTable TABLE (KEY uniqueidentifier);
INSERT INTO [dbo].[TestTable]
([ID], [Name], Value])
OUTPUT INSERTED.KEY INTO @TmpTable
VALUES(@ID, @Name, @Value);
SELECT @uniqueidentifier = KEY FROM @TmpTable;
DROP TABLE TestTable;
回答by TravisWhidden
Using this thread as a resource, I created the following for use within a trigger:
使用此线程作为资源，我创建了以下内容以在触发器中使用：
DECLARE @nextId uniqueIdentifier;
DECLARE @tempTable TABLE(theKey uniqueIdentifier NOT NULL DEFAULT NewSequentialID(), b int);
INSERT INTO @tempTable (b) Values(@b);
SELECT @nextId = theKey from @tempTable;
Might help someone else doing the same thing. Curious if anyone has anything bad to say performance wise if this is not a good idea or not.
可能会帮助其他人做同样的事情。如果这不是一个好主意，是否有人对性能有任何不好的说法。
