如何在SQL Server中生成惟一的递增整数值?

I have the following problem. An SQL table stores job items and each item has state. Items can change states and also other properties either together or separately. I want to be able to assign each job item a unique increasing integer (64 or more bits) that will only be assigned when I ask to, not updated on each update like timestamp behaves. So I want to be able to do two operations:

我有以下问题。SQL表存储作业项目，每个项目都有状态。项目可以同时或单独地更改状态和其他属性。我希望能够为每个作业项分配一个惟一的递增整数(64位或更多位)，这个整数只在我请求时分配，而不是在每次更新时进行更新，比如时间戳的行为。我想做两个操作

update some other fields without changing that integer on the row and
更新一些其他字段，而不需要更改该行上的整数。
update state and that integer
更新状态和该整数

and when the integer changes it should be greater than all other such integers ever generated for that column (per-table or per-database will of course do as well).

当整数发生变化时，它应该比为该列生成的所有此类整数都要大(每个表或每个数据库当然也会这样)。

This needs to be scalable so that multiple clients can work with the database without serious penalties.

这需要可伸缩，以便多个客户端可以使用数据库，而不会受到严重的影响。

How can I do that?

我怎么做呢?

2 个解决方案

#1

Sounds like you need a SEQUENCE.

听起来你需要一个序列。

This is decoupled from your table (unlike an IDENTITY) and can be table or database wide: up to you.

这与您的表(不像标识)是分离的，可以是表或数据库宽:由您决定。

Sequences are supported natively in the forthcoming SQL Server 2012, but until then you can emulate one as per this dba.se question: https://dba.stackexchange.com/q/3307/630

在即将到来的SQL Server 2012中，序列是原生支持的，但在此之前，您可以根据这个dba来模拟序列。se问题:https://dba.stackexchange.com/q/3307/630

#2

Look here http://blogs.msdn.com/b/sqlazure/archive/2010/07/15/10038656.aspx. Should help

看这里http://blogs.msdn.com/b/sqlazure/archive/2010/07/15/10038656.aspx。应该帮助

But in a nutshell, you need a field declared as follows in your table:

但是，简单地说，您需要在表中声明如下字段:

Id bigint PRIMARY KEY IDENTITY (1,1)

#1