DP-800 practice question 4 of 15

You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders, dbo.Orders has a clustered index, contains three years of data…

Choose your answer, then check it against the explanation.

You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders, dbo.Orders has a clustered index, contains three years of data, and is partitioned by a column named OrderDate by month. You need to remove all the rows for the oldest month. The solution must minimize the impact on other queries that access the data in dbo.orders. Solution; Identify the partition scheme (or the oldest month, and then run the following Transact-SQL statement. ALTER TABLE dbo.Orders DROP PARTITION SCHEME (partition_scheme_name); Does this meet the goal?

Answer options

Correct Answer:

Explanation

The proposed solution is incorrect. The Transact-SQL statement DROP PARTITION SCHEME is used to remove the entire partition scheme definition from the database. It cannot be executed while the scheme is being used by a table, such as dbo.Orders. The goal is to remove data from a single, specific partition (the oldest month), not to delete the entire partitioning structure. The correct and most efficient method for this "sliding window" scenario is to use ALTER TABLE ... SWITCH PARTITION to move the target partition to a staging table, which can then be truncated or archived. This approach minimizes logging and impact on the primary table.

Why Incorrect

A. Yes: This is incorrect because the DROP PARTITION SCHEME command does not remove data from a specific partition. It attempts to remove the scheme's definition and would fail because the scheme is in use.

References

1. Microsoft Learn, DROP PARTITION SCHEME (Transact-SQL): This document states, "Removes a partition scheme from the current database." It also specifies under 'Arguments' and 'Permissions' that "A partition scheme can be dropped only if there are no tables or indexes currently using the partition scheme." This confirms the proposed command is inappropriate and would fail.

Source: https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-partition-scheme-transact-sql

2. Microsoft Learn, Partitioned tables and indexes: This guide describes the "sliding window" scenario for managing large tables. It explicitly recommends using ALTER TABLE...SWITCH as the most efficient method to move old data out of a partitioned table. This highlights the correct procedure that should have been used instead of DROP PARTITION SCHEME.

Source: https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes, Section: "Managing partitions with a 'sliding window' scenario".

3. Microsoft Learn, ALTER TABLE (Transact-SQL): The documentation for ALTER TABLE details the SWITCH PARTITION clause. It explains that this operation reassigns a block of data between two tables. This is a metadata-only operation, making it extremely fast and minimally logged, thus meeting the requirement to minimize impact.

Source: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql, Section: "SWITCH [ PARTITION ... ] TO ...".

Question 4 of 15

Keep practicing

Take the free DP-800 practice test

Ten exam-style questions with answers and explanations, plus the exam facts and study guides.

Start the free practice test