DP-800 AI capabilities explained

Implement AI capabilities in database solutions is 25–30% of DP-800. It covers three areas: external models and embeddings, intelligent search (full-text, vector and hybrid) and retrieval-augmented generation (RAG) driven from T-SQL. The questions are practical: which search to choose, which embedding maintenance method fits, and how a prompt is assembled from query results.

Design and implement models and embeddings

An embedding is a vector of numbers produced by a model so that texts with similar meaning end up close together. The database stores those vectors and compares them.

• Evaluate external models: consider whether the model is multimodal, which languages it handles, its size and cost, its vector dimensions and whether it supports structured output.
• Create and manage external models: register a model endpoint in the database so that T-SQL can call it. Authenticate with a managed identity where possible instead of storing a key.
• Which columns to embed: embed the text that carries meaning (a title plus a description), not identifiers or numbers. Concatenate fields deliberately.
• Chunking: long documents are split into pieces that fit the model’s input limit and that each hold a coherent idea. Chunks that are too large blur meaning; too small lose context. Overlap helps keep context across boundaries.
• Generate embeddings: a T-SQL function calls the registered model and returns a vector you store in a vector column.

Keeping embeddings current

The usual exam answer is asynchronous: never make a user transaction wait for an external model.

Design and implement intelligent search

Choosing a search type

Vector data and functions

Store embeddings in the vector type with the dimensions your model produces. VECTOR_DISTANCE computes a distance between two vectors using a metric such as cosine, Euclidean or dot product, and VECTOR_NORMALIZE and VECTORPROPERTY help prepare and inspect vectors. VECTOR_SEARCH runs a nearest-neighbour search against a vector index.

KNN versus ANN. Exact k-nearest-neighbour search compares the query with every vector: perfect recall, cost grows with table size. Approximate nearest-neighbour search uses an index to find close vectors fast, at a small recall cost. Pick ANN for large tables and tight latency; pick exact search for small tables or when perfect results are required. Choose the distance metric that matches how the model was trained, and keep it consistent between index and query.

Hybrid search and RRF

Hybrid search runs a keyword search and a vector search, then merges the two result lists. Reciprocal rank fusion (RRF) scores each item by summing 1 / (k + rank) across lists, where k is a smoothing constant. Because it uses ranks, not raw scores, it does not matter that full-text and vector scores are on different scales. Evaluate search quality by measuring relevance on test queries as well as latency and index size.

Design and implement RAG

RAG answers a question using your data instead of the model’s memory. The steps:

1. Embed the user’s question and retrieve the top matching rows or chunks.
2. Convert the structured results to JSON (for example with FOR JSON) so the model can read them.
3. Build a prompt containing instructions, the question and the retrieved context.
4. Send it to the language model with the sp_invoke_external_rest_endpoint stored procedure, authenticating through a credential or managed identity.
5. Parse the JSON response, for example with JSON_VALUE, and return the answer.

Use RAG when answers must reflect current private data, cite sources or avoid retraining. Do not use it when a simple SQL query already answers the question.

Sample questions