What is Generate automatic values

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:

SQLCopy

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
);