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 sam
e 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 a
s 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
The next value tha
t 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 nu
mbering 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 specific
ation 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.