SQL 序列与身份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10062328/
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
Sequence vs identity
提问by Sleiman Jneidi
SQL Server 2012 introduced Sequenceas a new feature, same as in Oracle and Postgres. Where sequences are preferred over identities? And why do we need sequences?
SQL Server 2012Sequence作为新特性引入，与 Oracle 和 Postgres 相同。序列比身份更受欢迎？为什么我们需要序列？
采纳答案by Arion
I think you will find your answer here
我想你会在这里找到你的答案
Using the identity attribute for a column, you can easily generate auto-incrementing numbers (which as often used as a primary key). With Sequence, it will be a different object which you can attach to a table column while inserting. Unlike identity, the next number for the column value will be retrieved from memory rather than from the disk – this makes Sequence significantly faster than Identity. We will see this in coming examples.
使用列的标识属性，您可以轻松生成自动递增的数字（通常用作主键）。使用 Sequence，它将是一个不同的对象，您可以在插入时将其附加到表列。与 identity 不同的是，列值的下一个数字将从内存而不是从磁盘中检索——这使得 Sequence 比 Identity 快得多。我们将在接下来的示例中看到这一点。
And here:
而在这里：
Sequences: Sequences have been requested by the SQL Server community for years, and it's included in this release. Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence.
序列：SQL Server 社区多年来一直要求序列，并且它包含在此版本中。序列是一个用户定义的对象，它生成一个数字序列。这是一个使用 Sequence 的示例。
and hereas well:
还有这里：
A SQL Server sequence object generates sequence of numbers just like an identity column in sql tables. But the advantage of sequence numbers is the sequence number object is not limited with single sql table.
SQL Server 序列对象生成数字序列，就像 sql 表中的标识列一样。但是序列号的优点是序列号对象不受单个sql表的限制。
and on msdn you can also read more about usage and why we need it (here):
在 msdn 上，您还可以阅读更多有关用法以及我们需要它的原因（此处）：
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.
A sequence is created independently of the tables by using the CREATE SEQUENCE statement. Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance. For information about the options, see CREATE SEQUENCE.
Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function. The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table. The NEXT VALUE FOR function can be used as the default value for a column in a table definition. Use sp_sequence_get_range to get a range of multiple sequence numbers at once.
A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint.
序列是用户定义的模式绑定对象，它根据创建序列的规范生成数值序列。数值序列以定义的间隔按升序或降序生成，并可根据要求循环（重复）。与标识列不同，序列不与表相关联。应用程序引用序列对象以接收其下一个值。序列和表之间的关系由应用程序控制。用户应用程序可以引用一个序列对象并跨多个行和表协调值键。
使用 CREATE SEQUENCE 语句独立于表创建序列。选项使您能够控制增量、最大值和最小值、起点、自动重新启动功能和缓存以提高性能。有关选项的信息，请参阅创建序列。
与插入行时生成的标识列值不同，应用程序可以在插入行之前通过调用 NEXT VALUE FOR 函数获取下一个序列号。即使编号从未插入表中，在调用 NEXT VALUE FOR 时也会分配序列号。NEXT VALUE FOR 函数可用作表定义中列的默认值。使用 sp_sequence_get_range 一次获取多个序列号的范围。
序列可以定义为任何整数数据类型。如果未指定数据类型，则序列默认为 bigint。
回答by user1059637
Sequence and identity both used to generate auto number but the major difference is Identity is a table dependant and Sequence is independent from table.
序列和标识都用于生成自动编号，但主要区别是标识依赖于表，而序列独立于表。
If you have a scenario where you need to maintain an auto number globally (in multiple tables), also you need to restart you interval after particular number and you need to cache it also for performance, here is the place where we need sequence and not identity.
如果您有需要全局维护自动编号的场景（在多个表中），您还需要在特定编号后重新启动间隔，并且还需要缓存它以提高性能，这里是我们需要序列而不是的地方身份。
Given below are the articles defining the real world example of sequence, its implementation and also the difference between sequence and identity.
下面给出了定义序列的真实世界示例的文章，它的实现以及序列和身份之间的区别。
http://raresql.com/2012/04/29/how-sequence-works-in-sql-server-2012/http://raresql.com/2012/05/01/difference-between-identity-and-sequence/
http://raresql.com/2012/04/29/how-sequence-works-in-sql-server-2012/ http://raresql.com/2012/05/01/difference-between-identity-and-序列/
回答by Stagg
Although sequences provide more flexibility than identity columns, I didn't find they had any performance benefits.
尽管序列比标识列提供了更多的灵活性，但我没有发现它们有任何性能优势。
I found performance using identity was consistently 3x faster than using sequence for batch inserts.
我发现使用标识的性能始终比使用序列进行批量插入快 3 倍。
I inserted approx 1.5M rows and performance was:
我插入了大约 150 万行，性能是：
- 14 seconds for identity
- 45 seconds for sequence
- 14 秒身份验证
- 序列 45 秒
I inserted the rows into a table which used sequence object via a table default:
我将行插入到一个表中，该表通过表默认值使用序列对象：
NEXT VALUE for <seq> for <col_name>
NEXT VALUE for <seq> for <col_name>
and also tried specifying sequence value in select statement:
并尝试在 select 语句中指定序列值：
SELECT NEXT VALUE for <seq>, <other columns> from <table>
Both were the same factor slower than the identity method. I used the default cache option for the sequence.
两者都比身份方法慢相同的因素。我为序列使用了默认缓存选项。
The article referenced in Arion's first link shows performance for row-by-row insert and difference between identity and sequence was 16.6 seconds to 14.3 seconds for 10,000 inserts.
Arion 的第一个链接中引用的文章显示了逐行插入的性能，10,000 次插入的同一性和序列之间的差异为 16.6 秒到 14.3 秒。
The Caching option has a big impact on performance, but identity is faster for higher volumes (+1M rows)
缓存选项对性能有很大影响，但标识对于更高的卷（+1M 行）速度更快
See this linkfor an indepth analysis as per utly4life's comment.
请参阅此链接以根据 utly4life 的评论进行深入分析。
回答by Ken
I know this is a little old, but wanted to add an observation that bit me.
我知道这有点旧，但想添加一个让我感到不安的观察。
I switched from identity to sequence to have my indexes in order. I later found out that sequence doesn't transfer with replication. I started getting key violations after I setup replication between two databases since the sequences were not in sync. just something to watch out for before you make a decision.
我从身份切换到序列，以使我的索引井然有序。后来我发现序列不会随着复制而转移。由于序列不同步，我在两个数据库之间设置复制后开始出现密钥冲突。只是在你做出决定之前需要注意的事情。
回答by Greg Gum
I find the best use of Sequences is not to replace an identity column but to create a "Order Number" type of field.
我发现序列的最佳用途不是替换标识列，而是创建“订单号”类型的字段。
In other words, an Order Number is exposed to the end user and may have business rules along with it. You want it to be unique, but just using an Identity Column isn't really correct either.
换句话说，订单号向最终用户公开，并且可能伴随着业务规则。您希望它是唯一的，但仅使用标识列也不完全正确。
For example, different order types might require a different sequence, so you might have a sequence for Internet Order, as opposed to In-house orders.
例如，不同的订单类型可能需要不同的顺序，因此您可能有 Internet 订单的顺序，而不是内部订单。
In other words, don't think of a Sequence as simple a replacement for identity, think of it as being useful in cases where an identity does not fit the business requirements.
换言之，不要将 Sequence 视为身份的简单替代品，而应将其视为在身份不符合业务需求的情况下很有用。
回答by awilbourn
Recently was bit by something to consider for identity vs sequence. Seems MSFT now suggests sequence if you may want to keep identity without gaps. We had an issue where there were huge gaps in the identity, but based on this statement highlighted would explain our issue that SQL cached the identity and after reboot we lost those numbers.
最近有点需要考虑身份与顺序。如果您可能想保持身份而没有间隙，似乎 MSFT 现在建议序列。我们遇到了身份存在巨大差距的问题，但基于此突出显示的声明可以解释我们的问题，即 SQL 缓存了身份，并且在重新启动后我们丢失了这些数字。
Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.
服务器重新启动或其他故障后的连续值 - SQL Server 可能会出于性能原因缓存标识值，并且某些分配的值可能会在数据库故障或服务器重新启动期间丢失。这可能会导致插入时标识值出现间隙。如果间隙不可接受，则应用程序应使用其自己的机制来生成键值。使用带有 NOCACHE 选项的序列生成器可以限制从未提交的事务的间隔。
