sql server序列_SQL Server中的序列对象

sql server序列

Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012.

序列对象用于顺序生成数值。 它们是在SQL Server 2012中引入的。

Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE.

序列对象类似于任何SQL表中的IDENTITY列。 但是，与IDENTITY列不同，它们是独立的，并且不附加到任何表。 序列对象既可以独立使用，也可以在DML语句（即INSERT，UPDATE和DELETE）中使用。

This article will take a detailed look at sequence objects.

本文将详细介绍序列对象。

句法 (Syntax)

The syntax for a Sequence object is as follows:

Sequence对象的语法如下：

CREATE SEQUENCE [schema].[Name_of_Sequence]
  [ AS <data type> ]
  [ START WITH <value> ]
  [ INCREMENT BY <value> ]
  [ MINVALUE <value > | NO MINVALUE ]
  [ MAXVALUE <value> | NO MAXVALUE ]
  [ CYCLE | NO CYCLE ]
  [ CACHE value | NO CACHE ];

The syntax works as follows:

语法如下：

Parameter	Description
CREATE SEQUENCE	Used to create a sequence followed by a database schema and the name of the sequence
AS	Specifies the data type of the sequence. Data types can be Decimal, Int, SmallInt, TinyInt, and BigInt. The default value for the data type is BigInt
START WITH	Sets the starting value for the sequence object
INCREMENT BY	Sets the amount that you want your sequence object to increment by
MIN VALUE	This is an optional parameter that specifies the minimum value for the sequence object
MAX VALUE	This is an optional parameter that sets the maximum value for the sequence object
CYCLE	This specifies if the sequence object should be restarted once it has reached its maximum or minimum value. It is an optional parameter for which the default value is NO CYCLE
CACHE	This is used to cache sequence object values. It is also optional parameter with the default value of NO CACHE

参数	描述
创建序列	用于创建后跟数据库模式和序列名称的序列
如	指定序列的数据类型。 数据类型可以是Decimal，Int，SmallInt，TinyInt和BigInt。 数据类型的默认值为BigInt
从...开始	设置序列对象的起始值
增加	设置您希望序列对象增加的数量
最低价值	这是一个可选参数，用于指定序列对象的最小值
最大价值	这是一个可选参数，用于设置序列对象的最大值
周期	此选项指定序列对象达到最大值或最小值后是否应重新启动。 它是一个可选参数，其默认值为NO CYCLE
缓存	这用于缓存序列对象值。 它也是可选参数，默认值为NO CACHE

一个简单的例子 (A Simple Example)

Let’s take a look at a simple example of a sequence object. Execute the following script:

让我们看一下序列对象的简单示例。 执行以下脚本：

CREATE SEQUENCE [dbo].[NewCounter]
AS INT
START WITH 5
INCREMENT BY 5

In the script, we create a sequence object named NewCounter. The type of this sequence object is integer; it starts from 5 and increments by 5.

在脚本中，我们创建一个名为NewCounter的序列对象。 该序列对象的类型为整数； 从5开始，以5为增量。

To see what our NewCounter sequence object contains, execute the following script:

要查看NewCounter序列对象包含的内容，请执行以下脚本：

SELECT NEXT VALUE FOR [dbo].[NewCounter]

When you execute the above script for the first time, you will see ‘5’ in the output. As shown below. This is the start value for the counter.

首次执行上述脚本时，输出中将显示“ 5”。 如下所示。 这是计数器的起始值。

The “NEXT VALUE FOR” statement basically increments the counter.

“ NEXT VALUE FOR”语句基本上使计数器递增。

Execute the following script again:

再次执行以下脚本：

SELECT NEXT VALUE FOR [dbo].[NewCounter]

This time you will see ‘10’ in the output. Each time you execute the above script, the value for the NewCounter sequence will be incremented by 5.

这次您将在输出中看到“ 10”。 每次执行上述脚本时，NewCounter序列的值将增加5。

检索序列对象详细信息 (Retrieving Sequence Object Details)

