Providing an Improved Solution for Generating Keys :: SQL Server 2012
You're a member of the database administrator (DBA) group in a company that manufactures
hiking gear. Most tables in the company’s OLTP database currently use an IDENTITY property
but require more flexibility. For example, often the application needs to generate the new
key before using it. Sometimes the application needs to update the key column, overwriting
it with new values. Also, the application needs to produce keys that do not conflict across
multiple tables.
- Suggest an alternative to using the IDENTITY column property.
- Explain how the alternative solution solves the existing problems.
Answers
- You can address all of the existing problems with the IDENTITY property by using the
sequence object instead.
- With the sequence object, you can generate values before using them by invoking
the NEXT VALUE FOR function and storing the result in a variable. Unlike with the
IDENTITY property, you can update a column that normally gets its values from a
sequence object. Also, because a sequence object is not tied to a specific column in
a specific table, but instead is an independent object in the database, you can generate
values from one sequence and use them in different tables.