1/53
Comprehensive vocabulary flashcards covering the core concepts, syntax, specialized tables, security, CI/CD, and vector AI patterns for the DP-800 certification exam.
DP-800 Certification Credential
Microsoft Certified: SQL AI Developer Associate (requires a passing score of 700 or higher).
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.
Clustered Index
An index structure that defines the physical storage order of the data in a table; only one is permitted per table.
Nonclustered Index
A separate index structure containing key columns that points back to the base table row; multiple nonclustered indexes are permitted per table.
Covering Index
An index that contains every column required by a query, often utilizing included columns at the leaf level to eliminate key lookups.
Filtered Index
A nonclustered index defined with a WHERE clause that indexes only a specified subset of table rows.
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.
Nonclustered Columnstore Index (NCCI)
A columnstore index built on top of an underlying rowstore table to enable real-time operational analytics on transactional data.
Delta Store
A rowstore holding newly inserted rows until they reach sufficient size to be compressed into a columnstore rowgroup.
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.
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.
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.
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.
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.
Ledger Table
A tamper-evident table that provides cryptographic verification of data modifications using cryptographic hash chains.
Append-Only Ledger Table
A ledger table variant that allows only INSERT operations, completely blocking UPDATE and DELETE actions.
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.
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).
SHORTEST_PATH
A T-SQL graph function evaluated inside a MATCH clause to execute shortest or arbitrary-length path traversals across nodes and edges.
JSON_VALUE
A scalar T-SQL function that extracts a single scalar primitive value from a JSON string at a designated path.
JSON_QUERY
A T-SQL function that extracts an object or an array from a JSON string.
OPENJSON
A table-valued function that parses JSON text and returns objects and properties as rows and columns, optionally typed using a WITH clause.
SEQUENCE
A user-defined, schema-bound object that generates sequential numeric values independently of any specific table and is accessed using NEXT VALUE FOR.
Partition Switching
A metadata-only DDL operation used to quickly move an entire partition of data into or out of a partitioned table.
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.
Multi-Statement TVF
A user-defined function that defines and populates a table variable across multiple statements, which historically produces less accurate cardinality estimates.
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.
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.
JARO_WINKLER_DISTANCE
A string distance metric giving higher weight to strings sharing a common prefix; lower values signify closer similarity.
SET XACT_ABORT ON
A T-SQL session setting instructing the engine to automatically roll back the entire transaction when any runtime error occurs.
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.
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.
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.
Randomized Encryption
An encryption mode where identical plaintext produces different ciphertext outputs, providing enhanced cryptographic protection while disallowing search and join operations.
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.
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.
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.
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.
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.
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.
Query Store
A database feature that continuously captures query history, runtime execution statistics, and execution plans to facilitate troubleshooting and plan forcing.
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.
SqlPackage
A cross-platform command-line utility used to build, publish, extract, and report drift on data-tier application packages (.dacpac files).
Schema Drift
A discrepancy between the deployed live database schema and the source-controlled database project definition.
Change Tracking
A lightweight database mechanism that records only which rows were inserted, updated, or deleted along with version metadata, without capturing historical values.
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.
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.
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.
VECTOR_DISTANCE
A T-SQL function that calculates the mathematical distance between two vectors using distance metrics such as cosine, euclidean, or dot product.
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.
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.
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 ∑k+rank1, where k is commonly 60.
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.
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.