You administer a database that includes a table named Customers that contains more than
750 rows. You create a new column named PartitionNumber of the int type in the table.
You need to assign a PartitionNumber for each record in the Customers table.You also need
to ensure that the PartitionNumber satisfies the following conditions:
Always starts with 1.
Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?
A.
CREATE SEQUENCE CustomerSequence AS int
START WITH 0
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence
B.
CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence
C.
CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1
DROP SEQUENCE CustomerSequence
D.
CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100
CYCLE
UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ff878091.aspx
A starts at 0.
C does will throw an exception because it does not have CYCLE, and starts at 1 (because of CustomerSequence + 1).
D will restart at 0 after 100 row because of MINVALUE 0.
That’s why it’s B.
0
0
Option A doesn’t start at 0. When START WITH and MINVALUE are specified, the Sequence initializes to MinValue. The only reason why option A is wrong is because it omits the CYCLE keyword (whereas the sequence is expected to start again after reaching MAXVALUE 100). Option B is the choice though.
0
0
On the first initialization, the sequence will start with the START WITH value. After the sequence meets or exceeds the MAX VALUE, it will restart at the MIN VALUE. See source in the explanation section.
0
0
B cause of cycle!
0
0
B cause of START WITH…
0
0
Actual, it is because of cycle…
0
0
What’s more, part of new 200Q 70-461 dumps FYI:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0