Q1.A retail company has built a SQL database on Microsoft Fabric, and within it there is a dbo.Products table that holds a product catalog.
This dbo.Products table stores the catalog data for each product.
You need to create a new stored procedure that performs a hybrid search.
The solution must meet the following requirements.
Retrieve the top 20 candidate products by using approximate nearest neighbor (ANN).
Re-rank only the candidates that also match the full-text query.
Generate the embedding for the query.
How should you complete the Transact-SQL code?
Show answer
For the first blank, which builds an embedding vector from the query string, AI_GENERATE_EMBEDDINGS is used.
The table-valued function that retrieves the top 20 results by approximate nearest neighbor is VECTOR_SEARCH, which matches the TABLE, COLUMN, SIMILAR_TO, TOP_N, and METRIC specifications.
Finally, to join only the rows matching the full-text condition with a RANK value, you use CONTAINSTABLE.
The SEMANTIC functions, VECTOR_DISTANCE, and FREETEXTTABLE do not fit these syntax requirements.
VECTOR_SEARCH (Transact-SQL) (Preview)
Q2.You have an Azure SQL database named SalesDB that runs on a logical server named sales-sql01.
There is an Azure App Service web app named OrderApi that currently connects to SalesDB by using SQL authentication.
You enable a user-assigned managed identity named OrderApi-Id for OrderApi.
You need to configure OrderApi to connect to SalesDB by using Microsoft Entra authentication.
The managed identity requires read and write permissions on SalesDB.
Which Transact-SQL statement should you run on SalesDB?
Show answer
A managed identity that connects to Azure SQL Database by using Microsoft Entra authentication is created as a user within the target database by running CREATE USER FROM EXTERNAL PROVIDER.
Because read and write permissions are required, you add it to the db_datareader and db_datawriter roles.
CREATE LOGIN creates a login on the logical server side, so it does not suit the requirement of granting permissions within SalesDB.
The options that specify a password or grant sysadmin also fail to meet the requirements.
Securely connect a .NET app to Azure SQL Database by using a managed identity – Azure App Service | Microsoft Learn
Q3.You have an Azure Container App named app1-contoso-001 that runs a Data API builder (DAB) container in front of an Azure SQL database.
You add an entity named Todo that references a source named dbo.todos.
When a client accesses the REST endpoint for Todo, which URL pattern should it use?
Show answer
In Data API builder, each database object is exposed as an entity in the configuration file, and the default path for a REST endpoint is /api/{entity}.
Here the entity name is Todo and no custom REST path is specified, so the client accesses /api/Todo.
/graphql is the path for GraphQL, and /data-api is not the default DAB REST path.
Therefore, B is correct.
How to invoke REST endpoints – Data API builder
Q4.You have a database named db1.
Its schema is managed in a Git repository as an SDK-style SQL database project.
The repository contains the following GitHub Actions workflow.
For each of the following statements, select [Yes] if the statement is true; otherwise, select [No].
| Statement | Yes | No | |
|---|---|---|---|
| Each time a change is pushed to the main branch, the unit tests run automatically. | |||
| Schema validation occurs during the Build step. | |||
| Schema validation occurs during the Deploy step. |
Show answer
The unit-tests job has the condition github.ref == ‘refs/heads/main’, so it runs after build-and-deploy when a push is made to main.
Building a SQL database project validates the relationships between objects and the syntax of the target platform, and produces a .dacpac.
The Deploy step, on the other hand, deploys the artifact by using SqlPackage Publish, so schema validation can be determined to occur in the Build step.
Therefore, statements 1 and 2 are “Yes”, and statement 3 is “No”.
Create and deploy a SQL project – SQL Server | Microsoft Learn
Q5.You have a database named DB1.
Its schema is stored in a Git repository as an SDK-style SQL database project.
A GitHub Actions workflow that runs dotnet build to produce the database artifact is already in place.
You need to add a deployment step that publishes the .dacpac file to an Azure SQL database by using a secret stored in the GitHub repository secrets.
What should you include in the workflow?
Show answer
To publish a .dacpac to Azure SQL Database, you use the GitHub Actions azure/sql-action@v2 and specify path and connection-string under with.
Because this is a publish operation, the action is set to publish, not extract.
Since the connection information is referenced from the GitHub repository secrets as required by the question, option C, which uses secrets.SQL_CONNECTION_STRING, is appropriate.
A writes the connection string directly under env, and D is a build rather than a publish, so both are inappropriate.
SQL Projects Automation – SQL Server | Microsoft Learn
Q6.You have an SDK-style SQL database project stored in a Git repository.
The project targets an Azure SQL database.
When the project references system objects, the CI build fails with unresolved reference errors.
You need to update the SQL database project so that the correct Azure SQL Database system objects are included in the database model and dotnet build validates successfully.
Solution: You add an artifact reference to the master.dacpac file for Azure SQL Database.
Does this meet the goal?
Show answer
To reliably resolve Azure SQL Database system objects in an SDK-style SQL project, you normally add Microsoft.SqlServer.Dacpacs.Azure.Master as a PackageReference.
An artifact reference depends on a local master.dacpac present on the build agent, and if it is not at the same path in the CI environment, dotnet build validation may fail.
Therefore, this solution alone does not meet the goal.
So the answer is “No”.
System objects in SQL projects – SQL Server | Microsoft Learn
Q7.You have an Azure SQL database named ProductsDB.
You deploy Data API builder (DAB) to Azure Container Apps by using the mcr.microsoft.com/azure-databases/data-api-builder:latest image.
The container app has the following configuration.
Secrets: mssql-connection-string, dab-config-base64.
Environment variables: MSSQL_CONNECTION_STRING=secretref:mssql-connection-string, DAB_CONFIG_BASE64=secretref:dab-config-base64.
Ingress: externally exposed on port 5000.
Users report that the /health endpoint returns a healthy response, but every query request to an entity named Products fails with a connection error.
You have confirmed that the SQL login in the connection string is correct and that the database exists.
You need to enable the container app to establish a connection to the Azure SQL logical server without changing the container app deployment settings or the DAB configuration file.
What should you do on the Azure SQL logical server?
Show answer
When /health is healthy but only entity queries fail with a connection error, you check the network reachability from the DAB container to Azure SQL.
To let an app inside Azure connect to Azure SQL Database, you create a firewall rule of 0.0.0.0 to 0.0.0.0 on the logical server to allow connections from Azure services.
This is a fix that does not change the container settings or the DAB configuration.
Auto-failover, DBCC, and automatic tuning do not resolve a connection-permission problem.
IP firewall rules – Azure SQL Database and Azure Synapse Analytics | Microsoft Learn
Q8.You have an Azure SQL database that contains a table named Sales.Orders.
Sales.Orders contains the columns for each order.
Reporting queries frequently repeat the logic that calculates the number of days elapsed since an order was placed.
You need to create a scalar user-defined function (UDF) that returns the number of days between the input value of @OrderDate and the current date and time.
How should you complete the Transact-SQL code?
To answer, drag the appropriate values to the correct targets.
Each value may be used once, more than once, or not at all.
| Column | Data type |
|---|---|
| OrderId | int |
| CustomerId | int |
| OrderDate | datetime2 |
| TotalAmount | decimal(18,2) |
Drag each option to the matching requirement’s drop zone, or tap to place it. Click a filled zone to send it back. (The same option may be used more than once.)
DATEADD(day, @OrderDate, GETDATE())
DATEDIFF(day, @OrderDate, GETDATE())
RETURNS INT
RETURNS TABLE
WITH SCHEMABINDING
Show answer
Because a scalar UDF returns a single value, the function declaration specifies RETURNS INT.
To calculate the number of days from the order date to the current date and time, DATEDIFF(day, @OrderDate, GETDATE()), which returns the difference, is appropriate rather than DATEADD, which adds to a date.
The return value of DATEDIFF is int, so it fits the design of assigning it to the declared @Days and returning it.
RETURNS TABLE is for table-valued functions and is not used in this question.
DATEDIFF (Transact-SQL) – SQL Server
Q9.Which is a common pattern used when integrating SQL with a large language model (LLM)?
Show answer
A common way to integrate SQL data with an LLM is a RAG pipeline (retrieval-augmented generation), which retrieves relevant information from a database or other source and includes the results in the prompt to generate an answer.
A configuration in which the LLM runs SQL directly is not common from a safety and control standpoint.
Because RAG uses the retrieved grounding data, it can improve the accuracy and freshness of the answer.
Manual CSV export and backup restore are not patterns for LLM integration.
RAG and generative AI – Azure AI Search | Microsoft Learn
Q10.You have an Azure SQL database named ToDo that contains a table named dbo.ToDo.
A company plans to develop an Azure Functions app that runs each time a row in dbo.ToDo changes.
The app uses an Azure SQL trigger binding to process INSERT, UPDATE, and DELETE events.
You need to configure ToDo to support the planned app.
What should you do?
Show answer
The Azure SQL trigger for Azure Functions uses SQL change tracking to monitor INSERT, UPDATE, and DELETE on a table.
Therefore, you must enable change tracking on both the target database ToDo and the target table dbo.ToDo.
Approaches that call an HTTP endpoint from CDC or from a DML/DDL trigger are not the prerequisite configuration for an Azure SQL trigger binding.
For the exam, it is important to remember that the Azure SQL trigger uses change tracking.
Azure SQL trigger for Functions