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

问题描述

我真的在考虑从SQL Server 2005的Identity列切换到某种类型的序列生成器(据我所知，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.

是否有模型实现或最佳实践?我会遇到锁定问题吗?与仅使用身份"列相比，不利之处是什么?

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对象，请参见

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-使用生成的序列而不是Identity列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！