Pass the Microsoft Microsoft Certified: SQL AI Developer DP-800 Questions and answers with CertsForce

When you change embedding models, the stored vectors should be treated as belonging to a different embedding space unless you intentionally keep the entire corpus consistent. Microsoft’s vector guidance notes that when most or all embeddings are replaced with fresh embeddings from a new model, the recommended practice is to reload the new embeddings and, for large-scale replacement scenarios, consider dropping and recreating the vector index afterward so search quality remains predictable.

This question also says applications must continue to use VECTOR_SEARCH without runtime errors . VECTOR_SEARCH requires compatible vector dimensions, and the vector column already exists. Azure OpenAI documentation shows that text-embedding-ada-002 is fixed at 1536 dimensions and text-embedding-3-small supports up to 1536 dimensions . That means the migration can remain compatible with a VECTOR(1536) column, but the right implementation step is still to re-embed the existing rows so the table does not contain a mixed corpus produced by different models.

The other options are not appropriate:

B normalization does not solve a model migration problem.

C converting the vector column to nvarchar(max) would break vector-native search design.

D a vector index improves performance, but it does not migrate old embeddings to the new model.

A REST GET request to the order entity that includes the access token and x-MS-API-ROLE: operations will return data. → No

When DAB runs the stored procedure, the database policy defined on the FinalizeOrder entity will be enforced. → Yes

If the client omits the x-MS-API-ROLE header but still sends the same access token, the order entity read request will run in the authenticated role context. → Yes

The first statement is No . In Data API builder, when a valid token is sent with X-MS-API-ROLE, the request runs in that requested role if that role is present in the token . Here, that means the effective role becomes operations , not authenticated. But the order entity grants read only to the authenticated role, not to operations, so the GET request would not be authorized to return data.

The second statement is Yes . DAB evaluates the request against the permissions and policies configured for the effective role on the requested entity. The FinalizeOrder entity grants execute to role operations and includes a database policy of TenantId = @claims.tenantid, so that policy is part of the enforced authorization/filtering behavior when the stored procedure entity is executed.

The third statement is Yes . If the client sends a valid access token without X-MS-API-ROLE, DAB uses the built-in Authenticated system role by default. Since the order entity allows read for the authenticated role, that read request runs in the authenticated role context.

Top of Form

Bottom of Form

The correct answer is B . Microsoft’s SSMS Copilot documentation states that queries from Copilot in SSMS are executed under the context of the user’s login and permissions , and that there are no separate permissions for Copilot in SSMS . That means Copilot-run Transact-SQL cannot access more data than the developer’s own database principal is already allowed to access.

That is why the other options are incorrect:

A is wrong because Copilot does not use a separate read-only sandbox in place of database permissions.

C is wrong because enforcement is not a client-side filtering trick; it is enforced by the database security context of the current login.

D is wrong because Copilot does not apply a different RLS model from the developer; it simply runs under the same login context.

So the security boundary is the developer’s existing database identity and permissions .

Unit tests run automatically whenever changes are pushed to main. → Yes

Schema validation occurs during the Build step. → Yes

Schema validation occurs during the Deploy step. → No

The first statement is Yes . The workflow is configured to trigger on both push to main and pull_request targeting main. The unit-tests job has this condition:

if: github.ref == ' refs/heads/main '

On a push to main , GitHub sets github.ref to refs/heads/main, so the condition is true and the unit-tests job runs. GitHub’s workflow syntax documentation confirms that push.branches: [main] triggers on pushes to main, and the github.ref value for branch pushes is the fully qualified ref such as refs/heads/main.

The second statement is Yes . The Build step runs:

dotnet build db1.sqlproj --configuration Release

For an SDK-style SQL database project, the build process produces a .dacpac and validates the database project model as part of compilation/build. Microsoft’s SQL database project documentation describes SDK-style SQL projects as the project format used for SQL Database Projects, and Microsoft’s command-line build documentation is specifically about building a .dacpac from that SQL project. That means schema-level project validation happens during build.

The third statement is No . The Deploy step uses:

SqlPackage /Action:Publish ...

Microsoft documents that SqlPackage Publish incrementally updates the target database schema to match the source .dacpac. That is a deployment operation, not the primary schema-validation stage of the SQL project source itself. In this workflow, the schema is validated when the SQL project is built into the .dacpac; the deploy step applies that built artifact to the target database.

The members of SqlUsers can modify the data in SalesDB via SalesApi. → No

The members of SqlUsers can view the data in SalesDB via SalesApi. → No

The members of SqlUsers can change the field mappings of SalesApi. → Yes

For both viewing and modifying data through SalesApi, the key missing permission is Run Queries and Mutations . Microsoft’s Fabric GraphQL documentation states that callers need Execute permissions for the GraphQL API , which correspond to the Run Queries and Mutations option, and with SSO connectivity they also need appropriate permissions on the underlying data source. In the exhibit, SqlUsers has View and Edit GraphQL item selected, but Run Queries and Mutations is not selected, so members cannot query or mutate data through the API.

The third statement is Yes because the group was explicitly granted View and Edit GraphQL item . That permission is the one that allows users to open and modify the GraphQL item itself, including schema-related configuration such as field mappings in the API item. The workspace Viewer role does not by itself grant query execution through the API, but the direct GraphQL item permission shown does allow editing the item.

