You have an Azure SQL database named ToDo that contains a table named dbo.ToDo.Your company plans to develop an Azure Functions app to run whenever the rows in dbo.ToDo change. The app will process INSERT, UPDATE, and DELETE events by using the Azure SQL trigger binding.You need to configure ToDo to support the planned app.What should you do?
Answer(s): A
To use the Azure SQL trigger for Azure Functions, your database table must have Change Tracking enabled. This is the underlying mechanism the trigger uses to monitor row-level modifications.Required Table Configuration To facilitate the app, you must follow these three steps:1. Enable Change Tracking on the Database Before the table can be tracked, the feature must be turned on at the database level.2. Enable Change Tracking on the Table The specific table the Function monitors must have tracking enabled.3. Define a Primary Key The Azure SQL trigger requires the table to have a Primary Key. The trigger uses the Primary Key to identify which specific rows were changed. If your table lacks a Primary Key, the Function will fail to start.
https://demiliani.com/2025/05/08/azure-function-sql-trigger-how-to-use-it-and-why-it-can-be-useful-in-your-business-central-projects/
DRAG DROP (Drag and Drop is not supported)You have an Azure SQL database that contains a table named dbo.Customers. dbo.Customers contains the following columns:-CustomerId (int)(primary key)-ProfileJson (nvarchar(max))You have an application that returns phone numbers in a format of +000 000-000-0000. The phone numbers are stored in ProfileJson.You need to write a query that returns:-One row per customer-A PhoneNUmerals column that contains only the digitsHow should you complete the Transact-SQL query? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.NOTE: Each correct selection is worth one point.Select and Place:
To extract only the digits from a JSON property in T-SQL, you need to use JSON_VALUE to parse the string and TRANSLATE combined with REPLACE (or a similar pattern like REGEXP_REPLACE) to strip non-numeric characters.Box 1: JSON_VALUE(c.ProfileJson, '$.phone') Extracting from JSON JSON_VALUE(c.ProfileJson, '$.phone') JSON_VALUE extracts a scalar value from a JSON string.Box 2: REGEXP_REPLACE(p.PhoneRaw [^0-9]. ' ') In Transact-SQL (T-SQL), the REGEXP_REPLACE function can be used as a more powerful and concise alternative to the REPLACE(TRANSLATE(...)) pattern.Box 3: p.PhoneRawFilter p.PhoneRaw This ensures that only customers who actually have a phone number in their JSON profile are included in the final set.
https://techgenies.com/what-data-type-would-a-phone-number-be-in-sql https://devblogs.microsoft.com/azure-sql/general-availability-announcement-regex-support-in-sql-server-2025-azure-sql/
DRAG DROP (Drag and Drop is not supported)You have an Azure SQL database named SalesDB that supports an ecommerce application. SalesDB contains a table named dbo.Orders that has a clustered index on a column named OrderId.dbo.Orders receives continuous OLTP inserts and updates during business hours.Your analytics team runs hourly aggregate queries that scan dbo.Orders to calculate revenue trends for recent dates.You need to improve the performance of the hourly analytics queries without significantly affecting OLTP throughput. The solution must meet the following requirements:-Support near-real-time (NRT) analytics on the dbo.Orders table.-Reduce read time when retrieving analytical data from dbo.Orders.-Support indexing only rows that match a predicate, such as Active = 1.Which type of index should you use for each requirement? To answer, drag the appropriate index types to the correct requirements. Each index type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.NOTE: Each correct selection is worth one point.Select and Place:
Box 1: a Nonclustered Columnstore Index on an existing rowstore table. Support near-real-time (NRT) analytics on the dbo.Orders table.The best index for this scenario is a Nonclustered Columnstore Index (NCCI). This approach enables Hybrid Transactional and Analytical Processing (HTAP) by allowing you to run high-performance analytics directly on your OLTP table without moving data to a separate warehouse. Why Nonclustered Columnstore is the Best Choice Optimized for Aggregates: It uses columnar storage to drastically speed up SUM, AVG, and COUNT operations.Minimal OLTP Impact: Unlike a Clustered Columnstore, the table remains a B-tree (Clustered Index), keeping your singleton inserts and updates fast.NRT Analytics: Since the index is on the same table, the data is updated automatically as transactions occur.Compression: It significantly reduces the I/O needed for large table scans.Box 2: A filtered Nonclustered Columnstore Index with a WHERE predicate Reduce read time when retrieving analytical data from dbo.Orders.A Nonclustered Columnstore Index (NCCI) is the best choice for this scenario. This specific index type allows you to run high-performance analytics on an OLTP table without moving the data to a separate data warehouse.To ensure the OLTP throughput remains high, consider the following configuration: Filtered Indexing: If your aggregate queries only look at "recent dates," create a Filtered Nonclustered Columnstore Index. This only indexes the latest data (e.g., WHERE OrderDate > '2024-01-01'), reducing the overhead on older rows.Box 3: A filtered Nonclustered Columnstore Index with a WHERE predicate Support indexing only rows that match a predicate, such as Active = 1.The best solution for your requirement is a Filtered Index. A filtered index is a nonclustered index that includes a WHERE clause. It allows you to index a specific subset of data, which minimizes the index size and reduces the overhead of maintaining the index during OLTP operations. Why Filtered Indexes Fit Your Scenario Improved Query Performance: The SQL optimizer can target a much smaller subset of data (e.g., only Active = 1), leading to faster seeks and scans.Reduced Maintenance Overhead: Unlike a full nonclustered index, a filtered index is only updated when a row meets the predicate or is changed to/from meeting the predicate.Lower Storage Costs: Because it only stores a fraction of the table's rows, it consumes significantly less disk space and memory.Statistics Accuracy: SQL Server maintains more accurate statistics for the specific data subset, leading to better execution plans for those aggregate queries.
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance https://www.sqlshack.com/top-10-questions-answers-sql-server-indexes/
DRAG DROP (Drag and Drop is not supported)You have an Azure SQL database that stores sales data and contains tables named Sales and Products. Sales contains three columns named SalesDate, ProductKey, and TotalSale,Sales is 10 TB and is loaded nightly by using a batch process. Most reporting queries scan large portions of Sales, filter on SalesDate or ProductKey, and use SUM() to aggregate TotalSale.Products is relatively small and is used primarily for point lookups and joins to Sales.You need to recommend which indexes to create to optimize the reporting queries. The solution must minimize storage requirements.Which type of index should you recommend for each table? To answer, drag the appropriate index types to the correct tables. Each index type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.NOTE: Each correct selection is worth one point.Select and Place:
Box 1: A clustered columnstore index SalesA Clustered Columnstore Index (CCI) is the best choice for this scenario. For a 10 TB table focused on large-scale analytical scanning and aggregation, columnstore technology offers the most efficiency.Why Clustered Columnstore is the Winner Massive Compression: Columnstore indexes typically achieve 10x compression over raw data. This significantly reduces the 10 TB storage footprint.Segment Elimination: The engine only reads the specific columns needed (e.g., TotalSale). It skips "segments" of data that don't match the SalesDate or ProductKey filters.Batch Mode Processing: It uses a "batch mode" execution that processes rows in chunks rather than one byone, making SUM() operations extremely fast.Optimized for Bulk Loads: Since your data loads via a nightly batch process, CCIs handle these high-volume inserts more efficiently than traditional B-tree indexes.Box 2: A nonclustered rowstore index ProductsFor the Product table, you should create a Rowstore (B-tree) nonclustered index.Why Rowstore for the Product Table? Optimized for Point Lookups: Rowstore indexes are specifically designed to find individual rows quickly based on a key (like ProductKey).Small Table Efficiency: Columnstore indexes have high overhead (rowgroup metadata) that can actually increase the storage footprint of small tables.Join Performance: Since reporting queries join the 15 TB Sales table to the Product table, a Rowstore index on the join key allows the SQL engine to perform efficient Index Seeks.Minimal Storage: For a small table, a standard B-tree index uses negligible space compared to the compression structures required for Columnstore.
https://microsoft-bitools.blogspot.com/2017/11/ctas-fastest-way-to-load-data-on-mpp.html https://link.springer.com/chapter/10.1007/978-1-4842-8048-5_13
HOTSPOT (Drag and Drop is not supported)You have an Azure SQL database that contains a table named dbo.SupportTickets. dbo.SupportTickets contains a JSON column named Payload and a datetime column CreatedAt.You need to generate a report for the last seven days that meets the following requirements:-Returns exactly one row per customer per day-For each customer and day, returns the earliest ticket-Includes the customer ID stored in PayloadHow should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.Hot Area:
To complete your SQL query, you need to use the JSON_VALUE function to extract data from the JSON column and the ROW_NUMBER window function to rank the tickets by time.Box 1: JSON_VALUE JSON_VALUE: Use this to extract the customer.id from the payload.Box 2: ROW_NUMBER ROW_NUMBER(): Use this to assign a unique rank to each ticket per customer per day.Box 3: 1 1: Filter for the first ticket in each partition.
https://datalemur.com/blog/tiktok-sql-interview-questions
You are developing an Azure SQL database solution from a locally cloned GitHub repository by using Microsoft Visual Studio Code and GitHub Copilot Chat.You need to ensure that GitHub Copilot Chat can call the hosted GitHub MCP Server tools by using OAuth. The MCP server configuration must be scoped to the repository.What should you do in Visual Studio Code?
Answer(s): B
To connect GitHub Copilot Chat to an Azure SQL Database using a hosted GitHub MCP (Model Context Protocol) Server, follow these steps. This setup ensures that Copilot can interact directly with your repository and database schema using OAuth.Step 1: Configure the MCP Server in VS Code You must set the scope to the Workspace level to ensure the server is restricted to your specific repository. Open your locally cloned GitHub repository in VS Code. Open the Command Palette (Ctrl+Shift+P or Cmd+Shift+P). Type and select: MCP: Add Server. Select HTTP (HTTP or Server-Sent Events). Enter the URL: https://api.githubcopilot.com/mcp/ Crucial: When prompted for the configuration scope, select Workspace Settings. This saves the config to .vscode/settings.json, binding the server to this repo. Step 2: Authenticate via OAuth Once the server is added, Copilot needs permission to act on your behalf. Open the GitHub Copilot Chat panel. You will see a notification or a prompt within the chat asking to Authorize the MCP server. Follow the browser prompts to sign in and grant OAuth permissions. This allows the hosted MCP server to "see" your repository's code and metadata.
https://skywork.ai/skypage/en/The-Ultimate-Guide-to-GitHub's-MCP-Server:-Bridging-AI-and-Your-Codebase/1970685991323758592
HOTSPOT (Drag and Drop is not supported)You have an Azure SQL database that contains a table named Table1. Table1 contains 25,000,000 rows of data and a datetime2 column named DateKey. The data in Table1 spans the years 2020 through 2021.You need to partition the data in Table1 by year. The solution must minimize how long it takes to rebuild or reindex the table.How should you complete the Transact-SQL code? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.Hot Area:
Box 1: RANGE RIGHT To partition an Azure SQL database table with 25 million rows by year (2020–2021) using datetime2 while minimizing reindexing time, you should use a RANGE RIGHT partition function with boundary values set to the start of each year.Using RANGE RIGHT is generally preferred for date-based partitioning because it ensures that new data (e.g., for 2022) falls into a new, empty partition, minimizing the data movement required for maintenance operations.Box 2: '2021-01-01T00:00:00.000', '2022-01-01T00:00:00.000',Create the Partition Function This function defines the boundaries at the very beginning of each year. CREATE PARTITION FUNCTION pf_YearlyPartition (datetime2) AS RANGE RIGHT FOR VALUES ( '2021-01-01T00:00:00.000', '2022-01-01T00:00:00.000', '2023-01-01T00:00:00.000' -- Pre-emptive boundary for 2023 );Using RANGE RIGHT ensures that the boundary value (the start of the year) belongs to the partition to its right. This aligns with standard time-series data patterns where a partition holds data starting from "YYYY-01-01 00:00:00.000" onwards.
https://www.certlibrary.com/blog/accelerating-data-management-with-sql-server-table-partitioning-and-partition-switching
You are developing an Azure SQL solution by using Microsoft Visual Studio 2026. The solution uses a GitHub repository.You plan to use GitHub Copilot Chat to access the GitHub repository tools by connecting to the GitHub MCP Server.You need to configure Visual Studio to support the planned configuration. The solution must rely on OAuth to access the MCP server.What should you create?
In this scenario, you need to create an mcp.json file. This file acts as the configuration bridge that tells Visual Studio 2026 and GitHub Copilot Chat how to connect to and communicate with the GitHub MCP Server.Key Configuration Details The mcp.json file is typically placed in your solution's root directory or a specific .mcp folder to enable project-specific toolsConfiguration Steps 1. Create the MCP Configuration File You must define the connection to the GitHub MCP server so Copilot Chat can interact with your repository tools. File Name: mcp.json Location: Usually placed in %AppData%\Microsoft\VisualStudio\2026\mcp.json (or the project root depending on your specific extension settings). Content: { "mcpServers": { "github": { "url": "https://api.githubcopilot.com/mcp/", "auth": "OAuth" } } }2. Authenticate via OAuth3. Connect Azure SQL in VS 2026
https://www.workato.com/the-connector/mcp-server-tutorial/
Post your Comments and Discuss Microsoft DP-800 exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the DP-800 content, but please register or login to continue.