Question 174 of 240 from exam DP-203: Data Engineering on Microsoft Azure
Question
There is a Dedicated SQL Pool in which the existing tables are using clustered columnstore index.
You are planning for a partition switch to improve the performance following your partition strategy.
You run the partition table function on the first two tables.
But they were not successful.
Which of the options tells about the possible reasons for this failure? (Multiple choice)
Answers
Explanations
Click on the arrows to vote for the correct answer
A. B. C. D. E.Correct Answer: B and C.
Dedicated SQL pool supports partition switching, splitting, and merging.
Each function is executed using the ALTER TABLE statement.
But in the case of Data Partition Switching between two tables, the partitions must align to their respective boundaries and table functions should be matching.
Otherwise, we will have errors in this operation, and partition metadata sync will be failed.
The reason behind this is that check constraints are not available to enforce the range of values in a table.
So, the source and target tables should have the same partition boundaries.
Options A and D are incorrect: For partition switching to succeed, the boundaries should be the same, and table definitions should be met.
So, they are incorrect and not a reason for the failure of the partition switching function.
Options B and C are correct: These will be possible reasons for the failure of the partition switching function.
Option E is incorrect: Dedicated SQL pool not only supports splitting and merging but also partition switching.
Based on the given scenario, here are some possible reasons for the failure of the partition switch:
A. Partition boundaries should not be the same: If the partition boundaries are the same for both the source and target tables, then it will cause a failure. The partition switch requires non-overlapping partition ranges between source and target tables.
B. Partition boundaries should be the same: If the partition boundaries are not the same for both the source and target tables, then it will cause a failure. The partition switch requires the same partition scheme and partition function for both the source and target tables.
C. Table definitions should match: The source and target tables should have identical schemas (including the column order, data types, and nullability) for a successful partition switch. Any differences in the schema between the source and target tables will result in a failure.
D. Table definitions shouldn't match: This option is incorrect. Table definitions should match for a successful partition switch.
E. Dedicated SQL Pool support only partition splitting and merging using ALTER TABLE statement: This option is incorrect. Dedicated SQL Pool supports partition switching using the ALTER TABLE SWITCH statement. The partition switch statement switches a partition from a partitioned table to a staging table, and vice versa.
Therefore, options A, C, and E are the possible reasons for the failure of the partition switch in the given scenario.
