You are a database developer on an instance of SQL Server 2008. You have two partitioned tables with identical structures, named Trx and Trx_Hy. They were both partitioned on the same column using the following partition function and partition scheme:
CREATE PARTITION FUNCTION Pf1 (int)
AS RANGE LEFT FOR VALUES (10000, 30000, 50000);
CREATE PARTITION SCHEME Ps1 AS PARTITION Pf1 TO (fg1, fg2, fg3, fg4);
Both tables contain data. You issue the following statement to move a partition from the Trx table to the Trx_Hy table:
ALTER TABLE Trx SWITCH PARTITION 1 TO Trx_Hy PARTITION 4;
What is the result?
A. The statement fails if partition 4 of Trx_Hy contains data.
B. The statement succeeds if partition 1 of Trx and partition 4 of Trx_Hy both contain data.
C. The statement fails because the two tables use the same partition function.
D. The statement fails if either of the specified partitions is empty.
Correct Answer: A
Explanation/Reference:
The ALTER TABLE statement modifies a table definition to modify, add, or drop columns and constraints, switch partitions, or disable or enable triggers. The ALTER TABLE…SWITCH statement allows you to switch a partition from one partitioned table to another, which helps you transfer subsets of data quickly and efficiently. To be able to switch partitions, both tables must exist and be partitioned on the same column. In addition, the receiving partition must be empty. In this scenario, you issued a statement that would move partition 1 of the Trx table to partition 4 of the Trx_Hy table. This statement would fail if partition 4 of the Trx_Hy table contains data.
The option that states the statement fails if either of the specified partitions is empty is incorrect. To move a partition, the receiving partition must be empty.
The option that states the statement succeeds if partition 1 of Trx and partition 4 of Trx_Hy both contain data is incorrect. The statement will fail if partition 4 of the Trx_Hy table contains data.
The option that states the statement fails because the two tables use the same partition function is incorrect. Partitioned tables can use the same partition function.
The partition function maps the rows of a table or index into partitions based on the specified partition boundary values.
