The Identity Property and the Sequence Object

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)