AI capabilities in modern SQL engines

Store staff at Harrow & Finch Home Supply answer the same questions every day. Which primer goes under a dark paint? Will this caulk work in a shower? What do I need to replace a toilet fill valve? The answers exist, scattered across 18,400 product records and 1,240 how-to guides, but finding them means knowing the right product name or the right search word. A customer who asks for something to stop a bathroom mirror fogging up does not know that the product is called an anti-fog film, and the old catalog search, which matches words, returns nothing useful.

In October 2026, Grace Oduya, the store operations director, asked the development team for a better way. Hana Sato, the developer who rebuilt the Finch Rewards loyalty app, spent two weeks, from October 26 to November 11, building a prototype called Ask Finch on a test copy of the catalog database, hf-sql-catalog-test-cus, an Azure SQL Database in Central US. The prototype lets a staff member type a question in everyday words and get back the most relevant products and guide passages, and, in a second step, a short written answer with links to the sources it used.

Hana is also preparing for exam DP-800, Developing AI-Enabled Database Solutions, which she plans to sit in June 2027. Her mentor for the AI parts of the work is Nikolai Petrenko, a data engineer who joined Harrow & Finch in September 2026. Nikolai has one question he asks about every design: who reads this data, and how fast do they need it? Elena Brandt, the senior DBA, joins whenever a design touches database resources, and Carmen Ortiz, the privacy officer, reviews anything that sends company or customer data to a model. This course follows the prototype, what Hana learned building it, and the decisions the team made before a store pilot planned for February 2027.

Why put AI in the database

For most of the history of relational databases, artificial intelligence lived somewhere else. A data scientist exported data to files, trained or called a model in Python, and wrote results back. Search over meaning, rather than exact words, needed a separate search service or a specialized vector database, with its own copy of the data, its own security model and its own synchronization job. Every copy is another place where data can be stale, another set of permissions to manage and another bill.

Modern SQL engines bring several of these pieces inside the database. SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance and SQL database in Microsoft Fabric can store vectors in a native data type, compare them with built-in distance functions, search them with an approximate vector index, call an embedding model from T-SQL, split long text into chunks, and call any allowed REST endpoint, including a language model, from a stored procedure. The data stays where its security, backups, auditing and transactions already are, and a developer who knows T-SQL can build semantic search without learning a second database.

That does not mean every AI workload belongs in the database. Training models, running large batch inference over terabytes of data, or serving thousands of model calls a second are jobs for dedicated services. The database is a good home for AI features that sit close to operational data: searching products, finding similar support cases, grounding a chatbot in current inventory, or flagging records that look like known problems. Nikolai's rule of thumb is that if the answer depends on data that changes during the day and is already protected in the database, the AI feature should run next to that data.

The building blocks

The DP-800 outline groups the AI skills into three areas: designing and implementing models and embeddings, designing and implementing intelligent search, and designing and implementing retrieval-augmented generation. Each area uses a small set of engine features. The table summarizes them; later lessons cover each in detail.

Availability is not identical everywhere, and the exam expects candidates to notice. The vector data type with 32-bit elements is generally available in SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance and SQL database in Fabric. On Managed Instance, the AI functions need the Always-up-to-date or SQL Server 2025 update policy, because they arrived with the SQL Server 2025 engine. Some pieces are still in preview in some products as of November 2026: vector indexes are still described as preview in Azure SQL Database and Fabric on one reference page and as generally available on another, a difference lesson 6 explains, and they are in preview in SQL Server 2025, where they also require the PREVIEW_FEATURES database scoped configuration, and the half-precision float16 vector type is a preview feature in SQL Server 2025 while generally available in the Azure products. Hana keeps a one-page availability table pinned to her monitor, and Nikolai reminds her to recheck it before the pilot, because preview features change.

Embeddings in one paragraph

An embedding is a list of numbers that represents the meaning of a piece of text, an image or another item, produced by a model trained so that items with similar meaning get similar lists. The model Hana uses returns 1,536 numbers for any text it is given. The sentence stop the bathroom mirror steaming up and the product description anti-fog mirror film, prevents condensation share almost no words, yet their embeddings point in nearly the same direction. Comparing embeddings with a distance function finds related items even when the words differ. That is the whole idea behind semantic search, and lesson 3 explains how embeddings are made.

