What is 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.SQLCopySELECT 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.