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…
Full set: 61 questionsThe 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.
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.
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 ...".
Keep practicing
Take the free DP-800 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other DP-800 practice questions
- Question 1You need to recommend a solution that will resolve the ingestion pipeline failure issues.…
- Question 2You have an Azure SQL database That contains database-level Data Definition Language (DDL…
- Question 3You have a Microsoft SQL Server 2025 instance that contains a database named SalesDB Sale…
- Question 5You have a GitHub Actions workflow that builds and deploys an Azure SQL database. The sch…
