DP-800 — frequently asked questions

DigitsOnly must be derived from RawNumber, the extracted phone-number substring. Replacing on MessageRaw would keep unrelated message text and would not preserve the required NULL when no phone pattern matches.

Conceptually REGEXP_LIKE tests whether matching text exists, but the PhoneStatus expression is built as REGEXP_COUNT(MessageRaw, pattern) > 0; choose the drag value that matches that numeric comparison.

RCSI uses row versioning to provide a consistent snapshot for readers, eliminating the need for shared locks that would conflict with exclusive write locks.

Check sys.dm_exec_sessions for open_transaction_count > 0 and status = sleeping, then link via blocking_session_id in sys.dm_exec_requests.

Option A replaces everything from the '+' to the end of the string with nothing, deleting the domain name entirely.

It matches the literal plus sign, followed by any characters, stopping right before the '@' symbol.

It returns the earliest timestamp per group but cannot return the other columns of that specific ticket, so it never satisfies the requirement to return the earliest ticket row itself.

Yes. Partitioning by the extracted customer ID and the date, then ordering by CreatedAt ascending, makes row number 1 the earliest ticket for that customer on that day.

For data starting in 2020, '2021-01-01' separates 2020 from 2021; adding '2020-01-01' creates an empty pre-2020 partition, not a better yearly split.

The index must be partition-aligned so a single yearly partition can be rebuilt or reindexed; rebuilding on [PRIMARY] leaves it nonaligned and forces whole-table maintenance.

The question requires OAuth authentication, so Visual Studio signs in interactively and no token is written to configuration. Hard-coding a PAT with the repo scope also creates a credential-leak risk in the repository.

The .vscode folder is Visual Studio Code's configuration layout. Visual Studio 2026 reads project-scoped MCP servers from a .mcp.json file in the solution root, so only that path registers the server for Copilot Chat in Visual Studio.

No. The RLS filter predicate is evaluated first, so filtered rows never reach the result set and DDM has nothing to mask.

Not automatically. If the application connects with a non-admin login, RLS applies to that login; administrator bypass does not follow the app connection.

A clustered index determines physical storage order. Since the query filters by FleetId, indexing on LastUpdatedUtc would require scanning many rows before filtering, whereas FleetId provides direct access.

An index is covering when it contains all columns referenced in the query's WHERE and SELECT clauses. Here, including FleetId, LastUpdatedUtc, EngineStatus, and BatteryHealth allows the engine to satisfy the request entirely from the index.

Triggers run synchronously within the database transaction. Calling AI_GENERATE_EMBEDDINGS inside a trigger consumes significant SQL Server CPU and locks resources, violating the requirement to minimize DB CPU.

Only if real-time accuracy isn't critical and the dataset is small. For millions of rows, a full scan hourly causes severe performance degradation and is not considered an efficient maintenance strategy.

sys.dm_exec_requests provides direct access to session IDs and blocking relationships, making it easier to construct a self-referencing join for chain analysis.

OUTER APPLY allows you to execute a function (like getting SQL text) for each row in the left table, ensuring blocked sessions are included even if they lack immediate metadata.

No. Running queries and mutations requires the separate Run Queries and Mutations permission on the SalesApi item.

No. Without Run Queries and Mutations, the API cannot execute the query regardless of the connection identity or SSO setting.

Deadlocks are resolved immediately by killing one session. This session remains sleeping, indicating it is waiting, not deadlocked.

It means the session has begun a transaction but hasn't finished it, keeping all acquired locks active until commit or rollback.

The default RANGE frame aggregates all rows tied on TransactionDate, so same-day transactions show the same total. ROWS UNBOUNDED PRECEDING adds one row at a time for a true running total.

An inline TVF returns a rowset that can be joined or CROSS APPLY-ed and is inlined by the optimizer. A stored procedure cannot be referenced in a FROM clause, so it fails the join requirement.

Managed identities are only issued for Azure-hosted resources and their tokens come from the Azure Instance Metadata Service, which an on-premises server cannot reach. A service principal registered in Microsoft Entra ID is required instead.

No. A shared access key is a stored secret that must be configured and rotated manually, which is exactly the hard-coded credential problem the security requirement removes. Microsoft Entra ID authentication via service principal is the passwordless path.

You must configure the .sqlproj file to include the appropriate system reference paths or explicit references to Azure SQL system schemas.

The -bl flag generates a binary log file for diagnostics; it does not affect which files are compiled or referenced.

Yes. dotnet build compiles the project model and fails on unresolved objects or invalid schema references, so validation is not limited to the deployment action.

The deploy job depends on a successful build and is gated to pushes to main; pull requests can still run the build and test steps without publishing to db1.

SqlPackage publishes the built DACPAC to db1 and computes a deployment plan, which also verifies that the schema change can be applied to the target database.

Standard indexes cannot parse the internal structure of nvarchar(max) JSON data. You must extract the scalar value into a computed column first to make it indexable.

The INCLUDE clause adds non-key columns to the index leaf level. This allows the query to be satisfied entirely by the index (covering index), avoiding costly key lookups to the base table.

CDC is a heavier feature designed for detailed change history and auditing. The Azure Functions SQL trigger binding is specifically architected to use the lighter-weight Change Tracking feature for real-time notifications.

Change Tracking is designed to be low-overhead compared to CDC. It records primary key changes rather than full row snapshots, making it efficient for triggering external events like Azure Functions.

A clustered columnstore index transforms the entire table into a columnstore format. While good for pure analytics, it can significantly degrade OLTP insert/update performance. A nonclustered columnstore index sits alongside the existing clustered rowstore index, allowing the OLTP workload to remain optimized on the rowstore while offloading analytics to the columnstore.

No. A filtered index is a type of nonclustered index that covers a subset of rows. It is optimized for specific lookups or small range scans based on a predicate. It does not provide the compression and vectorized execution benefits needed for large-scale analytical aggregates across the table.

A Nonclustered Columnstore Index adds storage overhead and complexity. Since the question asks to minimize storage and the primary access path is scanning the entire table for reports, making the Columnstore index Clustering (the base table structure) is the most efficient approach.

It works functionally but fails the 'minimize storage' requirement. A standard B-tree rowstore index does not compress data as effectively as a columnstore index, leading to higher storage costs for a 10 TB table.

No. Scalar UDFs must be invoked within a SELECT list or assigned to a variable using SET. Using EXEC results in a syntax error.

The SQL Server Optimizer treats them as black boxes and often estimates a fixed row count (e.g., 100 rows), ignoring actual data distribution. This leads to suboptimal join strategies and plan instability.