DP-800 Study Guide: Developing AI-Enabled Database Solutions

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

1/53

flashcard set

Earn XP

Description and Tags

Comprehensive vocabulary flashcards covering the core concepts, syntax, specialized tables, security, CI/CD, and vector AI patterns for the DP-800 certification exam.

54 Terms

1
New cards

DP-800 Certification Credential

Microsoft Certified: SQL AI Developer Associate (requires a passing score of 700 or higher).

2
New cards

Right-Sizing Data Types

The practice of selecting the smallest data type that accommodates the data (e.g., INT vs BIGINT, VARCHAR(n) vs MAX, DATE vs DATETIME2) to maximize rows per data page and reduce I/O.

3
New cards

Clustered Index

An index structure that defines the physical storage order of the data in a table; only one is permitted per table.

4
New cards

Nonclustered Index

A separate index structure containing key columns that points back to the base table row; multiple nonclustered indexes are permitted per table.

5
New cards

Covering Index

An index that contains every column required by a query, often utilizing included columns at the leaf level to eliminate key lookups.

6
New cards

Filtered Index

A nonclustered index defined with a WHERE clause that indexes only a specified subset of table rows.

7
New cards

Clustered Columnstore Index (CCI)

An index type where the entire table is stored in a column-oriented, compressed format; optimized for batch mode analytics and large scans on fact tables.

8
New cards

Nonclustered Columnstore Index (NCCI)

A columnstore index built on top of an underlying rowstore table to enable real-time operational analytics on transactional data.

9
New cards

Delta Store

A rowstore holding newly inserted rows until they reach sufficient size to be compressed into a columnstore rowgroup.

10
New cards

Memory-Optimized Table

An In-Memory OLTP table where rows reside in memory with lock-free, latch-free access, designed for high-throughput and low-latency workloads.

11
New cards

DURABILITY = SCHEMA_AND_DATA

A memory-optimized table setting indicating that both table schema and data are persisted to disk and survive a server restart.

12
New cards

DURABILITY = SCHEMA_ONLY

A memory-optimized table setting where only the table schema survives a database restart while data is cleared; ideal for staging and temporary workloads.

13
New cards

Temporal Table

A system-versioned table that automatically tracks the full history of row modifications in an associated history table using period start and end DATETIME2 columns.

14
New cards

External Table

A database object providing metadata that references data located outside the database (via data virtualization or elastic query), enabling it to be queried like a local table.

15
New cards

Ledger Table

A tamper-evident table that provides cryptographic verification of data modifications using cryptographic hash chains.

16
New cards

Append-Only Ledger Table

A ledger table variant that allows only INSERT operations, completely blocking UPDATE and DELETE actions.

17
New cards

Ledger Digest

A cryptographic hash representing the state of the database ledger at a specific point in time, stored outside the database (e.g., in immutable storage) to verify integrity.

18
New cards

Graph Table

Tables declared AS NODE or AS EDGE to represent and query many-to-many relationships natively using pseudo-columns ($node_id, $edge_id, $from_id, $to_id).

19
New cards

SHORTEST_PATH

A T-SQL graph function evaluated inside a MATCH clause to execute shortest or arbitrary-length path traversals across nodes and edges.

20
New cards

JSON_VALUE

A scalar T-SQL function that extracts a single scalar primitive value from a JSON string at a designated path.

21
New cards

JSON_QUERY

A T-SQL function that extracts an object or an array from a JSON string.

22
New cards

OPENJSON

A table-valued function that parses JSON text and returns objects and properties as rows and columns, optionally typed using a WITH clause.

23
New cards

SEQUENCE

A user-defined, schema-bound object that generates sequential numeric values independently of any specific table and is accessed using NEXT VALUE FOR.

24
New cards

Partition Switching

A metadata-only DDL operation used to quickly move an entire partition of data into or out of a partitioned table.

25
New cards

Inline Table-Valued Function (iTVF)

A user-defined function containing a single SELECT statement returning a table, which the query optimizer expands directly into the calling query like a parameterized view.

26
New cards

Multi-Statement TVF

A user-defined function that defines and populates a table variable across multiple statements, which historically produces less accurate cardinality estimates.

27
New cards

Window Frame

A sub-clause of the OVER clause (using ROWS or RANGE BETWEEN) specifying the bounded subset of rows within a partition used to compute running totals and moving averages.

28
New cards

EDIT_DISTANCE

