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.
Option C is right
0
0
I’m sorry Option A is correct..
0
0
my answer is also A
0
0
**New**
Question 11
You plan to create four stored procedures that will use transactions. The stored procedures will be configured as shown in the following table:
Stored procedures name Concurrency strategy
SP1 A transaction will only recognize data changes made before the start of transaction. Other transactions are allowed to modify data.
SP2 A transaction can read data made from another transaction that has not been committed.
SP3 A transaction can only read data made from another transaction that has been committed.
SP4 During a transaction, no other transactions can modify data
Isolation Levels
READ COMMITTED
READ UNCOMMITED
REPEATABLE READ
SERIALIZABLE
Answer area
SP1: —————SNAPSHOT
SP2:—————-READ UNCOMMITTED
SP3: —————–READ COMMITTED
SP4: —————–SERIALIZABLE
NOTE:
Other posters have given the answer for SP2 = REPEATABLE READ.
My Opinion:
Whereas REPEATABLE READ eliminates both DIRTY and NONREPEATABLE READ anomalies, it does not guarantee there wouldn’t be PHANTOM READS.
In Repeatable Read Isolation, a transaction will “NEVER read data made from another transaction that has not been committed”, as this could result in both dirty reads and nonrepeatable reads.
Also, Repeatable Read Isolation provides higher READ CONSISTENCY but lesser CONCURRENCY than Read Committed.
0
0