Table of contents
- Introduction
- The Identity Property
- The Sequence object
- Sequence is an independent object in the database
- Sequence allows you to specify a maximum and minimum value within a specified type
- The sequence object supports Cycling!
- Sequence allows a starting and an increment value
- Caching
- Modification
- How to generate a new Sequence value
- Extra capabilities of the Sequence object
- Conclusion
- References
Introduction
The Identity property and Sequence object are used to generate keys for unique row identification in SQL. This article discusses the Identity property and its shortcomings. Additionally, I also discuss how the Sequence object resolves some of the identity property's limitations.
The Identity Property
SQL Server allows you to define a property called identity for a numeric column with a scale of 0 (no fraction). Upon INSERT, a new value is automatically generated based on a seed (first value) and an increment (step value) that are provided in the column's definition. The identity property is normally used with surrogate keys--keys generated by the DBMS and not derived from application data.
Let's take an example.
IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
keycol INT NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Table1 PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
CONSTRAINT CHK_T1_datacol CHECK(datacol LIKE '[A-Za-z]%') -- start with an alphabet
);
Table1 contains a column called keycol which is defined with an identity property using 1 as the seed and 1 as the increment. It also contains a column called datacol with a check constraint that requires the first character of an inserted string to be an alphabet.
The INSERT statement should completely ignore the identity column because the DBMS will handle that. For example, let's add some data.
INSERT INTO dbo.Table1 (datacol) VALUES ('AAAAA');
INSERT INTO dbo.Table1 (datacol) VALUES ('BBBBB');
INSERT INTO dbo.Table1 (datacol) VALUES ('CCCCC');
The result set below shoes that the DBMS inserted values for keycol automatically.
SELECT * FROM dbo.Table1;
Result set:
keycol datacol
------- ---------
1 AAAAA
2 CCCCC
3 BBBBB
When you query this table, you can refer to the identity column by its name (keycol). SQL Server provides another way to refer to the identity column--by using the more generic form $identity
.
For example,
SELECT $identity FROM dbo.Table1;
Result set:
keycol
-------
1
2
3
How SQL Server generates a new identity value
SQL Server generates a new identity value based on the current identity value in the table and the increment. You may want to obtain a newly generated identity value for some reason--for example, to insert child rows into a referencing table. To do this, you can query two functions namely @@identity
and SCOPE_IDENTITY
.
The @@identity
function
This is an old function that returns the last identity value generated from the current session, regardless of scope(for example, the current procedure and trigger fired by INSERT are different scopes).
The SCOPE_IDENTITY
function
SCOPE_IDENTITY returns the last identity value generated in the current scope (for example, the same procedure) and current session. Except for very special cases when you don't care about scope, you should use the SCOPE_IDENTITY
function.
As an example, the following code inserts a row into dbo.Table1
, obtains the newly generated identity value into a variable by querying the SCOPE_IDENTITY
function, and queries the variable.
DECLARE @new_key AS INT;
INSERT INTO dbo.Table1 (datacol) VALUES ('AAAAA');
SET @new_key = SCOPE_IDENTITY();
SELECT @new_key AS new_key
Result set
new_key
-----------
4
As a reminder, both @@identity
and SCOPE_IDENTITY
return the last identity value in the current session. Neither is affected by inserts issued by other sessions. If you want to get the last identity value generated for a table regardless of session, you should use the IDENT_CURRENT
function and provide the table name as input. Let's see this in action. Start a new session and run the code below.
SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
@@identity AS [@@identity],
IDENT_CURRENT('dbo.Table1') AS [IDENT_CURRENT]
Result set
SCOPE_IDENTITY @@identity IDENT_CURRENT
------------------ ------------ ------------------
NULL NULL 4
SCOPE_IDENTITY
and @@identity
returned NULL marks because an identity value was not generated in the session that created this query. IDENT_CURRENT
returned 4 because that's the current identity value of dbo.Table1
regardless of the session in which it was created.
Gotchas about the @@identity
function
Gotcha 1
The update to the identity value is not undone if the INSERT operation fails or if the transaction in which the statement runs is rolled back. For example, let's insert a row which contradicts the CHECK
constraint in datacol
.
INSERT INTO dbo.Table1(datacol) VALUES ('12345')
The following error is produced.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_T1_datcol". The conflict occurred in database "BikeStores", table "dbo.Table1", column 'datacol'.
Although the INSERT operation failed, the identity column in dbo.Table1
was updated. Run the following code to see the current identity value in dbo.Table1
.
SELECT IDENT_CURRENT('dbo.Table1') AS [CURRENT INDEX]
Result set
CURRENT INDEX
--------------
5
This means that there could be gaps in identity values so you should only implement the identity property in a column if you don't care about the gaps. Let's insert a row into dbo.Table1
to show the presence of a gap.
INSERT INTO dbo.Table1 (datacol) VALUES ('EEEEE');
SELECT * FROM dbo.Table1
Result set
keycol datacol
-------- ----------
1 AAAAA
2 CCCCC
3 BBBBB
4 AAAAA
6 EEEEE
Gotcha 2
You cannot add an identity property to an existing column or remove it from an existing column. You can only define the property along with a column as part of a CREATE TABLE
statement or an ALTER TABLE
statement that adds a new column. SQL Server does allow you to specify custom values for an identity column with a little hack. This hack involves setting a session option called IDENTITY_INSERT
against the table. But there's still no option that will allow you to update an identity column. In the example below, a row with a keycol
set to 5 is added to dbo.Table1
.
SET IDENTITY_INSERT dbo.Table1 ON;
INSERT INTO dbo.Table1(keycol, datacol) VALUES (5, 'FFFFF');
SET IDENTITY_INSERT dbo.Table1 OFF;
Gotcha 3
SQL Server will only change the current identity value in a table if the new identity value is greater than the current identity value. Since the current identity property of dbo.Table1
is 6, and we inserted a new row with a keycol
of 5, the current identity property of dbo.Table1
remains the same (6 in this case).
Gotcha 4
The identity property of a column does not enforce uniqueness in that column. We saw that you can insert your own values by setting the IDENTITY_INSERT
option on the table to ON
. Those values can be ones that already exist in the table. Additionally, you can reseed the current identity value in a table using the DBCC CHECKIDENT
command.
For example, let's delete all the data in the dbo.Table1
table and reseed its identity value. Check out this post to learn more about the DBCC CHECKIDENT
command.
TRUNCATE TABLE dbo.Table1;
GO
DBCC CHECKIDENT ('dbo.Table1', RESEED, 1);
GO
Concretely, the identity property does NOT enforce uniqueness. If you want to guarantee the uniqueness of a column then define a primary key or a unique constraint on that column.
The Sequence object
This feature was added in SQL Server 2012 as an alternative key-generating mechanism for identity. The sequence object is more flexible in many ways, making it the preferred choice in many cases.
Sequence is an independent object in the database
One merit of the sequence object is that unlike the identity property, it is not tied to any column in a table; rather, it's an independent object in the database. You invoke the function against the object whenever you need to generate a new value. This means that you can use one sequence object that will help you maintain keys that would not conflict across multiple tables.
Use the CREATE SEQUENCE
command to create a sequence object. There are a few specifications you can provide but the minimum that is required is the name of the object. It's worth noting that you may not like the defaults that SQL Server provides. If you don't provide the type of the object, SQL Server will use BIGINT
by default. If you want a different type, indicate AS <type>
. For example, if you want the type of the object to be INT
, indicate AS INT
.
Sequence allows you to specify a maximum and minimum value within a specified type
Unlike the identity property, you can specify a minimum value (MINVALUE ) and maximum value (MAXVALUE ) within the type. If the maximum and minimum values are not specified, value of -2,147483,648 and 2,147483,647 is assumed for the maximum and minimum values respectively.
The sequence object supports Cycling!
Again, unlike the identity property, the sequence object supports cycling. It's worth noting that this is not the default behaviour, so if you want your sequence object to cycle, then you'll have using the CYCLE option to enable this behaviour.
Sequence allows a starting and an increment value
Like the identity property, sequence allows you to specify a starting value (START WITH ) and the increment (INCREMENT BY ). If you don't indicate the starting value, the default will be the same as the minimum value (MINVALUE), and if you don't specify an increment, then it'll be 1 by default.
Caching
The sequence object has a caching option (CACHE | NO CACHE) that tells SQL Server the number of values to write to disk. Writing less frequently improves performance when generating a value (on average), but you risk losing more data in the event of an unexpected shutdown of the server.
Modification
You can change the options of a sequence object by using the ALTER SEQUENCE
command. You can specify values like the (MINVAL , MAXVAL , RESTART WITH , INCREMENT BY , CYCLE | NO CYCLE, or CACHE | NO CACHE).
Let's a create a simple sequence object of the type INT
, has a minimum value of 1 and a maximum supported by the type, starts with 1, increments by 1, and allows cycling.
CREATE SEQUENCE dbo.SeqOrderIDs AS INT
MINVALUE 1
CYCLE ;
We can modify dbo.SeqOrderIDs
like so
ALTER SEQUENCE dbo.SeqOrderIDs
NO CYCLE;
How to generate a new Sequence value
You can generate a new sequence value by using NEXT VALUE FOR <sequence name>
. As eerie as it looks, the aforementioned expression is a function.
SELECT NEXT VALUE FOR dbo.SeqOrderIDs;
The beauty about the sequence object is that you don't need to insert a row into a table to generate a new value. You just have to invoke it. In some applications, you may have to generate this new value before using it. Let's take an example.
IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
keycol INT NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
-- Generate a new sequence value and store it in a variable
DECLARE @neworderid AS INT = NEXT VALUE FOR dbo.SeqOrderIDs;
-- Use the variable to insert a row into dbo.Table1
INSERT INTO dbo.Table1 (keycol, datacol) VALUES (@neworderid, 'a');
SELECT * FROM dbo.Table1
Result set
keycol datacol
------ ----------
2 a
You can also specify the NEXT VALUE FOR
function directly inside the INSERT
statement.
INSERT INTO dbo.Table1(keycol, datacol)
VALUES(NEXT VALUE FOR dbo.SeqOrderIDs, 'b');
SELECT * FROM dbo.Table1
Result set
keycol datacol
------ ----------
2 a
3 b
Unlike with identity, you can generate new sequence values in an UPDATE statement like so.
UPDATE dbo.Table1
SET keycol = NEXT VALUE FOR dbo.SeqOrderIDs;
SELECT * FROM dbo.Table1
Extra capabilities of the Sequence object
Controlling order
SQL Server allows you to control the order of assigned sequence values in a multi-row insert by using an OVER
clause similar to what is used in window functions.
INSERT INTO dbo.Table1(keycol, datacol)
SELECT
NEXT VALUE FOR dbo.SeqOrderIDs OVER (ORDER BY orderdate),
LEFT (first_name, 1) + LEFT(last_name, 1)
FROM dbo.Orders AS T1
JOIN sales.staffs AS T2
ON T1.empid = T2.staff_id;
SELECT * FROM dbo.Table1
Result set
keycol datacol
------- --------
2 a
3 GS
4 GS
...
Specifying a default value
ALTER TABLE dbo.Table1
ADD CONSTRAINT DFT_T1_keycol
DEFAULT (NEXT VALUE FOR dbo.SeqOrderIDs)
FOR keycol;
You should now be able to insert rows without specifying a keycol
value.
INSERT INTO dbo.Table1(datacol) VALUES ('RM');
SELECT * FROM dbo.Table1;
This is a great advantage over identity--you can add a default constraint to an existing table and remove it from an existing table as well.
Allocate a range of sequence values
SQL Server allows you to allocate a range of sequence values by using a stored procedure called sp_sequence_get_range
. The idea is that if an application needs to assign a range of sequence values, it is easiest to update the sequence only once, incrementing it by the size of the range. You call the procedure, indicate the size of the range you want, and collect the first value in the range, as well as other information, by using output parameters.
DECLARE @first AS SQL_VARIANT;
EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.SeqOrderIDs',
@range_size = 1000,
@range_first_value = @first OUTPUT ;
SELECT @first
Conclusion
In this article, I extensively discussed the identity property. We learned that you can't update a column with an identity property, you cannot update a column with the identity property and you cannot remove the identity property of a column. We also learned about the use @@identity
and SCOPE_IDENTITY
functions to get the current identity value.
I also discussed the sequence object and the extensions that were added its functionality in SQL Server 2012. I demonstrated how to create a sequence object. Moreover, I demonstrated how to invoke a function against the object using the NEXT VALUE FOR
function. Additionally, I discussed the idiosyncrasies of the object including cycling, MINVALUE, MAXVALUE, TYPE and others. Lastly, I discussed some smart ways of using the sequence object to generate values.
References
Itzik Ben-Gan - Microsoft SQL Server 2012 T-SQL Fundamentals-Microsoft Press (2012)