DP-800 All questions

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/180

encourage image

There's no tags or description

Looks like no tags are added yet.

181 Terms

1
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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
New cards

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

61
New cards

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

62
New cards

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

63
New cards

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

64
New cards

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

65
New cards

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

66
New cards

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

67
New cards

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

68
New cards

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

69
New cards

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

70
New cards

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

71
New cards

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

72
New cards

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)

73
New cards

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

74
New cards

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

75
New cards

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

76
New cards

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

77
New cards

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

78
New cards

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

79
New cards

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

80
New cards

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

81
New cards

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

82
New cards

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

83
New cards

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

84
New cards

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

85
New cards

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

86
New cards

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()

87
New cards

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

88
New cards

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

89
New cards

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

90
New cards

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

91
New cards

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

92
New cards

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

93
New cards

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

94
New cards

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

95
New cards

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

96
New cards

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

97
New cards

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

98
New cards

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

99
New cards

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

100
New cards

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