Generate automatic values

  • 3 minutes

You may need to automatically generate sequential values for one column in a specific table. Transact-SQL provides two ways to do this: use the IDENTITY property with a specific column in a table, or define a SEQUENCE object and use values generated by that object.

The IDENTITY property

To use the IDENTITY property, define a column using a numeric data type with a scale of 0 (meaning whole numbers only) and include the IDENTITY keyword. The allowable types include all integer types and decimal types where you explicitly give a scale of 0.

An optional seed (starting value), and an increment (step value) can also be specified. Leaving out the seed and increment will set them both to 1.

Note

The IDENTITY property is specified in place of specifying NULL or NOT NULL in the column definition. Any column with the IDENTITY property is automatically not nullable. You can specify NOT NULL just for self-documentation, but if you specify the column as NULL (meaning nullable), the table creation statement will generate an error.

Only one column in a table may have the IDENTITY property set; it's frequently used as either the PRIMARY KEY or an alternate key.

The following code shows the creation of the Sales.Promotion table used in the previous section examples, but this time with an identity column named PromotionID as the primary key:

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Note

The full details of the CREATE TABLE statement are beyond the scope of this module.

Inserting data into an identity column

When the IDENTITY property is defined for a column, INSERT statements into the table generally don't specify a value for the IDENTITY column. The database engine generates a value using the next available value for the column.

For example, you could insert a row into the Sales.Promotion table without specifying a value for the PromotionID column:

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Notice that even though the VALUES clause doesn't include a value for the PromotionID column, you don't need to specify a column list in the INSERT clause - Identity columns are exempt from this requirement.

If this row is the first one inserted into the table, the result is a new row like this:

PromotionID

PromotionName

StartDate

ProductModelID

Discount

Notes

1

Clearance Sale

2021-01-01T00:00:00

23

0.1

10% discount

When the table was created, no seed or increment values were set for the IDENTITY column, so the first row is inserted with a value of 1. The next row to be inserted will be assigned a PromotionID value of 2, and so on.

Retrieving an identity value

To return the most recently assigned IDENTITY value within the same session and scope, use the SCOPE_IDENTITY function; like this:

SELECT SCOPE_IDENTITY();

The SCOPE_IDENTITY function returns the most recent identity value generated in the current scope for any table. If you need the latest identity value in a specific table, you can use the IDENT_CURRENT function, like this:

SELECT IDENT_CURRENT('Sales.Promotion');

Overriding identity values

If you want to override the automatically generated value and assign a specific value to the IDENTITY column, you first need to enable identity inserts by using the SET IDENTITY INSERT table_name ON statement. With this option enabled, you can insert an explicit value for the identity column, just like any other column. When you're finished, you can use the SET IDENTITY INSERT table_name OFF statement to resume using automatic identity values, using the last value you explicitly entered as a seed.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

As you've learned, the IDENTITY property is used to generate a sequence of values for a column within a table. However, the IDENTITY property isn't suitable for coordinating values across multiple tables within a database. For example, suppose your organization differentiates between direct sales and sales to resellers, and wants to store data for these sales in separate tables. Both kinds of sale may need a unique invoice number, and you may want to avoid duplicating the same value for two different kinds of sale. One solution for this requirement is to maintain a pool of unique sequential values across both tables.

Reseeding an identity column

Occasionally, you'll need to reset or skip identity values for the column. To do this, you'll be "reseeding" the column using the DBCC CHECKIDENT function. You can use this to skip many values, or to reset the next identity value to 1 after you've deleted all of the rows in the table. For full details using DBCC CHECKIDENT, see the Transact-SQL reference documentation.

SEQUENCE

In Transact-SQL, you can use a sequence object to define new sequential values independently of a specific table. A sequence object is created using the CREATE SEQUENCE statement, optionally supplying the data type (must be an integer type or decimal or numeric with a scale of 0), the starting value, an increment value, a maximum value, and other options related to performance.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

To retrieve the next available value from a sequence, use the NEXT VALUE FOR construct, like this:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY or SEQUENCE

When deciding whether to use IDENTITY columns or a SEQUENCE object for auto-populating values, keep the following points in mind:

  • Use SEQUENCE if your application requires sharing a single series of numbers between multiple tables or multiple columns within a table.

  • SEQUENCE allows you to sort the values by another column. The NEXT VALUE FOR construct can use the OVER clause to specify the sort column. The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause. This functionality also allows you to generate row numbers for rows as they’re being returned in a SELECT. In the following example, the Production.Product table is sorted by the Name column, and the first returned column is a sequential number.

    SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;

    Even though the previous statement was just selecting SEQUENCE values to display, the values are still being 'used up' and the displayed SEQUENCE values will no longer be available. If you run the above SELECT multiple times, you'll get different SEQUENCE values each time.

  • Use SEQUENCE if your application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. You can use the sp_sequence_get_range system procedure to retrieve several numbers in the sequence at once.

  • SEQUENCE allows you to change the specification of the sequence, such as the increment value.

  • IDENTITY values are protected from updates. If you try to update a column with the IDENTITY property, you'll get an error.