SQL SERVER SEQUENCE

I. INTRODUCTION of sequence

The sequence object is the most familiar database object for Oracle users, and this object is finally visible to SQL SERVER2012. Sequence is a new feature introduced by SQL Server2012. This feature allows database-level serial numbers to be shared between multiple tables or columns.

Ii. Basic concepts of sequence

Oracle has sequence functionality, and SQL Server-like functionality is implemented using the identity column, but the identity column has a lot of limitations. Microsoft finally added the sequence object to the 2012. Unlike the previous identity column, sequence is a database-level object that is bound to the schema, not the specific columns of the specific table. This means that sequence brings the convenience of sharing serial numbers between multiple tables, with the following adverse effects:

1. Unlike the identity column, the serial number in the sequence Insert table can be update unless it is protected by a trigger

2, unlike the identity column, sequence can insert duplicate values (sequence can set the loop, for the loop sequence will have a duplicate value)

3, sequence is only responsible for generating serial number, is not responsible for controlling how to use the serial number, so when the generation of a serial number is rollback, sequence will continue to generate the next serial number, resulting in a gap between the serial number

Third, the use of sequence

The syntax for creating sequence on MSDN is as follows:

CREATE SEQUENCE [schema_name.] Sequence_name      | user-Defined_integer_type] [      <constant> ]      <constant> ]      < Constant>]} | {NO MINVALUE}]       <constant>]} | {NO MAXVALUE}]      | {NO CYCLE}]       <constant>]} | {NO CACHE}]      [ ; ]

Parameters:

Sequence_name
specifies that the database is known sequence 's unique name. the type is sysname.

[Built_in_integer_type | user-defined_integer_type
A sequence can be defined as any integer type. The following types are allowed.

• Tinyint-range 0 to 255

• smallint-range-32,768 to 32,767

• INT-range-2,147,483,648 to 2,147,483,647

• bigint-range-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

• The size of decimal and number 0.

• Any user-defined data type (alias type) based on one of the allowed types.

  If no data type is provided, the bigint data type is used as the default value.

  START with <constant>
  The first value returned by a Sequence object. The start value must be less than one value or equal to the maximum and greater than or equal to the minimum value of the Sequence object. The default starting value for the new sequence object?? is the maximum value of the ascending sequence object's minimum and descending sequence objects.

  INCREMENT by <constant>
  The value that is used to increment (or decrease to negative) the value of the sequence object for each call to the next value for function. If the increment is negative, the sequence object is decremented; Otherwise, it is ascending. The increment cannot be 0. The default increment for the new sequence object is 1.

  [MINVALUE <constant> | NO MINVALUE]
  Specifies the bounds of the sequence object. The default minimum value for a new sequence object is the minimum value of the data type of the Sequence object. For the tinyint data type is zero, all other data types are negative.

  [MAXVALUE <constant> | NO MAXVALUE
  Specifies the bounds of the sequence object. The default maximum value for a new sequence object is the maximum value of the data type of the Sequence object.

  [CYCLE | NO CYCLE]
  Specifies whether the sequence object should restart from the minimum (or the maximum value of the descending sequence object), or throw an exception if the minimum or maximum value is exceeded. The default looping option for new sequence objects is no cycle.

  Note that the loop restarts from the minimum or maximum value instead of starting at the starting value.

  [CACHE [<constant>] | NO CACHE]
  Improve the performance of applications that use sequence objects by minimizing the number of disk IO required to generate serial numbers. The default is cache.

  For example, if you choose a cache size of 50, SQL Server does not retain 50 separate values for caching. It caches only the current value and the number of values remaining in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the Sequence object.

To create a simple sequence

1 CREATE sequence Seq_test-- The name of the sequence: Seq_test2 as bigint-- type 3 100000001 -- start value 41 -- step 51 -- minimum value 6 999999999 -- Max 7 No cycle-- no cycle 8 Cache--Set buffer

2. Query sequence

After you have created the sequence, you can view the sequence that you just created by using the new view sys.sequences in SQL Server 2012, as shown in:

1 -- view sequence 2 SELECT * from sys.sequences WHERE name='seq_test'

3. Use sequence

Inserting a sequence in a single table

Share serial numbers between multiple tables

1--Create test Table 1 and test table 22 CREATE Table #test13  (4 ID bigint5  )6 CREATE Table #test27  (8 ID bigint9  )Ten--inserting test Data One DECLARE @index bigint ASET @index =100000001 -while (@index <100000005) - begin the INSERT into #test1 (ID) VALUES (NEXT value for seq_test) - INSERT into #test2 (ID) VALUES (NEXT value for seq_test) -SET @[email protected]+1 - End +--Show test Data -SELECT *From #test1 +SELECT * from #test2

The results are as follows:

As you can see, if we do not specify the upper and lower bounds of the sequence, the maximum and minimum values for the specified data type are used by default as upper and lower bounds (the upper and lower bounds of the int type). When you reach the line, you can specify a loop to start the loop again from the specified start value after the sequence reaches the upper limit.

1--Create a sequence2CREATE Sequence Seq_test1--name of the sequence: Seq_test3  as int--type4Start with1--Start Value5Increment by1--Step Size6MinValue1--Minimum Value7MaxValue5--Maximum Value8Cycle--Loops9--Create a test tableTen CREATE table Test1 One ( AIdint - ) -DECLARE @indexint theSET @index =0 -while (@index <Ten) - begin - INSERT into test1 (ID) VALUES (NEXT value for Seq_test1) +SET @[email protected]+1 - End +--View Results ASELECT * from Test1

The query results are as follows:

You can specify its initial value as a specific value by modifying sequence

1 -- Modify the value of the sequence 2ALTER sequence seq_test1334 -- Query the current value  5 SELECT next value for Seq_test1

The query results are as follows:

Sequence one thing to note is that sequence is only responsible for generating serial numbers, regardless of how the serial number is used, and if the transaction is unsuccessful or rolled back, SequenceNumber continues to generate the serial number backwards

We can also specify the cache option for sequence, which reduces IO, for example, we specify a cache option of 3, then the current sequence is increased by 1 by 3, and SQL Server allocates 3 spaces from 4 to 6, when assigned to 7 o'clock, SQL The server continues this in a loop, and if you do not specify a cache value, the value is assigned by SQL Server. A simple example.

SQL SERVER SEQUENCE