获取插入行的 PK Guid 的最佳方法 [英] Best way to get PK Guid of inserted row

问题描述

我已阅读关于获取插入行的标识.我的问题有点相关.

I've read this question about getting the identity of an inserted row. My question is sort of related.

有没有办法获取插入行的 guid?我正在使用的表有一个 guid 作为主键(默认为 newid)，我想在插入行后检索该 guid.

Is there a way to get the guid for an inserted row? The table I am working with has a guid as the primary key (defaulted to newid), and I would like to retrieve that guid after inserting the row.

Guids 是否有 @@IDENTITY、IDENT_CURRENT 或 SCOPE_IDENTITY 之类的东西?

Is there anything like @@IDENTITY, IDENT_CURRENT or SCOPE_IDENTITY for Guids?

推荐答案

您可以使用 OUTPUT 功能将默认值返回到参数中.

You can use the OUTPUT functionality to return the default values back into a parameter.

CREATE TABLE MyTable
(
    MyPK UNIQUEIDENTIFIER DEFAULT NEWID(),
    MyColumn1 NVARCHAR(100),
    MyColumn2 NVARCHAR(100)
)

DECLARE @myNewPKTable TABLE (myNewPK UNIQUEIDENTIFIER)

INSERT INTO 
    MyTable
(
    MyColumn1,
    MyColumn2
)
OUTPUT INSERTED.MyPK INTO @myNewPKTable
VALUES
(
    'MyValue1',
    'MyValue2'
)

SELECT * FROM @myNewPKTable

我不得不说，使用唯一标识符作为主键时要小心.对 GUID 进行索引的性能极差，因为任何新生成的 guid 都必须插入到索引的中间，并且很少只添加到最后.SQL2005 中有NewSequentialId() 的新功能.如果您的 Guid 不需要晦涩难懂，那么它是一种可能的替代方案.

I have to say though, be careful using a unique identifier as a primary key. Indexing on a GUID is extremely poor performance as any newly generated guids will have to be inserted into the middle of an index and rrarely just added on the end. There is new functionality in SQL2005 for NewSequentialId(). If obscurity is not required with your Guids then its a possible alternative.

这篇关于获取插入行的 PK Guid 的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！