A fuzzy string matching function that calculates the number of single-character insertions, deletions, or substitutions required to transform one string into another.

29
New cards

JARO_WINKLER_DISTANCE

A string distance metric giving higher weight to strings sharing a common prefix; lower values signify closer similarity.

30
New cards

SET XACT_ABORT ON

A T-SQL session setting instructing the engine to automatically roll back the entire transaction when any runtime error occurs.

31
New cards

Model Context Protocol (MCP)

An open standard protocol that enables AI assistants to discover, invoke, and interact with external data sources and execution tools consistently.

32
New cards

Always Encrypted

A client-side data encryption technology where sensitive data is encrypted before leaving the client application, ensuring database administrators and server processes cannot view plaintext or keys.

33
New cards

Deterministic Encryption

An encryption mode that generates identical ciphertext for identical plaintext values, enabling equality comparisons, grouping, and joins at the risk of pattern leakage.

34
New cards

Randomized Encryption

An encryption mode where identical plaintext produces different ciphertext outputs, providing enhanced cryptographic protection while disallowing search and join operations.

35
New cards

Secure Enclaves

A protected hardware memory region that enables the database engine to perform pattern matching, sorting, and range queries on Always Encrypted data in place.

36
New cards

Dynamic Data Masking (DDM)

A presentation-layer security mechanism that obfuscates sensitive column data in result sets returned to non-privileged users without modifying the stored data on disk.

37
New cards

Row-Level Security (RLS)

A security feature that restricts row access based on execution context using an inline table-valued security predicate function bound via a security policy.

38
New cards

Ownership Chaining

A SQL Server permission behavior where access checks on underlying objects are bypassed if the calling object and the referenced objects share the same owner.

39
New cards

Data API Builder (DAB)

An open-source engine that exposes relational database entities via REST, GraphQL, and MCP endpoints based on a JSON configuration file without custom code.

40
New cards

READ_COMMITTED_SNAPSHOT (RCSI)

A database-level option that uses statement-level row versioning under the READ COMMITTED isolation level so that readers do not block writers; enabled by default in Azure SQL Database.

41
New cards

Query Store

A database feature that continuously captures query history, runtime execution statistics, and execution plans to facilitate troubleshooting and plan forcing.

42
New cards

Deadlock

A concurrency condition where two or more sessions hold locks that the other sessions require, forming a circular dependency that forces the database engine to terminate one session as a victim.

43
New cards

SqlPackage

A cross-platform command-line utility used to build, publish, extract, and report drift on data-tier application packages (.dacpac files).

44
New cards

Schema Drift

A discrepancy between the deployed live database schema and the source-controlled database project definition.

45
New cards

Change Tracking

A lightweight database mechanism that records only which rows were inserted, updated, or deleted along with version metadata, without capturing historical values.

46
New cards

Change Data Capture (CDC)

An asynchronous transaction-log-based feature that records full historical row change data, including before and after values, into dedicated capture tables.

47
New cards

Embedding

A dense numerical vector representing the semantic meaning of text or other data, arranged such that semantically similar items reside close to one another in vector space.

48
New cards

Chunking

The technique of dividing large source texts into smaller, bounded segments (often with overlap) to fit model token limits and optimize vector retrieval accuracy.

49
New cards

VECTOR_DISTANCE

A T-SQL function that calculates the mathematical distance between two vectors using distance metrics such as cosine, euclidean, or dot product.

50
New cards

Approximate Nearest Neighbor (ANN)

An index-accelerated vector search method that rapidly identifies matches close to the query vector by trading a minor degree of recall for superior query performance on large datasets.

51
New cards

Exact Nearest Neighbor (ENN)

An exhaustive vector search strategy that calculates the exact distance across every vector in a table, providing 100% recall at the cost of linear computational scaling.

52
New cards

Reciprocal Rank Fusion (RRF)

A rank-based aggregation algorithm used in hybrid search to combine full-text and vector ranking lists without score calibration, computing scores via ∑1k+rank\sum \frac{1}{k + \text{rank}}, where kk is commonly 6060.

53
New cards

Retrieval-Augmented Generation (RAG)

An AI architecture pattern that retrieves domain-specific database records, appends them into a model prompt as context, and prompts a large language model to produce a grounded response.

54
New cards

sp_invoke_external_rest_endpoint

A system stored procedure that issues HTTPS REST requests directly from T-SQL to external endpoints (such as Azure OpenAI) using a database scoped credential for authentication.