To retrieve the details of our newly created sequence, execute the following script:

要检索我们新创建的序列的详细信息，请执行以下脚本：

SELECT NEXT VALUE FOR [dbo].[NewCounter]

The script above retrieves all the details of the sequence object such as name, minimum value, maximum value, cycled or not, cached or not, current value, date creation etc. The output of the script above looks like this:

上面的脚本检索序列对象的所有详细信息，例如名称，最小值，最大值，是否循环，是否缓存，当前值，创建日期等。上面脚本的输出如下所示：

改变顺序 (Altering a Sequence)

To modify an existing sequence, the ALTER SEQUENCE statement is used. Have a look at the script below:

要修改现有序列，请使用ALTER SEQUENCE语句。 看下面的脚本：

ALTER SEQUENCE [NewCounter]
RESTART WITH 7

The above script will modify the existing sequence object ‘NewCounter’ by updating its starting value to 7.

上面的脚本将通过将其起始值更新为7来修改现有序列对象“ NewCounter”。

Now if you execute the following statement:

现在，如果执行以下语句：

SELECT NEXT VALUE FOR [dbo].[NewCounter]

You will see ‘7’ in the output.

您将在输出中看到“ 7”。

Executing the above statement again will return 12 (7+5). This is because we only updated the starting value, the value for INCREMENT BY remains same, therefore 7 plus the increment value 5 will be equal to 12.

再次执行上面的语句将返回12（7 + 5）。 这是因为我们只更新了起始值，INCREMENT BY的值保持不变，因此7加增量值5将等于12。

将序列对象与INSERT一起使用 (Using Sequence Object with INSERT)

Sequence objects can be used in combination with INSERT statements to insert values in a sequential manner. For instance, sequence object can be used to insert values for the primary key column.

序列对象可以与INSERT语句结合使用，以顺序方式插入值。 例如，序列对象可用于为主键列插入值。

Let’s create a simple table Students table with three columns Id, StudentName and StudentAge. We will use sequence object to insert a value in the Id column whenever a new record is inserted into the table.

让我们创建一个具有三列Id，StudentName和StudentAge的简单学生表。 每当将新记录插入表中时，我们将使用序列对象在Id列中插入一个值。

Let’s create a table:

让我们创建一个表：

CREATE DATABASE School
GO
 
USE School
GO
 
CREATE TABLE Students
(
	Id INT PRIMARY KEY,
	StudentName VARCHAR (50),
	StudentAge INT
)

Next, we will create a sequence object with an initial value of 1. We will increment this counter by 1. Execute the following script to create such a sequence object, which we’ve called IdCounter.

接下来，我们将创建一个初始值为1的序列对象。我们将使该计数器增加1。执行以下脚本来创建这样的序列对象，我们将其称为IdCounter。

CREATE SEQUENCE [dbo].[IdCounter]
AS INT
START WITH 1
INCREMENT BY 1	

Now let’s insert some records in the Students table that we just created. For the Id column of the table we will use the following statement:

现在，让我们在刚创建的Student表中插入一些记录。 对于表的ID列，我们将使用以下语句：

NEXT VALUE FOR [dbo].[IdCounter]

The above statement will fetch the next value for the IdCounter sequence object and will insert it into the Students table. The script for inserting records into Students table is as follows:

上面的语句将获取IdCounter序列对象的下一个值，并将其插入Students表中。 将记录插入“学生”表的脚本如下：

USE School
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter], 'Sally', 20 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Edward', 36 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Jon', 35)
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Scot', 41 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Ben', 35 )

Select all the records from the Students table, execute the following script:

从“学生”表中选择所有记录，执行以下脚本：

SELECT * FROM Students

The output looks like this:

输出看起来像这样：

You can see from the output that the Id column contains values from 1 to 5 as provided by the IdCounter sequence object.

从输出中可以看到，Id列包含IdCounter序列对象提供的1到5的值。

减少序列对象 (Decrementing a Sequence Object)