For authentication , the correct choice is Enable a managed identity and use Microsoft Entra authentication . Azure App Service supports managed identity, and Microsoft documents this as the recommended way to connect to Azure SQL Database without passwords or secrets . This minimizes administrative effort because there are no SQL passwords to rotate, store, or manage.

For network access , the correct choice is Create a private endpoint and disable public network access . Microsoft documents that when public network access is disabled , Azure SQL Database only accepts connections through private endpoints . That keeps the database traffic on Azure private networking rather than exposing it through the public endpoint. Microsoft also notes that simply adding a private endpoint does not disable public access by itself, so disabling public network access is part of the correct configuration.

The other options are weaker:

Rotated SQL credentials or storing SQL logins in Key Vault still use passwords and create more admin overhead.

Allowing Azure services, adding outbound IP firewall rules, or database firewall rules still rely on the public endpoint , so they do not best satisfy the requirement that database traffic remain within the subscription.

The correct answer is C because the email must be normalized by removing only the subaddressing portion between the plus sign and the @, while preserving the domain. JSON_VALUE is the correct function to extract the scalar email value from the JSON document. Microsoft states that JSON_VALUE is used to extract a scalar value from JSON text.

Then REGEXP_REPLACE should remove the pattern \+.*@ and replace it with a single @. For example:

user1+promo@contoso.com → user1@contoso.com

Microsoft documents that REGEXP_REPLACE returns the source string with text matching the regular expression replaced by the replacement string, and that an empty or custom replacement string can be used to reshape the result.

Why the other options are wrong:

A removes everything from + to the end of the string, which would leave user1 and lose @contoso.com.

B tries to extract a string that already excludes +..., but it does not reliably reconstruct the normalized address in this pattern.

D also removes everything after +, including the domain, which is incorrect.

So the normalized-email expression is:

REGEXP_REPLACE(JSON_VALUE(Payload, ' $.customer_email ' ), ' \+.*@ ' , ' @ ' )

Data API builder supports reading the database connection string from an environment variable by using the syntax:

@env( ' MSSQL_CONNECTION_STRING ' )

Microsoft’s DAB documentation explicitly shows that @env( ' MSSQL_CONNECTION_STRING ' ) tells Data API builder to read the connection string from an environment variable at runtime.

That fits this scenario because Azure Container Apps secrets are typically exposed to the container as environment variables . Microsoft’s Azure Container Apps documentation states that environment variables can reference secrets, and DAB’s Azure Container Apps deployment guidance shows a secret being mapped into an environment variable that DAB then reads.

Why the other options are wrong:

A and D incorrectly point the connection string to DAB_CONFIG_BASE64, which is the config payload secret, not the SQL connection string.

C uses secretref: syntax inside dab init, but DAB expects the connection string parameter in the config to use the environment-variable reference syntax @env(...). The secretref: pattern is for Azure Container Apps environment variable configuration, not for the DAB CLI connection-string argument itself.

So the correct command is:

dab init --database-type mssql --connection-string " @env( ' MSSQL_CONNECTION_STRING ' ) " --host-mode Production --config dab-config.json

Microsoft documents REGEXP_SUBSTR for Transact-SQL with the string_expression parameter as supporting character string types char, nchar, varchar, and nvarchar. For the regex functions, support for LOB types such as varchar(max) and nvarchar(max) is specifically called out for REGEXP_LIKE , REGEXP_COUNT , and REGEXP_INSTR up to 2 MB, but that support note is not listed for REGEXP_SUBSTR in the surfaced documentation. In exam terms, the safe and expected approach is to cast the nvarchar(max) column to nvarchar(4000) before calling REGEXP_SUBSTR.

This also fits the scenario detail that the ID is always contained within the first paragraph of MessageText. Since the needed value is near the start of the text, narrowing the input to a non-LOB string type such as nvarchar(4000) is sufficient and avoids incompatibility concerns with nvarchar(max).

The other options are not appropriate:

A STRING_ESCAPE(..., ' json ' ) is for JSON escaping, not regex extraction.

C adding a case-sensitive collation changes comparison behavior, but it is not the required fix for REGEXP_SUBSTR on nvarchar(max).

D TRY_CONVERT(varchar(max), ...) still leaves a MAX type and also risks unnecessary Unicode loss.

The two correct actions are D and E because the ingestion failures are caused by malformed JSON and duplicate payloads , and these two controls address those two problems directly. Microsoft’s JSON documentation states that SQL Server and Azure SQL support validating JSON with ISJSON , and Microsoft specifically recommends using a CHECK constraint to ensure JSON text stored in a column is properly formatted.

For the duplicate-payload issue, creating a unique index on a hash of the payload is the appropriate design. Microsoft documents using hashing functions such as HASHBYTES to hash column values, and SQL Server allows a deterministic computed column to be used as a key column in a UNIQUE constraint or unique index . That makes a persisted hash-based computed column plus a unique index a practical and exam-consistent way to reject duplicate payloads efficiently.

The other options do not solve the stated root causes:

Snapshot isolation addresses concurrency behavior, not malformed JSON or duplicate payload detection.

A trigger to rewrite malformed JSON is not the right integrity control and is brittle.

Foreign key constraints enforce referential integrity, not JSON validity or duplicate-payload prevention