Hello everybody!
Good Morning!
Today I came across a situation where the use of the sequences, old known to me from Oracle and that from SQL Server 2012 were introduced in Microsoft DBMS, saw a perfect solution to the problem I was facing.
Introduction - Detailing the Problem
This problem consisted of a table with tens of millions of records that has a sequential number for control and uniqueness of the record and now a need has arisen to create another sequential number in that same independent table where existing records should be NULL and that would be started in 1, to control another type of information and only to be populated when a given event occurs (unlike the existing sequence that is generated with each insertion in the table)
Another situation that should be avoided at all costs is that never two sessions can take the same sequence and write to the table and this possibility is quite possible, since this table has multiple inserts in parallel sessions per second.
In this case, using IDENTITY would not be possible since IDENTITY is applied to all records in the table, which would not be the case. Using ranking structures like ROW_NUMBER could even be feasible if it were not so heavy to calculate this ranking with each new insertion (besides the possibility of two sessions taking the same sequential).
Differences Between Sequence and IDENTITY
| SEQUENCE | IDENTITY |
|---|---|
| It is an independent object, which can be used to populate any column (including more than one in the same table) of integer type (int, bigit, smallint, tinyint, 0-scale decimal or 0-scale numeric), of one or more tables | Is associated with a column of a table. |
| It is populated when called. That is, it can be populated with each insertion or only when some condition is met | Populates on each insert |
| When started on an already populated table, previous records will not change | When started in the table, the entire column is populated with the sequential |
| Must be called manually to generate sequential | The sequential is automatically generated |
| Has separate permissions | No additional permissions beyond table |
| Minimum and maximum value can be set (Ex: From 1 to 100) | The maximum value is the limit of the column data type. |
| The sequence can be restarted automatically upon reaching the maximum value (CYCLE parameter) | When maximum value is reached, no more records can be entered |
| A new sequence can be generated in UPDATE commands if required. | Sequential is generated only on INSERT of data |
| Available from SQL Server 2012 | Available from SQL Server 6.0 (SQL 95) |
| The current value of the sequence can be The current value of the sequence can be queried through the sys.sequences view. | querying through the sys.identity_columns view |
| String value can be reset | String value cannot be reset |
How to create a sequence
To solve the problem described above, I had the idea of creating a sequence, which is a database object specially created for this kind of need. Unlike IDENTITY, you can use more than one sequence in the table and previous records are not changed. These were the reasons that led me to use this feature of SQL Server to solve this situation.
Let's see now how to create a sequence:
|
1 2 3 4 5 6 7 8 9 |
CREATE SEQUENCE dbo.[seq_Teste] AS [INT] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 999999999 CYCLE CACHE GO |
The parameters explained:
- START WITH: Defines the starting sequence number.
- MINVALUE and MAXVALUE: Delimits the SEQUENCE limit to its respective maximum and minimum value. If the value is not defined, the maximum and minimum value of the chosen data type will be assigned.
- INCREMENT BY: Defines the amount that will be incremented in sequence. In the example above, 1 will be incremented by 1.
- CYCLE: The CYCLE property allows you to start a cycle again once the MINVALUE and MAXVALUE property is reached. That is, when reaching the value set in MAXVALUE, the sequence will start again at the value of the parameter MINVALUE (when this occurs, duplicate values will be generated in the sequence, because the whole range has already been traversed)
- CACHE: When using this parameter, SQL Server pre-allocates the sequence numbers by the CACHE property, the default value for which is 15, meaning that the values of the next 15 available values will be allocated in memory until they are used and the sequence already treats these numbers as used. When all CACHE numbers are used, 15 new values are allocated to memory again and the cycle follows. It is worth remembering that if the instance is restarted, the numbers that are in the cache are lost and this “hole” is left in the sequence.
How to return the next number in a sequence
Returning the next number in a sequence is a very simple task:
|
1 |
SELECT NEXT VALUE FOR seq_Teste |
However, you should keep in mind that unlike IDENTITY, each insert you must call sequence to return the sequential and insert into the table or create a default constraint to automate this task.
How to return the next number of a sequence automatically
Although most people (including me) use the sequence manually with each insert to return the next sequence, this can be automated using a DEFAULT CONSTRAINT in the table:
|
1 2 3 4 |
CREATE TABLE dbo.Teste_Sequence ( Id INT DEFAULT NEXT VALUE FOR dbo.seq_Teste, Nome VARCHAR(100) ) |
Example:
How to reset the value of sequence
At times, the sequence counter must be reset or changed to a specific value. For this we can use ALTER SEQUENCE for this task:
|
1 |
ALTER SEQUENCE seq_Teste RESTART WITH 1 |
In the example above, we are resetting the string value to 1.
Example:
Retrieving current value of sequence without increasing sequence
To retrieve the current value of sequence simply perform a query of the sys.sequences view:
|
1 2 3 |
SELECT current_value FROM sys.sequences WHERE name = 'seq_Teste' |
How to change a sequence
Changing a sequence follows the same parameters as the creation, and can be changed at any time.
Examples:
|
1 2 |
ALTER SEQUENCE seq_Teste MAXVALUE 99999 ALTER SEQUENCE seq_Teste CACHE |
How to delete a sequence
Removing a sequence from the SQL Server database is as simple as any other database object and can be done using the DROP statement:
|
1 2 |
DROP SEQUENCE dbo.Sua_Sequence GO |
Sequence permissions
As already commented, sequence are independent objects in the database and therefore have independent permissions as well:
CREATE SEQUENCE: To create a sequence, you must have CREATE SEQUENCE, ALTER, or CONTROL permission on the schema. Role users db_owner and db_ddladmin can create, change, and drop sequences, and role users db_owner and db_datawriter can use sequence to return the next sequence number.
Grant Example:
|
1 |
GRANT CREATE SEQUENCE ON SCHEMA::dbo TO [DOMINIO\usuario] |
ALTER SEQUENCE: To change a sequence, you must have ALTER permission on the schema.
Grant Example:
|
1 |
GRANT ALTER ON OBJECT::dbo.Sua_Sequence TO [DOMINIO\usuario] |
DROP SEQUENCE: To delete a sequence, you must have ALTER or CONTROL permission on the schema.
Demonstration of a single sequence for more than one table
Test Source Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
CREATE SEQUENCE dbo.[seq_Pessoa] AS [INT] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 999999999 CYCLE CACHE GO CREATE TABLE dbo.Pessoa_Fisica ( Id INT DEFAULT NEXT VALUE FOR dbo.seq_Pessoa, Nome VARCHAR(100), CPF VARCHAR(11) ) CREATE TABLE dbo.Pessoa_Juridica ( Id INT DEFAULT NEXT VALUE FOR dbo.seq_Pessoa, Nome VARCHAR(100), CNPJ VARCHAR(14) ) INSERT INTO dbo.Pessoa_Fisica (Nome, CPF) VALUES('Dirceu Resende', '11111111111') INSERT INTO dbo.Pessoa_Juridica (Nome, CNPJ) VALUES('Dirceu Resende Ltda', '22222222222222') INSERT INTO dbo.Pessoa_Fisica (Nome, CPF) VALUES('Dirceu Resende 2', '33333333333') INSERT INTO dbo.Pessoa_Juridica (Nome, CNPJ) VALUES('Dirceu Resende ME', '44444444444444') SELECT * FROM dbo.Pessoa_Fisica SELECT * FROM dbo.Pessoa_Juridica |
Result
That's it folks!
Hope you enjoyed and see you in the next post.
Note: Need to use a sequence in a user defined function, either scalar, aggregate or table-valued and is not able? See the solution in the post Using sequences in user defined functions in SQL Server : )
sql server sequence how to use work restart reset create delete change return get current value next value
sql server sequence how to use work restart reset create delete change return get current value next value
Very good !!
Dirceu is it possible to do a sequence with Start on 1 and INCREMENT 1, and when the year changes, restart the sequence?
Ex .: 01/2019…. 1000/2019
01/2020… .1000 / 2020
You can't concatenate the year in the sequence value, but you can create 1 job that whenever you change the year, reset the sequence value to 1 .. With that, just add the current year to your table and can concatenate the value of this column with the value of sequence
This article was very interesting for my research. Thank you for your help!
Great tip, Vithor! I will add in the post!
Thanks for the visit
Dirceu, very good!
I think you can complement by saying that it is possible to create a CREATE TABLE where a given field uses a sequence like NEXT VALUE, this is amazing!
Eliminates the need for INSERT to query the next value.
Example:
ALTER TABLE Test.MyTable
ADD
DEFAULT N'AdvWorks_ '+
CAST (NEXT VALUE FOR Test.CounterSeq AS NVARCHAR (20))
FOR IDColumn;
GO
INSERT Test.MyTable (name)
VALUES ('Larry');
GO
https://msdn.microsoft.com/en-us/library/ff878370.aspx