To decrement sequence, set the value for INCREMENT BY to a negative number. Setting INCREMENT BY to -1 decrements the sequence object by 1.

要减少顺序，请将INCREMENT BY的值设置为负数。 将INCREMENT BY设置为-1会将序列对象减1。

CREATE SEQUENCE [dbo].[NewCounter2]
AS INT
START WITH 10
INCREMENT BY -1

Now execute the following script twice:

现在执行以下脚本两次：

SELECT NEXT VALUE FOR [dbo].[NewCounter2]

You will see 9 in the output. This is because the first time you execute the command above, the value of NewCounter2 sequence object is initialized to 10. Executing the script again decrements it by 1 to 9.

您将在输出中看到9。 这是因为第一次执行上述命令时，NewCounter2序列对象的值被初始化为10。再次执行该脚本会将其递减1到9。

设置序列对象的最小值和最大值 (Setting the Min and Max Value for Sequence Objects)

You can set the minimum and maximum value that your sequence object can reach. If you try to increment or decrement a sequence object beyond the maximum or minimum values, an exception is thrown.

您可以设置序列对象可以达到的最小值和最大值。 如果您尝试将序列对象增加或减少到最大值或最小值之外，则会引发异常。

CREATE SEQUENCE [dbo].[NewCounter4]
AS INT
START WITH 10
INCREMENT BY 10
MINVALUE 10
MAXVALUE 50

In the script above, we create a sequence object named ‘NewCounter4’. It has a starting value as well as increment of 10. The minimum value for this sequence object is 10 while the maximum value is 50.

在上面的脚本中，我们创建一个名为“ NewCounter4”的序列对象。 它具有一个起始值以及一个增量10。此序列对象的最小值为10，最大值为50。

Now if you increment the value of this sequence object beyond 50, an error will be thrown. For instance, executing the script 6 times attempts to set the value of the sequence object NewCounter4 to 60. The maximum value for the sequence object is set at 50, and so an error will be thrown.

现在，如果将此序列对象的值增加到50以上，将引发错误。 例如，执行脚本6次尝试将序列对象NewCounter4的值设置为60。序列对象的最大值设置为50，因此将引发错误。

SELECT NEXT VALUE FOR [dbo].[NewCounter4]

The screenshot for the error is as follows:

该错误的屏幕截图如下：

循环中递增/递减序列对象 (Incrementing/Decrementing Sequence Objects in Cycle)

We saw that when we tried to increment a sequence object beyond its maximum value, an error was thrown. You can use a CYCLE flag to avoid this error. If a CYCLE flag for a sequence is set to true, the value for the sequence object is again set to its starting value , whenever you try to increment or decrement its value beyond the maximum or minimum value.

我们看到，当我们尝试将序列对象增加到其最大值以外时，会引发错误。 您可以使用CYCLE标志来避免此错误。 如果序列的CYCLE标志设置为true，则每当您尝试将其值递增或递减超过最大值或最小值时，都会将该序列对象的值再次设置为其初始值。

This is best explained with the help of an example.

最好借助示例进行解释。

CREATE SEQUENCE [dbo].[NewCounter7]
AS INT
START WITH 10
INCREMENT BY 10
MINVALUE 10
MAXVALUE 50
CYCLE

Now, if you try to increment the value of the sequence object NewCounter7 6 times, the value of the sequence object will be incremented to 60. This is greater than the maximum value 50. In this case the value of the NewCounter7 will be again set to starting value i.e. 10.

现在，如果您尝试将序列对象NewCounter7的值递增6次，则序列对象的值将递增为60。这大于最大值50。在这种情况下，将再次设置NewCounter7的值至起始值，即10。

本的其他精彩文章 (Other great articles from Ben)

Understanding the GUID data type in SQL Server

Sequence Objects in SQL Server

了解SQL Server中的GUID数据类型

SQL Server中的序列对象

翻译自: https://www.sqlshack.com/sequence-objects-in-sql-server/

sql server序列