Question 320
A supported modern T-SQL pipeline must reject strings that fail a regular-expression pattern and also normalize recurring whitespace through regex replacement. Which TWO functions are needed? Choose TWO.
- REGEXP_COUNT
- REGEXP_LIKE
- REGEXP_REPLACE
- REGEXP_SUBSTR
- REGEXP_INSTR
Explanation
Correct Answers
B:REGEXP_LIKE evaluates whether input text matches a regular expression and returns a predicate result; used in combination with REGEXP_REPLACE, it handles a separate requirement of this combined workflow.
C:REGEXP_REPLACE substitutes regex-matched substrings and returns the transformed text on supported platforms; used in combination with REGEXP_LIKE, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:REGEXP_COUNT counts matches of a regular expression in a source string; the alternative cannot substitute for REGEXP_LIKE and REGEXP_REPLACE under the given constraints.
D:REGEXP_SUBSTR extracts text matched by a regular expression rather than returning a Boolean match test; that function is not interchangeable with REGEXP_LIKE and REGEXP_REPLACE for the requested result.
E:REGEXP_INSTR returns a match position for a supplied regular-expression pattern; for the stated conditions, the deciding feature is REGEXP_LIKE and REGEXP_REPLACE rather than this behavior.
Question 321
A CATCH handler must distinguish a transaction that has already ended from one that remains active before calling ROLLBACK. Which T-SQL function returns 0 when no active user transaction exists, rather than -1 for an uncommittable transaction?
- TRY...CATCH
- SET XACT_ABORT ON
- THROW
- XACT_STATE()
Explanation
Correct Answer
D:XACT_STATE() returns 0 when no user transaction is active, 1 when an active transaction can commit, and -1 when an active transaction is uncommittable.
Incorrect Answers
A:TRY...CATCH structures error handling into protected and handler blocks for catchable execution errors; it addresses a different step; the question calls for XACT_STATE().
B:XACT_ABORT ON makes specified runtime failures abort and roll back a transaction, affecting the error-handling behavior of the batch; this differs from the XACT_STATE() requirement described in the scenario.
C:THROW explicitly raises or rethrows T-SQL errors and supports propagating failures through calling code; the required capability here is XACT_STATE(), which this operation does not supply.
Question 322
Draft product documentation is not approved for customer responses, even if the draft has the highest similarity score. Which action is justified by these conditions?
- Filter language metadata
- Enforce tenant predicate
- Apply effective-date policy
- Filter published state
Explanation
Correct Answer
D:A published-state predicate prevents unapproved drafts from entering the context supplied to the model.
Incorrect Answers
A:A language metadata predicate constrains the candidate corpus to the required locale before final answer construction; the missing behavior is Filter published state; this mechanism does something else.
B:Authorization-aware filtering restricts eligible search rows independently of similarity ranking and should be enforced server-side; that function is not interchangeable with Filter published state for the requested result.
C:A date-range eligibility predicate excludes outdated or future policies independently of embedding quality; for the stated conditions, the deciding feature is Filter published state rather than this behavior.
Question 323
A deployment stored procedure should catch a run-time conversion or constraint error and perform explicit rollback and logging logic. What is the most suitable implementation?
- BEGIN TRY / BEGIN CATCH
- THROW without arguments
- SAVE TRANSACTION
- XACT_STATE()
Explanation
Correct Answer
A:TRY...CATCH provides a structured handler for many run-time T-SQL errors, allowing recovery code to run in the CATCH branch.
Incorrect Answers
B:THROW without arguments inside CATCH rethrows the caught exception, preserving its original error details rather than inventing a replacement exception; the alternative cannot substitute for BEGIN TRY / BEGIN CATCH under the given constraints.
C:SAVE TRANSACTION establishes a savepoint inside an active transaction, allowing partial rollback when the transaction's state permits it; the condition in the stem is resolved by BEGIN TRY / BEGIN CATCH, not by this feature alone.
D:XACT_STATE() returns -1 for an uncommittable active transaction, permitting only rollback and read operations until that transaction is ended; the missing behavior is BEGIN TRY / BEGIN CATCH; this mechanism does something else.
Question 324
A team wants coding-assistant responses to follow project-wide T-SQL naming conventions whenever developers work in the repository. Which mechanism addresses the requirement?
- Repository copilot-instructions.md
- MCP server configuration
- Chat model selection
- Chat tool permissions
Explanation
Correct Answer
A:A repository instruction file can communicate persistent project-specific guidance to compatible GitHub Copilot tooling, without changing database enforcement rules.
Incorrect Answers
B:Configuring an MCP server endpoint tells an MCP-capable client which tool host it may contact, subject to authentication and policy; it addresses a different step; the question calls for Repository copilot-instructions.md.
C:The assistant's model selection controls model behavior in the supported chat tool, not the runtime semantics of deployed T-SQL; this differs from the Repository copilot-instructions.md requirement described in the scenario.
D:Tool permission and approval settings control reachable actions in the assistant session and need restriction before permitting database access; the required capability here is Repository copilot-instructions.md, which this operation does not supply.
Question 325
The ranking should not be dominated by inconsistent vector lengths when using a magnitude-sensitive similarity measure. What should be configured to meet this objective?
- Cosine distance
- Euclidean distance
- Dot-product distance
- Normalized vectors
Explanation
Correct Answer
D:Normalization rescales vectors to a selected norm, making downstream distance comparisons reflect a controlled magnitude basis.
Incorrect Answers
A:Cosine distance evaluates angular similarity, making it useful for text embeddings where direction carries semantic signal; the condition in the stem is resolved by Normalized vectors, not by this feature alone.
B:Euclidean distance measures geometric distance using coordinate differences and incorporates magnitude; the missing behavior is Normalized vectors; this mechanism does something else.
C:Dot-product-based vector distance applies the supported negative dot-product convention, with ordering affected by normalization and magnitudes; that function is not interchangeable with Normalized vectors for the requested result.
Question 326
A DBA wants a generated T-SQL deployment script from a DACPAC comparison to examine before application. Which approach should the team select?
- SqlPackage Script
- Static analysis rule
- SqlPackage DeployReport
- `dotnet build` of SQL project
- Declarative object file
- Schema Compare
Explanation
Correct Answer
A:SqlPackage Script emits deployment SQL instead of immediately publishing the changes to the target database.
Incorrect Answers
B:SQL code analysis can inspect modeled schema and code against configured rules as part of validation, complementing runtime tests; the required capability here is SqlPackage Script, which this operation does not supply.
C:DeployReport describes planned deployment changes derived from model comparison and supports approval workflows; the alternative cannot substitute for SqlPackage Script under the given constraints.
D:Building an SDK-style SQL project validates its schema model and emits a DACPAC when the build succeeds; the condition in the stem is resolved by SqlPackage Script, not by this feature alone.
E:SQL projects store declarative object definitions, which DacFx compares with deployed schemas to derive necessary changes; the missing behavior is SqlPackage Script; this mechanism does something else.
F:Schema Compare contrasts schema definitions from two sources and can help identify drift and reconcile them; that function is not interchangeable with SqlPackage Script for the requested result.
Question 327
An Azure SQL table has an eligible VECTOR column but no vector index yet. The application query already uses FROM VECTOR_SEARCH. Choose TWO: the DDL statement to build a vector index and the SELECT clause needed for approximate top-k retrieval with the latest index format. Choose TWO.
- VECTOR_SEARCH
- FORCE_ANN_ONLY hint
- SELECT TOP (N) WITH APPROXIMATE
- VECTOR_DISTANCE exact ordering
- CREATE VECTOR INDEX
Explanation
Correct Answers
C:SELECT TOP (N) WITH APPROXIMATE is the select-list modifier required by the current VECTOR_SEARCH syntax to request ANN retrieval with the latest vector-index version.
E:CREATE VECTOR INDEX is the DDL operation for building a supported ANN index over an eligible VECTOR column; it is separate from the query syntax used to consume that index.
Incorrect Answers
A:VECTOR_SEARCH provides vector-neighbor retrieval and can use supported ANN indexing under the appropriate query form; that function is not interchangeable with SELECT TOP (N) WITH APPROXIMATE and CREATE VECTOR INDEX for the requested result.
B:FORCE_ANN_ONLY requests the approximate index strategy and has prerequisites including an appropriate index and approximate top-k query; for the stated conditions, the deciding feature is SELECT TOP (N) WITH APPROXIMATE and CREATE VECTOR INDEX rather than this behavior.
D:Sorting by VECTOR_DISTANCE calculates exact distances and can benchmark recall against indexed approximate results; this differs from the SELECT TOP (N) WITH APPROXIMATE and CREATE VECTOR INDEX requirement described in the scenario.
Question 328
A serverless routine should write processed records back to a SQL table through its configured function binding. Which choice best meets these constraints?
- Azure Functions SQL output binding
- Azure Logic Apps SQL connector
- CDC-based ingestion service
- Azure Functions SQL input binding
Explanation
Correct Answer
A:A SQL output binding can persist supported output values through configured SQL connectivity and binding semantics.
Incorrect Answers
B:Logic Apps offers managed connectors and workflow orchestration for database-triggered or database-driven business processes; the condition in the stem is resolved by Azure Functions SQL output binding, not by this feature alone.
C:A CDC consumer reads durable change captures and can reconstruct supported insert/update/delete change activity; the missing behavior is Azure Functions SQL output binding; this mechanism does something else.
D:A SQL input binding can supply configured database query results to a supported Azure Function invocation; that function is not interchangeable with Azure Functions SQL output binding for the requested result.
Question 329
A procedure refers to an object in another controlled database, and the project needs to resolve that reference during compilation. Which option satisfies the desired behavior most directly?
- Static analysis rule
- Target platform configuration
- SDK-style SQL database project
- Database reference
- `dotnet build` of SQL project
Explanation
Correct Answer
D:Database references supply schema models for external dependencies and help SQL project builds validate cross-database object references.
Incorrect Answers
A:SQL code analysis can inspect modeled schema and code against configured rules as part of validation, complementing runtime tests; it addresses a different step; the question calls for Database reference.
B:SQL project target-platform settings influence build-time compatibility checks for supported database features and syntax; this differs from the Database reference requirement described in the scenario.
C:An SDK-style SQL project provides declarative schema files and cross-platform build tooling supported by Microsoft.Build.Sql; the required capability here is Database reference, which this operation does not supply.
E:Building an SDK-style SQL project validates its schema model and emits a DACPAC when the build succeeds; the condition in the stem is resolved by Database reference, not by this feature alone.
Question 330
A maintenance procedure needs one elevated table permission while ordinary callers must not receive that permission directly. Which approach should the team select?
- Group-based database access
- Certificate-signed SQL module
- Dedicated managed identity
- Database role membership
Explanation
Correct Answer
B:Signing an SQL module can attach narrowly scoped certificate-associated permissions during execution without broad user privilege escalation.
Incorrect Answers
A:Group-backed principals allow permissions to be managed through authorized group membership while database grants remain scoped; the missing behavior is Certificate-signed SQL module; this mechanism does something else.
C:A dedicated managed identity offers a workload principal whose token authorization can be controlled independently of human account lifecycle; for the stated conditions, the deciding feature is Certificate-signed SQL module rather than this behavior.
D:A database role collects permissions for members and reduces the need to duplicate identical grants on individual principals; it addresses a different step; the question calls for Certificate-signed SQL module.