MS SQL 2011 - New Sequence Object
The opportunity, which is not surprising now users of Oracle, DB2, PostgreSQL and many other relational databases, finally appeared in MS SQL Server. In the arena of Sequence!
Sequence - generates a sequence of numbers as well as identity. However, the main advantage of sequence is that the sequence does not depend on any particular table and is an object of the database.
Consider an example script written in SQL Server 2008. Creating a simple table with two columns, one of which will be auto-increment.
')
Similarly, create another table.
As you can see from the examples, we recorded the values in the table, and the value of the incremental field was automatically and independently of us filled. We cannot reuse the value of this field in another table. Let's see how to get out of this situation using Sequence.
The general syntax for a command is:
Create a sequence of numbers:
After the execution of the specified script, in the base object browser, in the Sequences node you can find our object.
After the object is created, you can use it in creating and populating tables as shown below:
If you create a second table in the same way, you can again use the GenerateNumberSequence and get a through numbering of objects.
The sequence (Sequence) that we created can be viewed in the sys system directory . sequences .
This is not all the available information on the sequence, we just need these columns later. To get all the information, replace the column names with an asterisk. Is_Exhausted will be mentioned later.
Sequence can be of the following types:
It is not necessary to start the sequence from one. You can start with any number within the possible values of the declared type. For example, for integer values, this could be from -2147483648 to 2147483647.
Let's test in practice what SQL Server will say when setting the initial number outside the acceptable range. Let's start with the left border.
Analogous value for the given data type.
As expected. Now we break the right border.
The server will report the error to us like this:
The sequence object "GenerateNumberSequence" is greater than the number of available values; the cache has been set automatically.
And if we pay attention to the Is_Exhausted column in the sys.sequences catalog, we will see that the value became equal to 1. That tells us about the impossibility of further use of this sequence.
When you try to create a table using this sequence, the server will generate an error:
The sequence object 'GenerateNumberSequence' has reached the minimum or maximum value. Restart the sequence.
This can be interpreted as a request from the engine to restart the specified sequence. To do this, use the RESTART WITH construct.
The value must be within the valid range of the declared type. Further, the sequence will start from the specified value, not from the following.
Those. if you specify:
And then execute the script:
That result will be:
The sequence began with a given value.
You can get the minimum and maximum values from the sys directory . sequences .
For sequences, you can set the boundaries of valid values. Let's try to execute such a script below.
The minimum value is 10, the maximum is 20, but we are trying to set an initial value equal to one. This is outside the acceptable range and therefore we will be pleased with the message:
The object is generated for the object.
Further, we can imagine that the next value in the sequence violates the boundary. In this case, we get an error:
The sequence object 'GenerateNumberSequence' has reached the minimum or maximum value. Restart the sequence.
There are two ways to solve the problem:
This option loops the sequence and, having reached the maximum value, the sequence continues from the minimum. For example:
After the maximum value has been reached, the results will be as follows:
For the sample the query was used:
If you look closely at the output, you will notice that the records were confused. If we didn't use sequences, the output would be
But due to the fact that the second entry crossed the range of valid values, the number was reset to the minimum value specified for the sequence (10). If you now look at the sys.sequences directory, you will see that the current value is 10.
Next time, filling in the table could be this:
At this point, the Sequence will check the order in which the records will be inserted, and since the “Violet” comes before the “Tape” and the current number is 10, the records will be inserted as:
Next_value = Current_value + Shift ie 10 +1 will be assigned to “Violet”. Now the value of Sequence = 11 and for the second record the value will be 12 following the same formula.
The behavior of this option has already been considered at the very beginning, and is the default when creating a Sequence.
You can use a sequence with an Over expression to generate sequence numbers as shown below:
Result:
You may notice that the records were sorted and the sequence was applied correctly to the stored data. This means that the records are sorted first and only then sequence numbering is applied.
In no case can the Sequence be used in conjunction with:
If we consider all the approaches used above to add rows to tables using NEXT VALUE FOR , it becomes noticeable that this expression is present in every level of VALUES, which looks a bit tedious. Instead, you can use the sp_sequence_get_range function to get the necessary range of values that can be used later. Now I will demonstrate how this can be done.
Here is what will happen as a result of execution
Here you can see that the sequence was increased to 1000 and the missing values were not used anywhere without our knowledge. In this case, we used them to insert values.
You should not put a global equality sign between them due to the following factors:
And a few more words about Sequence.
You can read more about Sequence on MSDN:
Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .
Sequence - generates a sequence of numbers as well as identity. However, the main advantage of sequence is that the sequence does not depend on any particular table and is an object of the database.
Consider an example script written in SQL Server 2008. Creating a simple table with two columns, one of which will be auto-increment.
')
Create Table WithOutSequence1 ( EmpId int identity not null primary key ,EmpName varchar(50) not null ) Insert into WithOutSequence1 Select 'Violet' Union All Select 'Tape' Select * from WithOutSequence1 Similarly, create another table.
Create Table WithOutSequence2 ( EmpId int identity not null primary key ,EmpName varchar(50) not null ) Insert into WithOutSequence2 Select 'Violet' Union All Select 'Tape' Select * from WithOutSequence2 As you can see from the examples, we recorded the values in the table, and the value of the incremental field was automatically and independently of us filled. We cannot reuse the value of this field in another table. Let's see how to get out of this situation using Sequence.
The general syntax for a command is:
CREATE SEQUENCE [schema_name . ] sequence_name [ AS { built_in_integer_type | user-defined_integer_type } ] | START WITH <constant> | INCREMENT BY <constant> | { MINVALUE <constant> | NO MINVALUE } | { MAXVALUE <constant> | NO MAXVALUE } | { CYCLE | NO CYCLE } | { CACHE [<constant> ] | NO CACHE } Create a sequence of numbers:
IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'GenerateNumberSequence' AND TYPE='SO') DROP Sequence GenerateNumberSequence GO SET ANSI_NULLS ON GO CREATE SEQUENCE GenerateNumberSequence START WITH 1 INCREMENT BY 1; GO After the execution of the specified script, in the base object browser, in the Sequences node you can find our object.
After the object is created, you can use it in creating and populating tables as shown below:
Create Table WithSequence1 ( EmpId int not null primary key ,EmpName varchar(50) not null ); Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence1; If you create a second table in the same way, you can again use the GenerateNumberSequence and get a through numbering of objects.
Create Table WithSequence2 ( EmpId int not null primary key ,EmpName varchar(50) not null ); Insert into WithSequence2(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence2; The sequence (Sequence) that we created can be viewed in the sys system directory . sequences .
SELECT Name ,Object_ID ,Type ,Type_Desc ,Start_Value ,Increment ,Minimum_Value ,Maximum_Value ,Current_Value ,Is_Exhausted FROM sys.sequences This is not all the available information on the sequence, we just need these columns later. To get all the information, replace the column names with an asterisk. Is_Exhausted will be mentioned later.
Sequence can be of the following types:
- Int
- Smallint
- Tinyint
- Bigint
- Decimal
- Numeric
It is not necessary to start the sequence from one. You can start with any number within the possible values of the declared type. For example, for integer values, this could be from -2147483648 to 2147483647.
Let's test in practice what SQL Server will say when setting the initial number outside the acceptable range. Let's start with the left border.
CREATE SEQUENCE GenerateNumberSequence START WITH -2147483649 --outside the range of the int datatype boundary INCREMENT BY 1; Analogous value for the given data type.
As expected. Now we break the right border.
CREATE SEQUENCE GenerateNumberSequence START WITH 2147483647 --the max range of the int datatype INCREMENT BY 1; The server will report the error to us like this:
The sequence object "GenerateNumberSequence" is greater than the number of available values; the cache has been set automatically.
And if we pay attention to the Is_Exhausted column in the sys.sequences catalog, we will see that the value became equal to 1. That tells us about the impossibility of further use of this sequence.
When you try to create a table using this sequence, the server will generate an error:
The sequence object 'GenerateNumberSequence' has reached the minimum or maximum value. Restart the sequence.
This can be interpreted as a request from the engine to restart the specified sequence. To do this, use the RESTART WITH construct.
ALTER SEQUENCE dbo.GenerateNumberSequence RESTART WITH 1; The value must be within the valid range of the declared type. Further, the sequence will start from the specified value, not from the following.
Those. if you specify:
ALTER SEQUENCE dbo.GenerateNumberSequence RESTART WITH 10; And then execute the script:
Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'violet'), (NEXT VALUE FOR GenerateNumberSequence, 'tape') SELECT * FROM WithSequence1; That result will be:
EmpId EmpName ----- ------- 10 violet 11 tape The sequence began with a given value.
You can get the minimum and maximum values from the sys directory . sequences .
MIN and MAX values
For sequences, you can set the boundaries of valid values. Let's try to execute such a script below.
CREATE SEQUENCE GenerateNumberSequence START WITH 1 INCREMENT BY 1 MINVALUE 10 MAXVALUE 20 The minimum value is 10, the maximum is 20, but we are trying to set an initial value equal to one. This is outside the acceptable range and therefore we will be pleased with the message:
The object is generated for the object.
Further, we can imagine that the next value in the sequence violates the boundary. In this case, we get an error:
The sequence object 'GenerateNumberSequence' has reached the minimum or maximum value. Restart the sequence.
There are two ways to solve the problem:
- Use the service words Restart or Restart With.
- Use CYCLE option
CYCLE option
This option loops the sequence and, having reached the maximum value, the sequence continues from the minimum. For example:
CREATE SEQUENCE GenerateNumberSequence START WITH 20 INCREMENT BY 1 MINVALUE 10 MAXVALUE 20 CYCLE After the maximum value has been reached, the results will be as follows:
EmpId EmpName ----- ------- 10 Tape 20 Violet For the sample the query was used:
Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence1; If you look closely at the output, you will notice that the records were confused. If we didn't use sequences, the output would be
EmpId EmpName ----- ------- 20 Violet 21 Tape But due to the fact that the second entry crossed the range of valid values, the number was reset to the minimum value specified for the sequence (10). If you now look at the sys.sequences directory, you will see that the current value is 10.
Next time, filling in the table could be this:
EmpId EmpName ---- ------- 11 Violet 12 Tape At this point, the Sequence will check the order in which the records will be inserted, and since the “Violet” comes before the “Tape” and the current number is 10, the records will be inserted as:
Next_value = Current_value + Shift ie 10 +1 will be assigned to “Violet”. Now the value of Sequence = 11 and for the second record the value will be 12 following the same formula.
NO CYCLE option
The behavior of this option has already been considered at the very beginning, and is the default when creating a Sequence.
Sequence in combination with Over ()
You can use a sequence with an Over expression to generate sequence numbers as shown below:
--Declare a table Declare @tblEmp Table ( EmpId int identity ,EmpName varchar(50) not null ) --Populate some records Insert Into @tblEmp Select 'Niladri' Union All Select 'Arina' Union All Select 'Deepak' Union All Select 'Debasis' Union All Select 'Sachin' Union All Select 'Gaurav' Union All Select 'Rahul' Union All Select 'Jacob' Union All Select 'Williams' Union All Select 'Henry' --Fire a query SELECT e.* ,Seq = NEXT VALUE FOR GenerateNumberSequence OVER (ORDER BY EmpName) FROM @tblEmp e Result:
You may notice that the records were sorted and the sequence was applied correctly to the stored data. This means that the records are sorted first and only then sequence numbering is applied.
Restrictions on the use of Next Value for functions.
In no case can the Sequence be used in conjunction with:
- Constraints check (constraints)
- Defaults
- Calculated columns
- Views
- Custom Functions
- Custom aggregation functions
- Subqueries
- CTE (Common Table Expression)
- Subtables
- TOP expression
- Expression over
- Output expression
- Expression on
- Where expression
- Group By expression
- Having an expression
- Order By
- Compute expression
- Compute By
Sp_sequence_get_range function
If we consider all the approaches used above to add rows to tables using NEXT VALUE FOR , it becomes noticeable that this expression is present in every level of VALUES, which looks a bit tedious. Instead, you can use the sp_sequence_get_range function to get the necessary range of values that can be used later. Now I will demonstrate how this can be done.
-- , IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'GenerateRangeNumberSequence' AND TYPE='SO') DROP Sequence GenerateRangeNumberSequence GO -- , IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_RangeSequence' AND type = 'U') DROP TABLE tbl_RangeSequence GO SET ANSI_NULLS ON GO -- CREATE SEQUENCE GenerateRangeNumberSequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2000 CYCLE GO -- CREATE TABLE [dbo].[tbl_RangeSequence]( [EmpId] [int] NOT NULL, [EmpName] [varchar](50) NOT NULL, PRIMARY KEY CLUSTERED ( [EmpId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO -- sp_sequence_get_range DECLARE @sequence_name nvarchar(100) = N'GenerateRangeNumberSequence', @range_size int = 1000, @range_first_value sql_variant, @range_last_value sql_variant, @sequence_increment sql_variant, @sequence_min_value sql_variant, @sequence_max_value sql_variant; -- sp_sequence_get_range EXEC sp_sequence_get_range @sequence_name = @sequence_name, @range_size = @range_size, @range_first_value = @range_first_value OUTPUT, @range_last_value = @range_last_value OUTPUT, @sequence_increment = @sequence_increment OUTPUT, @sequence_min_value = @sequence_min_value OUTPUT, @sequence_max_value = @sequence_max_value OUTPUT; -- SELECT @range_size AS [Range Size], @range_first_value AS [Start Value], @range_last_value AS [End Value], @sequence_increment AS [Increment], @sequence_min_value AS [Minimum Value], @sequence_max_value AS [Maximum Value]; -- ;With Cte As ( Select Rn = 1, SeqValue = Cast(@range_first_value as int) Union All Select Rn+1, Cast(SeqValue as int) + Cast( @sequence_increment as int) From Cte Where Rn<@range_last_value ) -- 100 Insert into tbl_RangeSequence(EmpId, EmpName) Select SeqValue,'Name' + Cast(SeqValue as varchar(3)) From Cte Where SeqValue<=100 Option (MaxRecursion 0) -- SELECT * FROM tbl_RangeSequence Here is what will happen as a result of execution
Here you can see that the sequence was increased to 1000 and the missing values were not used anywhere without our knowledge. In this case, we used them to insert values.
Comparison between Sequence and Identity
You should not put a global equality sign between them due to the following factors:
- Identity refers to the table and is part of its inseparable, Sequence - an independent database object.
- You can get a sequence set using sp_sequence_get_range, which is basically impossible with Identity.
- For Sequence, you can define boundaries of values, which is also impossible for Identity.
- The cyclic values can be set the same way only for Sequence.
And a few more words about Sequence.
- Sequence gives a greater performance boost compared to Identity. Comparison and results in the article by Aaron Bertrand
- You can set permissions to the Sequence, as well as to other database objects.
You can read more about Sequence on MSDN:
Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .
Source: https://habr.com/ru/post/123446/
All Articles