You are the database administrator of your company. The network contains a SQL Server 2008 computer thathas two partitioned tables named Sales and Sales_Hy.
You want to switch a partition from the Sales table to the Sales_Hy table.
Which Transact-SQL statement should you use?
A.
ALTER SCHEMA
B.
ALTER PARTITION FUNCTION
C.
ALTER PARTITION SCHEME
D.
ALTER TABLE
Explanation:
You should use the ALTER TABLE statement. The ALTER TABLE statement modifies a table definition tomodify, add, or drop columns and constraints, switch partitions, or disable or enable triggers. The ALTERTABLE…SWITCH statement allows you to switch a partition from one partitioned table to another, which helpsyou transfer subsets of data quickly and efficiently. To switch the first partition of the Sales table into the thirdpartition of the Sales_Hy table, you should use the following Transact-SQL statement:
ALTER TABLE Sales SWITCH PARTITION 1 TO Sales_Hy PARTITION 3
To be able to switch partitions, you must ensure that the following requirements are met:
? Before you perform the switch operation, you must ensure that the table that contains the partition and thetable that will receive the partition exist in the database. You must also ensure that both the tables sharethe same filegroup.
? The target partition must be empty.
? The partitions must be created on the same column.You should not use the ALTER SCHEMA statement because this statement does not allow you to switch apartition from one partitioned table to another. The ALTER SCHEMA statement is used to transfer a securablefrom one schema to another. You should not use the ALTER PARTITION FUNCTION
statement because this statement does not allow you toswitch a partition from one partitioned table to another. This statement modifies a partition function by splitting ormerging its boundary values. For example, you can use the ALTER PARTITION FUNCTION statement to split apartition of any table or index that uses the partition function into two partitions, or merge two partitions into onepartition. The complete syntax for the ALTER PARTITION FUNCTION statement is as follows:
ALTER PARTITION FUNCTION partition_function_name() { SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value ) };
You should not use the ALTER PARTITION SCHEME statement because this statement does not allow you toswitch a partition from one partitioned table to another.
This statement adds or modifies the designation of afilegroup to a partition scheme.Objective:
Performing Data Management TasksSub-Objective:
Manage data partitions.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > ALTER TABLE (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Partitioned Tables and Indexes > Designing PartitionedTables and Indexes > Designing Partitions to Manage Subsets of Data