70-463 · Question #228
If you want to switch content from a nonpartitioned table to a partition of a partitioned table, what conditions must the nonpartitioned table meet? (Choose all that apply.)
The correct answer is A. It must have the same constraints as the partitioned table. B. It must have the same compression as the partitioned table. D. It must have a check constraint on the partitioning column that guarantees that all of the data E. It must have the same indexes as the partitioned table.. Switching data from a nonpartitioned table into a partition requires the source table to structurally match the destination partition in constraints, compression, indexes, and data range.
Question
If you want to switch content from a nonpartitioned table to a partition of a partitioned table, what conditions must the nonpartitioned table meet? (Choose all that apply.)
Options
- AIt must have the same constraints as the partitioned table.
- BIt must have the same compression as the partitioned table.
- CIt must be in a special PartitionedTables schema.
- DIt must have a check constraint on the partitioning column that guarantees that all of the data
- EIt must have the same indexes as the partitioned table.
How the community answered
(33 responses)- A73% (24)
- C27% (9)
Why each option
Switching data from a nonpartitioned table into a partition requires the source table to structurally match the destination partition in constraints, compression, indexes, and data range.
The nonpartitioned source table must have the same CHECK and NOT NULL constraints as the partitioned table to ensure data integrity rules are equivalent before the switch.
The source table must use the same compression setting (ROW, PAGE, or NONE) as the target partition; mismatched compression prevents the switch operation.
SQL Server has no requirement for a special schema named 'PartitionedTables'; the source table can reside in any schema.
A CHECK constraint on the partitioning column must be present on the source table to guarantee all rows fall within the target partition's range, which SQL Server verifies before allowing the switch.
The source table must have indexes that match those on the target partition, including the same columns, ordering, and index types, to ensure structural compatibility.
Concept tested: Table partition switching prerequisites and requirements
Source: https://learn.microsoft.com/en-us/sql/relational-databases/partitions/transfer-data-efficiently-by-using-partition-switching
Topics
Community Discussion
No community discussion yet for this question.