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.
  1. Suggest an alternative to using the IDENTITY column property.

  2. Explain how the alternative solution solves the existing problems.
  1. You can address all of the existing problems with the IDENTITY property by using the sequence object instead.

  2. 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.