DP-800: Developing AI-Enabled Database Solutions

By Microsoft · Question Mode

QuestionQ52

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: Execute the following Transact-SQL statement.

DELETE FROM dbo.Orders -  
WHERE OrderDate < DATEADD(month, -36, SYSUTCDATETIME());

Does this accomplish the goal?

Explanation

A row-by-row DELETE is not a low-impact way to remove an entire monthly partition from a large partitioned table. It can consume transaction-log and processing resources and interfere with concurrent access; removing data through a partition-level approach is needed to minimize impact on other queries.

Community Discussion

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