What should you do?

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.

Answer: A

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.

Latest 70-764 Dumps Valid Version with 451 Q&As

Latest And Valid Q&A | Instant Download | Once Fail, Full Refund

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments