DP-800: Developing AI-Enabled Database Solutions

By Microsoft · Question Mode

QuestionQ51

VerifiedSecure, optimize, and deploy database solutions

You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders.

  • dbo.Orders has a clustered index.
  • It contains three years of data.
  • It is partitioned by a column named OrderDate by month.

You need to remove all rows for the oldest month. The solution must minimize the impact on other queries that access the data in dbo.Orders.

Solution: Determine the partition number for the oldest month, and then execute the following Transact-SQL statement.

TRUNCATE TABLE dbo.Orders  
WITH (PARTITIONS (partition number));

Does this achieve the goal?

Explanation

TRUNCATE TABLE with the PARTITIONS clause removes data from the specified partition without logging individual row deletions. Truncating only the oldest monthly partition targets the required data-retention slice and uses fewer transaction-log and locking resources than deleting its rows individually. The table and its indexes must be aligned to use partition truncation.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!