Microsoft Certified SQL AI Developer Associate certification (DP-800) - Microsoft Exam Questions

You have a GitHub Codespaces environment that has GitHub Copilot Chat installed and is connected to a SQL database in Microsoft Fabric named DB1 DB1 contains tables named Sales.Orders and Sales.Customers. You use GitHub Copilot Chat in the context of DB1 . A company policy prohibits sharing customer Personally Identifiable Information (Pll), secrets, and query result sets with any Al service. You need to use GitHub Copilot Chat to write and review Transact-SQL code for a new stored procedure that will join Sales.Orders to sales .Customers and return customer names and email addresses. The solution must NOT share the actual data in the tables with GitHub Copilot Chat.

What should you do?

You have a Microsoft SQL Server 2025 instance that contains a database named SalesDB SalesDB supports a Retrieval Augmented Generation (RAG) pattern for internal support tickets. The SQL Server instance runs without any outbound network connectivity. You plan to generate embeddings inside the SQL Server instance and store them in a table for vector similarity queries. You need to ensure that only a database user account named AlApplicationUser can run embedding generation by using the model.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

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 number for the oldest month, and then run the following Transact- SQL statement. TRUNCATE TABIE dbo.Orders
WITH (PARTITIONS (partition number));
Does this meet the goal?

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: Run the following Transact-SQL statement.
DELETE FROM dbo.Orders

WHERE OrderDate < DATEADD(nonth, -36, SYSUTCDATETIME());
Does this meet the goal?

You have an Azure SQL database that supports a customer-facing API. The API calls a stored procedure named dbo.GetCustomerOrders thousands of times per hour. After a deployment that updated indexes and statistics, users report that the API endpoint backed by dbo.Getcustomerorders is slower. In Query Store, the same query now has two persisted execution plans. During the last hour, the newer plan had a significantly higher average duration and CPU time than the older plan. You need to restore the previous performance quickly, without changing the API code.

Which Transact-SQL command should you run?