1/180
Looks like no tags are added yet.
1. A REST endpoint backed by SQL should authenticate callers with tokens rather than shared passwords. Which option is preferred?
A. Shared API key
B. SQL authentication
C. OAuth token auth
D. Password in connection string
C. OAuth token auth
2. You need to call an HTTPS model endpoint directly from T-SQL as part of a grounded workflow. Which stored procedure should you use?
A. sp_invoke_external_rest_endpoint
B. REGEXP_LIKE
C. Query Store
D. JSON_ARRAYAGG
A. sp_invoke_external_rest_endpoint
3. You need a centralized workspace to query logs across the API host, Azure SQL diagnostics, and other Azure resources. Which service should you use?
A. Application Insights
B. JSON_ARRAY
C. Log Analytics
D. Memory-optimized table
C. Log Analytics
4. Users ask semantically similar questions that might not share exact keywords with the documents. Which search approach is best?
A. Dynamic Data Masking
B. Vector search
C. Full-text search
D. CHECK constraint
B. Vector search
5. You need to query parquet files in external storage without first importing them into a local SQL table. Which object should you create?
A. Ledger table
B. Graph tables
C. External table
D. Temporal table
C. External table
6. You need a quick visual way in Azure SQL to identify top resource-consuming queries. Which feature should you use?
A. Query Store
B. Query Performance Insight
C. Table partitioning
D. AFTER trigger
B. Query Performance Insight
7. A JSON array of line items must be expanded into relational rows for joining and filtering. Which function is most appropriate?
A. JSON_OBJECT
B. JSON_CONTAINS
C. OPENJSON
D. JSON_VALUE
C. OPENJSON
8. A retrieval design must support both exact keyword constraints and semantic ranking. Which search option should you recommend?
A. Full-text search
B. Hybrid search
C. Database auditing
D. Vector search
B. Hybrid search
9. You want Copilot Chat to use the current database schema from tools rather than relying on manually copied table definitions in the prompt. What should you configure?
A. Copilot instruction file
B. MCP server
C. Managed Identity
D. Query Store
B. MCP server
10. An analyst should be able to select from a curated view but should not be able to execute update procedures. Which approach fits best?
A. Object-level permissions
B. db_datareader
C. Dynamic Data Masking
D. Least privilege
A. Object-level permissions
11. A developer proposes skipping retrieval and sending the user's question straight to the model, even though the answer must be based on SQL data. What is the main drawback?
A. It always improves grounding
B. It adds more retrieval evidence
C. It enforces Row-Level Security automatically
D. Ungrounded generation can hallucinate
D. Ungrounded generation can hallucinate
12. A design review asks what should happen after source text changes so that retrieval remains aligned with the latest content. What is the correct answer?
A. Switch to SQL authentication
B. Mask the text with DDM
C. Regenerate embeddings for changed content
D. Only rebuild indexes yearly
C. Regenerate embeddings for changed content
13. A client repeatedly requests large result sets from a DAB endpoint, and you want to protect the API and database from oversized responses. Which capability should you emphasize?
A. Database auditing
B. Always Encrypted
C. Graph tables
D. Pagination
D. Pagination
14. You need to count how many times a delimiter-like pattern appears in free-form text. Which function should you use?
A. REGEXP_COUNT
B. REGEXP_INSTR
C. REGEXP_LIKE
D. REGEXP_REPLACE
A. REGEXP_COUNT
15. A table needs a column that uniquely identifies each row. Which constraint should you apply first?
A. UNIQUE constraint
B. PRIMARY KEY
C. CHECK constraint
D. DEFAULT constraint
B. PRIMARY KEY
16. Which column type is usually a poor choice to include directly in an embedding because it has little semantic meaning?
A. View
B. Semantic columns
C. JSON column
D. Surrogate keys
D. Surrogate keys
17. Your team wants Copilot to consistently generate T-SQL that uses your naming standards and deployment conventions across the repository. What should you add?
A. Copilot instruction file
B. Application Insights
C. MCP server
D. Managed Identity
A. Copilot instruction file
18. You need a record of who changed schema or data and when, for compliance review and investigations. Which capability should you enable?
A. Columnstore index
B. Dynamic Data Masking
C. JSON index
D. Database auditing
D. Database auditing
19. After a model endpoint returns a JSON response to your T-SQL procedure, what should the solution do next to use the answer in the application?
A. Rebuild all indexes
B. Extract the model response
C. Change the isolation level
D. Create a SEQUENCE
B. Extract the model response
20. You need to ensure that each order row always references an existing customer row. Which database constraint should you implement?
A. DEFAULT constraint
B. UNIQUE constraint
C. FOREIGN KEY
D. CHECK constraint
C. FOREIGN KEY
21. You want to register an external model inference endpoint so it can be referenced from SQL. Which statement should you use?
A. CREATE TRIGGER
B. CREATE EXTERNAL MODEL
C. CREATE VIEW
D. CREATE SEQUENCE
B. CREATE EXTERNAL MODEL
22. A lookup table of approved countries must be versioned together with the schema so deployments are repeatable. What should you place in source control?
A. Row-Level Security
B. Reference/static data scripts
C. Dynamic Data Masking
D. Query Store
B. Reference/static data scripts
23. You want every developer on the team to get the same Copilot guidance automatically when they open the repository. Which approach is best?
A. Copilot instruction file
B. Manual review
C. Personal chat prompt
D. Ad hoc email guidance
A. Copilot instruction file
24. You want to present a simplified, reusable SELECT statement to analysts without duplicating the query text in every report. Which object should you create?
A. Scalar function
B. Stored procedure
C. AFTER trigger
D. View
D. View
25. A Fabric user wants built-in AI help while working directly in Microsoft Fabric experiences. Which capability should be enabled?
A. GitHub Copilot
B. Managed Identity
C. Data API builder
D. Copilot in Fabric
D. Copilot in Fabric
26. You need to standardize phone numbers by removing punctuation characters from text. Which regular-expression function is the best fit?
A. REGEXP_SUBSTR
B. REGEXP_REPLACE
C. REGEXP_LIKE
D. REGEXP_COUNT
B. REGEXP_REPLACE
27. You need to expose tables and views as REST and GraphQL endpoints without writing a custom API service. Which product should you use?
A. Data API builder
B. SQL Database Project
C. GitHub Copilot
D. Database auditing
A. Data API builder
28. Your AI workflow must return JSON that downstream SQL logic can parse reliably. Which model characteristic is most important?
A. Multilingual model
B. Structured output model
C. Full-text search
D. Multimodal model
B. Structured output model
29. Which function calculates the distance between two vectors using a specified metric?
A. VECTORPROPERTY
B. VECTOR_DISTANCE
C. VECTOR_SEARCH
D. VECTOR_NORMALIZE
B. VECTOR_DISTANCE
30. A team wants designated database reviewers to be required automatically when objects in a protected folder change. Which file helps enforce this?
A. Pull request
B. GitHub Actions secrets
C. CODEOWNERS
D. DAB configuration file
C. CODEOWNERS
31. You need a reusable object that exposes only selected columns from a base table while hiding internal columns from client queries. Which object is most appropriate?
A. Scalar function
B. Stored procedure
C. INSTEAD OF trigger
D. View
D. View
32. You want a resource-oriented API pattern with conventional HTTP verbs over SQL-backed entities. Which endpoint style should you use?
A. GraphQL endpoint
B. Query Store
C. REST endpoint
D. JSON index
C. REST endpoint
33. Each sales representative should see only the orders for their own territory from a shared table. Which feature is the best fit?
A. Dynamic Data Masking
B. Row-Level Security
C. Query Store
D. Column-level encryption
B. Row-Level Security
34. You want to reduce the risk associated with long-lived database passwords embedded in app settings. Which strategy is best?
A. Shared API key
B. Password in connection string
C. SQL authentication
D. Managed Identity
D. Managed Identity
35. Two concurrent workloads must avoid dirty reads, but the team also wants to reduce reader blocking where possible. Which isolation-level option is the best compromise among the listed choices?
A. No vector index
B. READ COMMITTED SNAPSHOT
C. READ UNCOMMITTED
D. SERIALIZABLE
B. READ COMMITTED SNAPSHOT
36. Which function returns specific properties of a vector rather than distance or search results?
A. ROW_NUMBER()
B. VECTOR_SEARCH
C. VECTOR_DISTANCE
D. VECTORPROPERTY
D. VECTORPROPERTY
37. You must return an employee hierarchy from a self-referencing table. Which T-SQL construct is most appropriate?
A. Recursive CTE
B. OPENJSON
C. TRY...CATCH
D. ROW_NUMBER()
A. Recursive CTE
38. A data-quality rule must run automatically after each INSERT or UPDATE on a table and reject the statement when the rule fails. Which object should you implement?
A. Scalar function
B. AFTER trigger
C. Table-valued function
D. View
B. AFTER trigger
39. You want to catch invalid object definitions before any deployment reaches a shared environment. What should the pipeline do?
A. Only review production logs
B. Build and validate the SQL Database Project
C. Run Query Performance Insight
D. Skip validation to speed deployment
B. Build and validate the SQL Database Project
40. A deployment pipeline needs a secure place to store connection information and publish credentials. Which option is best?
A. Query Store
B. Reference/static data scripts
C. Columnstore index
D. GitHub Actions secrets
D. GitHub Actions secrets
41. You need to traverse relationships in graph tables by expressing a pattern between nodes and edges. Which operator should you use?
A. OPENJSON
B. ROW_NUMBER()
C. MATCH
D. REGEXP_LIKE
C. MATCH
42. A reporting user should query a view but should not be able to read the base tables directly. What should you apply?
A. Object-level permissions
B. db_datareader
C. Always Encrypted
D. Managed Identity
A. Object-level permissions
43. Readers are being blocked by writers under the default read committed behavior, and you want to reduce that blocking for read workloads. Which option is best?
A. Dynamic Data Masking
B. SERIALIZABLE
C. READ COMMITTED SNAPSHOT
D. READ UNCOMMITTED
C. READ COMMITTED SNAPSHOT
44. A high-volume system wants to refresh embeddings asynchronously after data changes without adding latency to the write transaction. Which option is a better fit than a table trigger?
A. Azure Functions
B. Scalar function
C. Table trigger
D. Dynamic Data Masking
A. Azure Functions
45. A team wants Copilot to answer questions using objects in a Fabric lakehouse through tools instead of static pasted context. What should the chat session connect to?
A. Copilot instruction file
B. GitHub Actions secrets
C. Columnstore index
D. MCP server
D. MCP server
46. A finance team must be able to query previous versions of account records as they existed at the end of any month. Which database object should you implement?
A. Temporal table
B. Graph tables
C. Ledger table
D. External table
A. Temporal table
47. A pipeline currently stores credentials in a YAML file committed to the repository. What is the best improvement?
A. Leave the YAML file as is
B. Store secrets in README
C. db_datareader
D. GitHub Actions secrets
D. GitHub Actions secrets
48. A front-end application needs a database endpoint that accepts parameters, performs validation, and updates data. Which object should you expose?
A. View
B. Table-valued function
C. AFTER trigger
D. Stored procedure
D. Stored procedure
49. Queries frequently filter on a property inside a JSON document, and performance has become a problem. What should you add?
A. Clustered index
B. JSON index
C. Columnstore index
D. FOREIGN KEY
B. JSON index
50. A CreatedDate column should automatically receive the current date when an insert doesn't supply a value. Which constraint should you use?
A. UNIQUE constraint
B. DEFAULT constraint
C. FOREIGN KEY
D. CHECK constraint
B. DEFAULT constraint
51. A multitenant application stores all tenants in one table. You need a database-side control that filters rows even if developers forget a WHERE clause. Which feature should you use?
A. Feature branch
B. Dynamic Data Masking
C. Row-Level Security
D. Column-level encryption
C. Row-Level Security
52. You want a fuzzy similarity score that is directly based on edit distance rather than just the raw count of edits. Which function should you use?
A. REGEXP_LIKE
B. EDIT_DISTANCE_SIMILARITY
C. EDIT_DISTANCE
D. JARO_WINKLER_DISTANCE
B. EDIT_DISTANCE_SIMILARITY
53. A query must return one JSON array containing all product names from the matching rows. Which function is the best fit?
A. JSON_VALUE
B. JSON_ARRAY
C. JSON_OBJECT
D. JSON_ARRAYAGG
D. JSON_ARRAYAGG
54. A lookup query filters on a selective OrderNumber column and returns very few rows. Which design is most likely to help?
A. Nonclustered index
B. Table partitioning
C. Graph tables
D. Columnstore index
A. Nonclustered index
55. You need to compare each row's revenue with the previous month's revenue without using a self-join. Which window function should you choose?
A. RANK()
B. LAG()
C. ROW_NUMBER()
D. JSON_ARRAYAGG
B. LAG()
56. An application must execute a multi-step operation that inserts an order, inserts line items, and returns a status code. Which object is the best choice?
A. View
B. Stored procedure
C. Scalar function
D. AFTER trigger
B. Stored procedure
57. You want to combine a full-text ranking list and a vector ranking list into one blended ranking. Which technique should you use?
A. Query Store
B. CHECK constraint
C. JSON_CONTAINS
D. RRF
D. RRF
58. A lightweight synchronization process only needs to know which rows changed, not the full before/after row images. Which feature is most suitable?
A. Column-level encryption
B. Change Data Capture
C. Change Tracking
D. Change event streaming
C. Change Tracking
59. A developer suggests READ UNCOMMITTED to speed up a financial reconciliation query. What is the main trade-off?
A. Query plans are persisted
B. Data is automatically encrypted
C. Dirty reads are possible
D. Phantom reads are eliminated
C. Dirty reads are possible
60. You want to identify currently expensive requests and waits while an incident is still happening, rather than only looking at historical summaries. Which source is best?
A. DMVs
B. Query Store
C. Temporal table
D. Database auditing
A. DMVs
1. Your team builds a multilingual support application and wants an AI assistant model choice that can reason well across several languages. Which model capability matters most?
A. Multimodal model
B. Multilingual model
C. Columnstore index
D. Structured output model
B. Multilingual model
2. A slow statement needs detailed operator-level analysis to see scans, seeks, joins, and estimated costs. What should you inspect?
A. Execution plan
B. Row-Level Security
C. Database auditing
D. Copilot instruction file
A. Execution plan
3. Developers need isolation while they change database objects before merging to main. Which Git practice is most appropriate?
A. Database auditing
B. Environment approval
C. Full-text search
D. Feature branch
D. Feature branch
4. You need to know whether a slow query is performing a table scan when you expected an index seek. Which artifact should you review first?
A. Query Performance Insight
B. Feature branch
C. Database auditing
D. Execution plan
D. Execution plan
5. A subquery must compare each employee's salary to the average salary of that employee's own department by referencing the outer row. Which construct is this?
A. JSON_OBJECT
B. REGEXP_COUNT
C. Correlated subquery
D. Recursive CTE
C. Correlated subquery
6. You need the fastest scalable search over a very large embedding corpus and can accept a small trade-off in exactness. Which approach should you favor?
A. READ UNCOMMITTED
B. ANN
C. ENN
D. Temporal table
B. ANN
7. You need to return a result set from a reusable, parameterized object, but the caller also needs to compose additional filtering on top of that result in the same query. Which object is best?
A. Table-valued function
B. Scalar function
C. Stored procedure
D. AFTER trigger
A. Table-valued function
8. A pipeline should verify stored procedure behavior with isolated tests that run quickly on every commit. Which test type is the best fit?
A. Unit tests
B. Integration tests
C. Environment approval
D. Database auditing
A. Unit tests
9. A developer wants Copilot to suggest SQL that aligns with your branching, testing, and naming conventions. Which configuration is the most direct way to shape those suggestions?
A. Change Tracking
B. Application Insights
C. Copilot instruction file
D. Dynamic Data Masking
C. Copilot instruction file
10. You want serverless code to react when rows change in Azure SQL and then call another service. Which option should you use?
A. Change Tracking
B. Azure Logic Apps
C. GraphQL endpoint
D. Azure Functions SQL trigger
D. Azure Functions SQL trigger
11. A security review asks how to reduce the blast radius if a service credential is compromised. Which approach helps most?
A. db_datareader
B. Least privilege
C. Columnstore index
D. READ UNCOMMITTED
B. Least privilege
12. A CountryCode column always stores exactly two characters such as US or FR. Which data type is the most efficient design choice?
A. JSON column
B. BIGINT
C. CHAR(2)
D. NVARCHAR(4000)
C. CHAR(2)
13. A solution retrieves the top relevant chunks from SQL and injects them into a prompt before calling the model. Which RAG component do those chunks represent?
A. Column-level encryption
B. Retrieved context
C. Vector index
D. Branch policy
B. Retrieved context
14. A stored procedure retrieves structured rows that must be sent to a language model in a model-friendly format. What should you do next?
A. Create a clustered index
B. Enable Change Tracking
C. Convert the results to JSON payload
D. Apply Dynamic Data Masking
C. Convert the results to JSON payload
15. Which function returns the closest vectors using an approximate nearest neighbor search algorithm?
A. JSON_ARRAYAGG
B. VECTOR_DISTANCE
C. VECTORPROPERTY
D. VECTOR_SEARCH
D. VECTOR_SEARCH
16. You need a reusable database object that accepts parameters and returns a single tax amount for an input value. Which object is the best fit?
A. Table-valued function
B. View
C. Scalar function
D. Stored procedure
C. Scalar function
17. What object stores the location, authentication method, and purpose of an external AI model endpoint after it is registered?
A. DAB configuration file
B. External model
C. Feature branch
D. Columnstore index
B. External model
18. You need to build a JSON object from several SQL column values in a SELECT statement. Which function should you use?
A. JSON_VALUE
B. JSON_ARRAY
C. OPENJSON
D. JSON_OBJECT
D. JSON_OBJECT
19. A column contains national ID values that must remain encrypted so the database engine can't view plaintext during processing. Which feature should you choose?
A. Always Encrypted
B. Dynamic Data Masking
C. Column-level encryption
D. Database auditing
A. Always Encrypted
20. You want to combine lexical keyword matching with semantic similarity in the same retrieval experience. Which search strategy should you use?
A. Hybrid search
B. Database auditing
C. Vector search
D. Full-text search
A. Hybrid search
21. A compliance team needs to prove that invoice records and their history haven't been tampered with after posting. Which table type is the best choice?
A. External table
B. Ledger table
C. Temporal table
D. Memory-optimized table
B. Ledger table
22. A large transaction table is managed by month, and the operations team wants to archive and maintain one month at a time. Which design feature best supports this?
A. JSON column
B. Memory-optimized table
C. Columnstore index
D. Table partitioning
D. Table partitioning
23. A service must be able to execute one stored procedure but should not receive broad read access to the whole database. What should you use?
A. Object-level permissions
B. READ UNCOMMITTED
C. Dynamic Data Masking
D. db_datareader
A. Object-level permissions
24. You want GitHub Copilot Chat to understand your live SQL schema and tools instead of relying only on the prompt text. What should you connect?
A. Copilot instruction file
B. MCP server
C. Dynamic Data Masking
D. Application Insights
B. MCP server
25. A GraphQL client must traverse from an Order entity to related OrderLine entities in the same query. What should you configure in DAB?
A. Branch policy
B. READ UNCOMMITTED
C. GraphQL relationship
D. JSON column
C. GraphQL relationship
26. You need to assign a sequence number to orders within each customer, ordered by order date. Which function should you use?
A. ROW_NUMBER()
B. JSON_VALUE
C. RANK()
D. LAG()
A. ROW_NUMBER()
27. A release process requires a reviewer to compare source-controlled schema with the target database before publishing. Which capability best supports that check?
A. Schema drift detection
B. Application Insights
C. Dynamic Data Masking
D. Integration tests
A. Schema drift detection
28. A user should see all rows in a table, but sensitive columns should appear obfuscated in query results. Which feature meets that requirement?
A. Row-Level Security
B. Always Encrypted
C. Dynamic Data Masking
D. Object-level permissions
C. Dynamic Data Masking
29. Which control is best when the requirement is "users can query the table, but unauthorized users must not see certain rows at all"?
A. Row-Level Security
B. Database auditing
C. Dynamic Data Masking
D. Column-level encryption
A. Row-Level Security
30. A report query must join to a parameterized object that returns all open tickets for a supplied support queue. Which object should you create?
A. AFTER trigger
B. Table-valued function
C. Stored procedure
D. Scalar function
B. Table-valued function
31. A chunking strategy keeps losing context at paragraph boundaries, causing retrieval quality issues. What design change is most likely to help?
A. Remove metadata entirely
B. READ UNCOMMITTED
C. Smaller chunk size
D. Chunk overlap
D. Chunk overlap
32. A parsing routine must return all matches of a regex pattern from a text column. Which function is the best fit?
A. REGEXP_MATCHES
B. REGEXP_COUNT
C. REGEXP_SUBSTR
D. JSON_ARRAYAGG
A. REGEXP_MATCHES
33. A transaction must prevent phantom rows and provide the strictest standard isolation guarantees. Which isolation level should you use?
A. SERIALIZABLE
B. READ UNCOMMITTED
C. SNAPSHOT
D. READ COMMITTED SNAPSHOT
A. SERIALIZABLE
34. Before publishing changes, the team wants to know whether someone modified the target database outside the project definition. Which capability should they use?
A. JSON_ARRAYAGG
B. Schema drift detection
C. Graph tables
D. Dynamic Data Masking
B. Schema drift detection
35. You need to extract the first order number that matches a pattern from a larger text field. Which function should you use?
A. REGEXP_COUNT
B. REGEXP_LIKE
C. REGEXP_INSTR
D. REGEXP_SUBSTR
D. REGEXP_SUBSTR
36. A developer working in source control and code editor workflows wants inline code suggestions and chat assistance while writing T-SQL. Which tool best fits?
A. Ledger table
B. GitHub Copilot
C. Application Insights
D. Copilot in Fabric
B. GitHub Copilot
37. Which feature is best for preserving a record of plan choices and runtime statistics after the query has already completed?
A. READ UNCOMMITTED
B. DMVs
C. Query Store
D. INSTEAD OF trigger
C. Query Store
38. In a SQL-based RAG workflow, why is it useful to serialize tabular query results to JSON before sending them to the model?
A. JSON makes Row-Level Security unnecessary
B. JSON automatically encrypts the data
C. JSON replaces embeddings
D. JSON is easier for the model to consume than raw tabular rows
D. JSON is easier for the model to consume than raw tabular rows
39. You need an embedding maintenance option that identifies changed rows with relatively low overhead and then refreshes embeddings in a separate process. Which database feature is a good fit?
A. ROW_NUMBER()
B. Table trigger
C. Always Encrypted
D. Change Tracking
D. Change Tracking
40. A team wants a managed AI workflow outside the database engine to help maintain embeddings at scale. Which option from the study guide best fits?
A. Table trigger
B. Microsoft Foundry
C. RANK()
D. CHECK constraint
B. Microsoft Foundry