Three ways to search

The DP-800 outline asks candidates to choose between full-text, semantic vector and hybrid search. The choice depends on what users type and what they need back.

Hana's first tests showed why the choice matters. Searching for PNT-114 with vectors returned paint, but not always that paint, because a product code carries no meaning a language model can use. Searching for something to keep my deck from going gray with full-text returned nothing, because no product description contains those words. Hybrid search, which runs both and merges the lists, handled both questions well, and it became the design for the pilot. Lesson 7 shows how.

Retrieval-augmented generation

Search returns records. Many users want an answer instead: a sentence or two that explains which product to buy and why. A language model can write that answer, but on its own it knows nothing about Harrow & Finch's stock, prices or guides, and it may invent plausible details. Retrieval-augmented generation, usually shortened to RAG, fixes this by retrieving relevant records first and sending them to the model with the question, with instructions to answer only from those records and to cite them. The database does the retrieval, a stored procedure builds the prompt as JSON, and sp_invoke_external_rest_endpoint sends it to the model. Lesson 8 builds this pipeline.

What changes for the developer

Building AI features in the database adds new objects and new kinds of dependency to an ordinary application. External models and database scoped credentials become part of the schema and must be deployed with it. Calls to models take tens or hundreds of milliseconds and can fail for reasons outside the database, such as throttling or an expired key, so procedures need timeouts, retries and error handling. Model output is not deterministic in the way a query is, so tests check for properties, such as whether the answer cites a source, rather than exact strings. And every call to a hosted model costs money, so design decisions about how often to generate embeddings or call a chat model show up on the monthly bill. Lessons 10 and 11 return to security and cost.

There is also a design question about where each step runs. Hana could generate embeddings inside the database with AI_GENERATE_EMBEDDINGS, or in an Azure Function triggered by changes, or in a batch job that writes vectors back. The DP-800 outline lists several ways to keep embeddings current, including table triggers, change tracking, Azure Functions with a SQL trigger binding, Logic Apps, change data capture, change event streaming and pipelines in Microsoft Foundry. Lesson 3 compares them. For the prototype, Hana did everything in T-SQL because it was the quickest way to learn, and she noted which steps would move elsewhere for production.

Natural language to SQL, and AI-assisted tools

A different use of AI is letting a person ask a question in words and have a model write the SQL query. It is tempting, because managers would love to ask how many paint returns Store 12 had last month without waiting for a report. It is also risky, because generated SQL can be wrong, slow or, without safeguards, harmful. Lesson 9 covers how to do it safely: a read-only role, a limited set of views, validation of the generated statement and showing the query to the user. The DP-800 outline also covers AI-assisted development tools, such as GitHub Copilot and Copilot in Fabric connected to a database through a Model Context Protocol server; those belong to the design and development skills taught in MSAZ-300, and this course mentions them only where they touch AI features in the database.

The prototype at the end of two weeks

By November 11, the Ask Finch prototype held embeddings for every product description and every guide section in the test database, used hybrid search for retrieval, and produced grounded answers for a set of 60 test questions that store staff had written. Hana scored each answer with Grace's store managers: 48 of the 60 answers were judged correct and useful, 7 were correct but missed a better product, and 5 were wrong or unhelpful. That is 80 in 100 correct and useful, good for a two-week prototype and not yet good enough for customers. The rest of this course follows what the team changed and why.

results = {"correct and useful": 48, "correct, missed a better product": 7, "wrong or unhelpful": 5}
total = sum(results.values())
for label, n in results.items():
    print(f"{label:34s} {n:3d}  ({n * 100 / total:.1f} in 100)")
print("total questions:", total)

correct and useful                  48  (80.0 in 100)
correct, missed a better product     7  (11.7 in 100)
wrong or unhelpful                   5  (8.3 in 100)
total questions: 60

Nikolai's comment on the scores was the question he always asks. Store staff read these answers while a customer waits, so speed matters as much as accuracy, and a wrong answer delivered with confidence is worse than no answer. The pilot plan therefore has two targets: at least 90 in 100 answers judged correct and useful on a larger test set, and a typical response in under three seconds.