SQL AI Developer Associate (DP-800) - Full Practice Exam 1

1. A business needs supported querying of how a row looked at earlier times as the current row changes. Which table capability should be evaluated?
2. A client must reject an update based on an old row version rather than silently overwrite a newer change. Which approach fits?
3. A column has DEFAULT 0. A client explicitly inserts NULL into the nullable column. What should the developer understand?
4. A corpus contains exact product terms and semantically related descriptions. Which search design should be considered?
5. A database integration calls an external model endpoint. Where should any required secret be managed when keyless access is unavailable?
6. A database represents people as nodes and relationships as edges and needs supported graph-pattern queries. Which T-SQL capability is relevant?
7. A deployment moves vector search between SQL platforms or update policies. What should be checked before reusing its indexes and queries?
8. A deployment plan proposes dropping a populated column. Which two checks are necessary before applying it? Select TWO.
9. A design uses a SQL sequence and requires permanently gap-free business numbers even after rollbacks. What should be explained?
10. A developer calls VECTOR_DISTANCE and expects it to use an ANN index automatically. What should be clarified?
11. A developer defines a CTE before a SELECT and expects it to remain available to unrelated later statements. What should be clarified?
12. A downstream integration needs database-change information. What should guide choosing CDC, Change Tracking, or another supported mechanism?
13. A JSON column contains {"customer":{"name":"Ava"}}. A query needs the scalar name value. Which function should be evaluated?
14. A JSON document contains an array of line items that must be exposed as rows for a relational query. Which function fits?
15. A large analytical table is scanned for aggregates over a subset of columns. Which index family should be evaluated alongside workload update requirements?
16. A large vector dataset needs lower query latency and can accept a measured recall tradeoff. Which approach should be evaluated?
17. A model returns JSON intended to update business records. What should happen before the application persists it?
18. A plan badly underestimates rows after a large data-distribution change. Which evidence should be reviewed?
19. A predicate wraps an indexed column in a function and a query scans many rows. What should be evaluated?
20. A procedure builds SQL by concatenating a user-supplied search string. What should the developer use for values instead?
21. A procedure catches an error inside a transaction. What should its error path consider before returning success or continuing writes?
22. A procedure change needs validation against real database behavior without risking production data. Which approach fits?
23. A query assigns ROW_NUMBER() using an ORDER BY column that has ties. The application requires stable ordering between tied rows. What should be added?
24. A query explicitly uses READ UNCOMMITTED. Which behavior must the application be prepared for?
25. A query ranks rows using VECTOR_DISTANCE('cosine', Embedding, @QueryVector). Which order returns the most similar vectors first?
26. A query uses SUM(Amount) OVER (PARTITION BY CustomerId ORDER BY OrderDate, OrderId ROWS UNBOUNDED PRECEDING). What does this express?
27. A service needs supported REST and GraphQL access over selected database entities. Which capability should be evaluated?
28. A shared table contains several tenants' records. Which database capability can enforce supported predicate-based visibility?
29. A SQL-backed RAG application serves several tenants. Where must the caller's data boundary be enforced?
30. A SQL MCP endpoint is connected to a development assistant. What access design is appropriate?
31. A stored procedure regresses after a plan change. Which tool gives supported historical plans and runtime statistics?
32. A supported SQL platform calls a model endpoint using sp_invoke_external_rest_endpoint. What must the design validate?
33. A table needs one non-null unique identifier for each row. Which constraint directly expresses that identity?
34. A team masks a sensitive field and assumes no further permissions are needed. Which explanation is correct?
35. A team wants a versioned declarative model of its SQL schema with build-time validation. Which tooling should it evaluate?
36. A vector column is defined for a particular dimensionality. What must inserts and query vectors satisfy?
37. An AI assistant proposes a schema migration. What should the developer do before applying it to production?
38. An Amount column must never be NULL and must never be negative. Which two schema controls directly express these requirements? Select TWO.
39. An application needs tamper-evidence for supported database history. Which capability should be evaluated?
40. An application should execute one approved procedure but must not administer the database. Which permission design fits?
41. An Azure API must connect to SQL without storing a database password. Which supported design should be evaluated?
42. An engineer chooses which business fields to embed. Which principle is appropriate?
43. Every order line must reference an existing order. Which database constraint should be used?
44. Full-text and vector results have different score scales. Which supported ranking-fusion approach can combine their ranked lists without simply adding incomparable raw scores?
45. Long documents contain several unrelated sections. Which embedding design should be evaluated?
46. Production schema differs from the project's expected model. What should the release process do?
47. Selected columns should be decrypted only by authorized clients rather than exposed as plaintext to the ordinary database engine path. Which capability fits?
48. Source text changes after embeddings are generated. Which maintenance approach fits?
49. Stored document embeddings were produced by one model. Queries use an incompatible embedding model. What must be corrected?
50. Two transactions acquire resources in conflicting orders and one becomes a deadlock victim. Which mitigation should be evaluated?

Comments — 0