SQL Server 2005 - 使用生成的序列而不是标识列? [英] SQL Server 2005 - using generated sequences instead of Identity columns?

问题描述

我真的在考虑从 SQL Server 2005 中的标识列切换到某种类型的序列生成器(据我所知，2005 年没有任何内置功能可以做到这一点；可能会在 2011 年推出?)获取插入行的唯一 ID.

I'm really thinking about switching away from Identity columns in SQL Server 2005 and going with some type of sequence generator (as far as I know there's nothing built in to 2005 to do this; may be coming in 2011?) to get unique id's for inserted rows.

是否有模型实现或最佳实践?我会遇到锁定问题吗?与仅使用 Identity 列相比有哪些缺点?

Is there a model implementation or best practice for this? Will I encounter locking issues? What are the down sides compared to just using Identity columns?

推荐答案

是的，SQL 11 有 SEQUENCE 对象，参见 SQL Server v.Next (Denali):使用 SEQUENCE.

Yes, SQL 11 has SEQUENCE objects, see SQL Server v.Next (Denali) : Using SEQUENCE.

可以创建手动序列，但不推荐.制作序列生成器的技巧是在序列表上使用 UPDATE WITH OUTPUT.这是伪代码:

Creating manual sequences is possible, but not recommended. The trick to do a sequence generator is to use UPDATE WITH OUTPUT on a sequences table. Here is pseudo-code:

CREATE TABLE Sequences (
    Name sysname not null primary key, 
    Sequence bigint not null default 0);
GO

CREATE PROCEDURE sp_getSequence
    @name sysname,
    @value bigint output
AS
    UPDATE Sequences
    SET Sequence = Sequence + 1
     OUTPUT @value = INSERTED.Sequence
    WHERE Name = @name;
GO

我省略了一些细节，但这是总体思路.然而，有一个很大的问题:任何请求序列上的下一个值的事务都会锁定该序列直到它提交，因为它会在序列值上放置一个更新锁.这意味着所有事务在插入值时都必须依次序列化，而由此导致的性能下降在实际生产部署中是无法忍受的.

I left out some details, but this is the general idea. However, there is a huge problem: any transaction requesting the next value on a sequence will lock that sequence until it commits, because it will place an update lock on the sequence value. This means that all transactions have to serialize after each other when inserting values and the performance degradation that results is unbearable in real production deployments.

我更希望您坚持使用 IDENTITY 类型.虽然并不完美，但它们远比您自己所能实现的要好得多.

I would much rather have you stick with the IDENTITY types. While not perfect, they are far better than what you can achieve on your own.

这篇关于SQL Server 2005 - 使用生成的序列而不是标识列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！