You have a database named DB1.
You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the
same identifying value for each table, but the value must increase from one invocation of the stored procedure
to the next.
Occasionally, the identifying value must be reset to its initial value.
You need to design a mechanism to hold the identifying values for the stored procedure to use.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A.
Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the
stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed.
B.
Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the
stored procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE
statement. Reset the value as needed by using a different ALTER SEQUENCE statement.
C.
Create a fourth table that holds the next value in the sequence. At the end each transaction, update the
value by using the stored procedure. Reset the value as needed by using an UPDATE statement.
D.
Create an identity column in each of the three tables. Use the same seed and the same increment for each
table. Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT
command to reset the columns as needed.
Explanation:
* an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE
FOR function.
* ALTER SEQUENCE
Includes argument:
RESTART [ WITH <constant> ]
The next value that will be returned by the sequence object. If provided, the RESTART WITH value must be an
integer that is less than or equal to the maximum and greater than or equal to the minimum value of the
sequence object. If the WITH value is omitted, the sequence numbering restarts based on the original CREATE
SEQUENCE options.
* CREATE SEQUENCE
Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that
generates a sequence of numeric values according to the specification with which the sequence was created.
The sequence of numeric values is generated in an ascending or descending order at a defined interval and
can be configured to restart (cycle) when exhausted.
Sequence Numbers