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.