序列与身份 [英] Sequence vs identity

问题描述

SQL Server 2012 引入了 Sequence 作为新功能，与 Oracle 和 Postgres 中的相同.序列比身份更受欢迎?为什么我们需要序列?

SQL Server 2012 introduced Sequence as a new feature, same as in Oracle and Postgres. Where sequences are preferred over identities? And why do we need sequences?

推荐答案

我想你会找到答案 这里

使用列的身份属性，您可以轻松生成自动递增的数字(通常用作主键).和序列，它将是一个不同的对象，您可以将其附加到插入时的表列.与身份不同，下一个数字列值将从内存而不是从磁盘中检索 –这使得 Sequence 比 Identity 快得多.我们会看到这在接下来的例子中.

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.

还有这里:

序列:SQL Server 社区已请求序列多年来，它包含在此版本中.序列是一个用户生成数字序列的已定义对象.这是一个使用 Sequence 的示例.

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 序列对象生成数字序列就像sql 表中的标识列.但序列的优势numbers 是序列号对象，不受单个 sql 的限制表.

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.

在 msdn 上，您还可以阅读更多关于用法以及我们需要它的原因(这里):

and on msdn you can also read more about usage and why we need it (here):

序列是用户定义的模式绑定对象，它生成一个根据规范的数值序列序列已创建.生成数值序列以定义的间隔按升序或降序排列，并且可以根据要求循环(重复).与标识列不同，序列是与表无关.一个应用程序引用一个序列对象接收下一个值.序列和之间的关系表由应用程序控制.用户应用程序可以引用序列对象并协调值键多行和多表.

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.

使用 CREATE 创建一个独立于表的序列序列语句.选项使您能够控制增量，最大值和最小值、起点、自动重启能力和缓存以提高性能.有关的信息选项，请参阅创建序列.

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.

与标识列值不同，标识列值是在行为插入，应用程序可以获取之前的下一个序列号通过调用 NEXT VALUE FOR 函数插入行.序列调用 NEXT VALUE FOR 时分配号码，即使号码永远不会插入到表中.NEXT VALUE FOR 函数可以是用作表定义中列的默认值.用sp_sequence_get_range 获取多个序列号的范围一次.

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.

序列可以定义为任何整数数据类型.如果数据类型未指定，序列默认为 bigint.

A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint.

这篇关于序列与身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！