Implement AI Capabilities in Database Solutions

DP-800's AI domain is built around a simple idea the exam tests repeatedly: the SQL Database Engine can call out to AI models and generate embeddings natively in T-SQL, without an external orchestration layer.

Registering an external model

• `CREATE EXTERNAL MODEL model_name WITH (LOCATION = '...', API_FORMAT = 'Azure OpenAI', MODEL_TYPE = EMBEDDINGS, MODEL = 'text-embedding-3-small', CREDENTIAL = [...])` registers a callable AI endpoint as a first-class database object — `API_FORMAT` also accepts `OpenAI`, `Ollama`, and `ONNX Runtime`, covering both cloud-hosted and locally-run models.
• Authentication to the endpoint is handled by a `DATABASE SCOPED CREDENTIAL`, created with `IDENTITY = 'Managed Identity'` (no stored secret, the production-preferred pattern), `'HTTPEndpointHeaders'` (an API key sent as a header), or a couple of other identity types shared with `sp_invoke_external_rest_endpoint` — the credential name is then passed as `CREATE EXTERNAL MODEL`'s `CREDENTIAL` argument.
• `PARAMETERS` accepts a JSON string of runtime parameters appended to every request the model makes (for example `'{"dimensions": 1536}'` to fix an embedding's dimensionality, or a `sql_rest_options.retry_count` for automatic retry on transient failures) — these can also be overridden per call rather than fixed at registration time.
• Using an external model requires `EXECUTE` permission granted explicitly (`GRANT EXECUTE ON EXTERNAL MODEL::model_name TO [<principal>]`), separate from the broader `CREATE EXTERNAL MODEL`/`ALTER ANY EXTERNAL MODEL` permission needed to register or change one in the first place.

Generating embeddings inline

• `AI_GENERATE_EMBEDDINGS(source USE MODEL model_identifier [PARAMETERS json])` generates an embedding directly inside an ordinary `SELECT`, `INSERT`, or `UPDATE` statement — no separate application call or pipeline step is needed to turn text into a vector.
• A typical enrichment pattern is a single `UPDATE ... SET embedding_col = AI_GENERATE_EMBEDDINGS(text_col USE MODEL MyModel) FROM table`, and this requires the `external rest endpoint enabled` server configuration to be turned on (`EXECUTE sp_configure 'external rest endpoint enabled', 1; RECONFIGURE WITH OVERRIDE;`) — already enabled by default on Azure SQL Database and SQL database in Fabric, but off by default on SQL Server 2025 and Azure SQL Managed Instance until explicitly configured.

Chunking design

• `AI_GENERATE_CHUNKS(SOURCE = column_or_expression, CHUNK_TYPE = FIXED, CHUNK_SIZE = 100)` splits long text into fragments sized to fit an embedding model's context window, used with `CROSS APPLY` so each source row fans out into one row per chunk: `... CROSS APPLY AI_GENERATE_CHUNKS(SOURCE = d.content, CHUNK_TYPE = FIXED, CHUNK_SIZE = 100) AS c`.
• Chunk size is a genuine design trade-off, not just a technical limit to work around: smaller chunks give more precise retrieval (a matched chunk is more likely to be narrowly relevant) at the cost of losing surrounding context, while larger chunks preserve context at the cost of diluting a chunk's semantic focus — the right size depends on the source content's structure and how the chunks will be consumed downstream.
• Choosing *which columns* to embed matters as much as chunk size: embedding a free-text description column serves semantic search well, but embedding a rigid, low-cardinality column (a status code) wastes storage and compute without improving retrieval quality, since exact/relational filtering already handles that column better than vector similarity would.

Keeping embeddings fresh

• An embedding generated once goes stale the moment its source text changes, so a maintenance strategy has to re-run generation on updates — options mirror the change-handling mechanisms covered in the previous domain: a table trigger that recomputes the embedding synchronously on write, or an asynchronous pattern driven by Change Tracking, CDC, an Azure Functions SQL trigger binding, Azure Logic Apps, Change Event Streaming, or a scheduled Microsoft Foundry pipeline.
• The synchronous-trigger approach keeps embeddings always current at the cost of adding embedding-generation latency to every write; the asynchronous approaches decouple that latency from the write path at the cost of a window where the embedding is briefly stale relative to its source row — which trade-off is acceptable depends entirely on how quickly a change needs to be searchable.

Common confusion

• `CREATE EXTERNAL MODEL` and `AI_GENERATE_EMBEDDINGS` are two separate steps that are easy to conflate: `CREATE EXTERNAL MODEL` is a one-time registration of *where* the model lives and *how* to authenticate to it, while `AI_GENERATE_EMBEDDINGS` is the function called per-query or per-row that actually invokes it — you can't call `AI_GENERATE_EMBEDDINGS` against a model that hasn't been registered first, and registering a model does nothing on its own until something calls it.

Native vector support is the foundational AI feature the rest of this domain builds on — the exam expects precise recall of the type's syntax and limits, not just conceptual familiarity with "storing embeddings."

The VECTOR data type

• Column syntax is `column_name VECTOR(dimensions [, base_type])` — the default base type is `float32`; specifying `float16` explicitly stores each element at half precision, trading some numeric fidelity for roughly half the storage footprint.
• A vector must have at least one dimension, and the maximum supported is 1998 dimensions — a model that emits a higher-dimensional embedding (some larger embedding models exceed this) has to be dimensionality-reduced or truncated before it fits a native `VECTOR` column.
• Vectors are stored in an optimized binary format internally but exposed as JSON arrays for convenience — casting a JSON array literal to `VECTOR(n)` (`CAST('[1.0, -0.2, 30]' AS VECTOR(3))`, or the implicit form `DECLARE @v VECTOR(3) = '[1.0, -0.2, 30]'`) is the standard way to construct one, and casting a vector back to `NVARCHAR(MAX)` or `JSON` reverses it.
• `VECTOR` can be used as a table column, a variable, and a stored procedure or function parameter (including as an `OUTPUT` parameter) — it behaves like any other SQL Server data type in those respects.

Exact search with VECTOR_DISTANCE

• `VECTOR_DISTANCE(distance_metric, vector1, vector2)` computes the distance between two vectors using `'cosine'`, `'euclidean'`, or `'dot'` (negative dot product) as the metric — it is always exact, and critically, it never uses a vector index even if one exists on the column.
• A typical k-nearest-neighbor (kNN) query pattern is `SELECT TOP (10) id, title, VECTOR_DISTANCE('cosine', @queryVector, content_vector) AS distance FROM table ORDER BY distance` — because this is a brute-force scan across every candidate row, Microsoft's general guidance is to use exact search when the searchable set is under roughly 50,000 vectors (after any `WHERE`-clause filtering has already narrowed the candidate set), and to reach for approximate search covered next once it's larger.
• A query can also filter directly on a distance threshold (`WHERE VECTOR_DISTANCE('cosine', @v, title_vector) < 0.3`) rather than only taking a fixed `TOP N`, which is the right shape when "everything reasonably similar" matters more than "the fixed top 10."

Common confusion

• `VECTOR_DISTANCE` will happily run against a column that has a vector index defined on it, and will still return exact results every single time — the function's own documentation is explicit that it never uses a vector index, so seeing an index exist on a column is not evidence that a given query is using approximate search; only `VECTOR_SEARCH` (next section) can actually use one.

Once exact search stops scaling, approximate nearest neighbor (ANN) search — built on a dedicated vector index and the `VECTOR_SEARCH` function — trades a small amount of recall accuracy for search that stays fast as the dataset grows into the millions of rows.

Exact (ENN) vs. approximate (ANN) search

• Exact nearest neighbor (ENN) search — what `VECTOR_DISTANCE`-based kNN queries perform — calculates distance against every candidate row and is guaranteed to find the true nearest neighbors, at linear cost in the number of rows.
• Approximate nearest neighbor (ANN) search uses a specialized index structure to avoid scanning every row, returning results that are very likely but not *guaranteed* to be the true nearest neighbors — the trade a workload makes deliberately once dataset size makes exhaustive scanning too slow to be practical.
• Approximate vector index and vector search are preview features, currently available in SQL Server 2025, Azure SQL Database, and SQL database in Microsoft Fabric.

Creating and using a vector index

• `CREATE VECTOR INDEX idx_name ON table(vector_column) WITH (METRIC = 'cosine')` builds the ANN index structure used by approximate search.
• `VECTOR_SEARCH(TABLE = table AS alias, COLUMN = vector_column, SIMILAR_TO = @queryVector, METRIC = 'cosine')` is a table-valued function returning every column from the target table plus a computed `distance` column — when referencing an alias in the `TABLE` argument, that same alias (not `VECTOR_SEARCH`'s own result alias) is what you use to reference the table's other columns in the `SELECT`.
• `SELECT TOP (N) WITH APPROXIMATE ... ORDER BY distance` is what actually triggers approximate (ANN) execution — the `ORDER BY` must reference only the `distance` column in ascending order, and using `WITH APPROXIMATE` without a `VECTOR_SEARCH` function in the query, or omitting `TOP`/`ORDER BY` requirements, raises an error rather than silently falling back to exact search.
• Without `WITH APPROXIMATE`, a `VECTOR_SEARCH` query still runs, but as an exact kNN scan — meaning `VECTOR_SEARCH` itself doesn't guarantee approximate execution; the `WITH APPROXIMATE` clause is what does. A newer engine behavior also applies relational `WHERE` predicates *during* the vector search (iterative filtering) rather than only after the fact, which fixed a historical problem where filtering after the fact could return fewer rows than actually existed.
• The `FORCE_ANN_ONLY` table hint forces the optimizer to use the approximate index specifically rather than letting it choose an execution strategy — it requires both an existing vector index and `SELECT TOP (N) WITH APPROXIMATE` already being used; specifying it without either fails.
• Vector-indexed tables cannot be truncated directly with `TRUNCATE TABLE` — the index has to be dropped first, the table truncated and repopulated with at least 100 rows, and the index recreated afterward.

Choosing and evaluating vector index types and metrics

• The distance metric (`cosine`, `euclidean`, or `dot`) has to match how the embedding model itself was trained to be compared — using the wrong metric for a given embedding model silently degrades relevance without producing any error.
• Evaluating a vector or hybrid search setup means measuring recall (did the approximate index actually surface the true nearest neighbors often enough) against the latency and cost savings ANN provides over exhaustive ENN scanning — the acceptable accuracy loss is a business decision informed by testing on representative queries, not a fixed number.

Common confusion

• Adding a vector index to a table and using `VECTOR_SEARCH` in a query are each necessary but not sufficient on their own for approximate search to actually happen: a vector index with no `WITH APPROXIMATE` clause in the query still yields an exact kNN scan, and `WITH APPROXIMATE` with no vector index present is simply an error — getting ANN execution requires the index, the `VECTOR_SEARCH` function, and the `TOP (N) WITH APPROXIMATE ... ORDER BY distance` syntax all present together.

Vector similarity alone doesn't win every retrieval scenario — this section covers when to reach for keyword-based full-text search instead, and how hybrid search combines both approaches for better relevance than either alone.

Choosing among full-text, vector, and hybrid search

• Full-text search excels at exact terminology — product codes, proper nouns, acronyms, and precise keyword matches — cases where a semantically "close" vector match can actually be the *wrong* answer because it's topically similar but not textually correct.
• Vector (semantic) search excels at conceptual similarity — finding relevant content that's worded completely differently from the query — but can miss an exact identifier or rare term that isn't well represented in the embedding space.
• Hybrid search runs both a full-text (or keyword) query and a vector similarity query against the same request and merges their results, directly compensating for each approach's individual blind spot rather than forcing a single choice between them.

Implementing full-text search

• Full-text search requires a full-text index built on the searchable column(s), queried with predicates like `CONTAINS` or `FREETEXT` rather than a plain `LIKE` — it supports linguistic features (stemming, thesaurus, proximity search) that `LIKE` and even regex functions don't provide, since it's purpose-built for natural-language text search rather than pattern matching.

Merging results with Reciprocal Rank Fusion

• Reciprocal Rank Fusion (RRF) combines two or more ranked result lists (a keyword-search ranking and a vector-search ranking) into one final ranking, scoring each document by the sum of `1 / (k + rank)` across every list it appears in — a document ranked highly by *either* method contributes a large score, while one that ranks well in both compounds to an even higher combined score.
• RRF is specifically a *rank-based* fusion technique — it only needs each result's position in its respective ranked list, not the raw, differently-scaled similarity or relevance scores each method produces internally, which is exactly what makes it possible to combine two methods (full-text relevance scores and cosine vector distances) that otherwise aren't on a comparable numeric scale at all.

Evaluating vector and hybrid search performance

• Retrieval quality is measured independently of generation quality: precision and recall against a labeled set of "actually relevant" documents, and rank-aware metrics that reward relevant results appearing near the top, apply to a hybrid retriever the same way they would to any search-ranking system.
• Because hybrid search has more tunable pieces than either approach alone (the full-text query shape, the vector similarity threshold, and how RRF weighs the two), isolating what caused a relevance improvement or regression means changing one variable at a time against a fixed, representative test query set rather than adjusting several knobs simultaneously.

Common confusion

• Hybrid search and simply running two separate queries and eyeballing both result sets are not the same thing — the value of hybrid search specifically comes from a principled fusion step like RRF that produces one coherent ranking from both signals, rather than leaving it to the caller (or the end user) to reconcile two independently-ranked, differently-scored lists by hand.

RAG is the capstone pattern this domain builds toward — pulling relevant data out of the database, handing it to a language model as context, and returning that model's answer, entirely orchestrated from T-SQL.

Identifying RAG use cases

• RAG fits scenarios where an LLM needs to answer questions grounded in specific, current, or proprietary data it wasn't trained on — the model supplies natural-language fluency and reasoning, while the database supplies the facts, retrieved fresh at query time rather than baked into the model's training data.
• Because retrieval happens against live data, a RAG answer reflects data as of right now (a price change, a newly added row) in a way that would otherwise require retraining or fine-tuning a model to keep up with — the trade RAG makes is added query-time latency (the retrieval step, then the model call) in exchange for that freshness.

Converting structured data to JSON for LLM consumption

• A language model consumes text, so relational query results need to be shaped into JSON before being sent as prompt context — `JSON_OBJECT`, `JSON_ARRAY`, and `FOR JSON` all serialize rows and columns into the JSON text a prompt template can embed directly.
• Retrieval typically runs a hybrid or vector search (from the previous section) first to select only the *relevant* rows, then serializes just that narrowed result set to JSON — sending an entire table as context would both blow past the model's context window and dilute the genuinely relevant information with noise.

Prompting via sp_invoke_external_rest_endpoint

• `sys.sp_invoke_external_rest_endpoint @url = N'https://...', @payload = N'{...}', @method = N'POST', @headers = N'{...}', @credential = [...], @timeout = 30, @retry_count = 3, @response = @response OUTPUT` is the general-purpose stored procedure for calling any HTTPS REST endpoint from T-SQL — including an Azure OpenAI chat completion endpoint — and it's what actually sends the assembled prompt (system instructions plus the retrieved JSON context plus the user's question) to the model.
• Authentication uses the same `DATABASE SCOPED CREDENTIAL` mechanism as external models: `Managed Identity` sends the system-assigned identity in request headers, `HTTPEndpointHeaders`/`HTTPEndpointQueryString` inject a stored secret (like an API key) into headers or the query string, and `Shared Access Signature` provides delegated, time-limited access — a database user needs `REFERENCES` permission on the credential to use it via `@credential`.
• `sp_invoke_external_rest_endpoint` is disabled by default in SQL Server 2025 and Azure SQL Managed Instance (enabled with `EXECUTE sp_configure 'external rest endpoint enabled', 1; RECONFIGURE WITH OVERRIDE;`) but enabled by default in Azure SQL Database and SQL database in Fabric — and calling it at all requires the `EXECUTE ANY EXTERNAL ENDPOINT` database permission.
• Concurrent outbound calls through the procedure are throttled to roughly 10% of worker threads (capped at 150), enforced at the database level and, for an elastic pool, at the pool level too — a RAG workload issuing many concurrent model calls can hit this throttle well before it hits any model-side rate limit, and `sys.dm_user_db_resource_governance` reports the actual configured limits for a given service tier.

Extracting language-model responses

• The `@response` output parameter receives a JSON (or XML) envelope containing HTTP status/headers under a `response` key and the actual payload the model returned under a `result` key — extracting the model's generated text back out means applying `JSON_VALUE`/`JSON_QUERY` to that `result` portion of the response, the same JSON functions used everywhere else in this domain.
• Checking the HTTP status embedded in `@response` (or the procedure's own return value, which is `0` on a 2xx response and the HTTP status code otherwise) before trusting the payload matters because a non-2xx response still returns successfully from the procedure's perspective unless the call couldn't be made at all — silently parsing an error response as if it were a valid model answer is an easy mistake in a first RAG implementation.

Common confusion

• Generating an embedding (`AI_GENERATE_EMBEDDINGS`, used for the retrieval half of RAG) and generating a natural-language answer (`sp_invoke_external_rest_endpoint` calling a chat/completion model, used for the generation half) are frequently conflated because both are "calling AI from T-SQL," but they're different calls to conceptually different kinds of models — a RAG pipeline needs both steps in sequence, first embedding the query to retrieve relevant rows via vector or hybrid search, then separately sending those retrieved rows plus the original question to a generative model to produce the final answer.