Category: SQL Server and AI

Your data already lives in SQL Server. Your application queries run against it thousands of times per day. So why move data out to a separate AI service when you can bring AI capabilities directly into the database? From native vector search in Azure SQL to in-database Python/R execution and direct Azure OpenAI integration, SQL Server is becoming an AI-ready platform.

This guide covers the AI features across SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance — with production-ready T-SQL and Python code for each capability.

The AI capabilities in SQL Server

Vector search in Azure SQL

Azure SQL Database now supports native vector operations, enabling RAG (Retrieval-Augmented Generation) patterns directly in your database. Store embeddings alongside your relational data and perform similarity searches without an external vector database.

Storing embeddings

CREATE TABLE Documents (
    Id INT IDENTITY PRIMARY KEY,
    Title NVARCHAR(500),
    Content NVARCHAR(MAX),
    Embedding VECTOR(1536)  -- matches text-embedding-3-small dimensions
);

-- Insert a document with its embedding
INSERT INTO Documents (Title, Content, Embedding)
VALUES (
    'Remote Work Policy',
    'Employees may work remotely up to 3 days per week...',
    CAST('[0.0023, -0.0112, 0.0451, ...]' AS VECTOR(1536))
);

Similarity search

DECLARE @query_embedding VECTOR(1536) = /* embedding of the user's question */;

SELECT TOP 5
    Id,
    Title,
    VECTOR_DISTANCE('cosine', Embedding, @query_embedding) AS distance
FROM Documents
ORDER BY VECTOR_DISTANCE('cosine', Embedding, @query_embedding);

The VECTOR_DISTANCE function supports cosine, euclidean, and dot product distance metrics. Combined with a columnstore index, it handles millions of vectors efficiently.

Building a RAG pipeline in T-SQL

Here’s the complete pattern — generate an embedding for the user’s question, find relevant documents, and send both to Azure OpenAI for a grounded answer:

-- Step 1: Generate embedding for the user's question
DECLARE @question NVARCHAR(MAX) = 'Can I work from home on Fridays?';
DECLARE @embedding VECTOR(1536);

EXEC sp_invoke_external_rest_endpoint
    @url = 'https://your-resource.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-02-01',
    @method = 'POST',
    @payload = '{"input": "Can I work from home on Fridays?"}',
    @response = @embedding OUTPUT;

-- Step 2: Find relevant documents
SELECT TOP 3 Content
INTO #context
FROM Documents
ORDER BY VECTOR_DISTANCE('cosine', Embedding, @embedding);

-- Step 3: Send context + question to Azure OpenAI
DECLARE @context NVARCHAR(MAX) = (SELECT STRING_AGG(Content, CHAR(10)) FROM #context);

EXEC sp_invoke_external_rest_endpoint
    @url = 'https://your-resource.openai.azure.com/openai/deployments/gpt-4o/chat/completions?api-version=2024-02-01',
    @method = 'POST',
    @payload = /* JSON with system message + context + question */;

Machine Learning Services

SQL Server 2022 includes Machine Learning Services, which lets you run Python and R scripts directly inside the database engine using sp_execute_external_script.

EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'
import pandas as pd
from sklearn.cluster import KMeans

# InputDataSet is automatically populated from @input_data
model = KMeans(n_clusters=4, random_state=42)
InputDataSet["cluster"] = model.fit_predict(
    InputDataSet[["total_purchases", "avg_order_value", "days_since_last_order"]]
)
OutputDataSet = InputDataSet
',
    @input_data_1 = N'SELECT customer_id, total_purchases, avg_order_value, days_since_last_order FROM CustomerMetrics'
WITH RESULT SETS ((
    customer_id INT,
    total_purchases DECIMAL(10,2),
    avg_order_value DECIMAL(10,2),
    days_since_last_order INT,
    cluster INT
));

This runs the Python script in a sandboxed process alongside the SQL Server engine. Data flows in via InputDataSet and out via OutputDataSet — no data leaves the server.

Native PREDICT function

SQL Server 2022 can score ONNX models natively with the PREDICT function — no external runtime needed:

-- Load an ONNX model into the database
CREATE TABLE MLModels (
    model_name NVARCHAR(100) PRIMARY KEY,
    model_data VARBINARY(MAX)
);

INSERT INTO MLModels (model_name, model_data)
SELECT 'churn_predictor', BulkColumn
FROM OPENROWSET(BULK '/models/churn_model.onnx', SINGLE_BLOB) AS model;

-- Score data inline with a SELECT query
DECLARE @model VARBINARY(MAX) = (
    SELECT model_data FROM MLModels WHERE model_name = 'churn_predictor'
);

SELECT
    c.customer_id,
    c.customer_name,
    p.predicted_churn,
    p.churn_probability
FROM PREDICT(MODEL = @model, DATA = Customers AS c)
WITH (predicted_churn INT, churn_probability FLOAT) AS p
WHERE p.churn_probability > 0.7;

This enables real-time scoring at query time — every SELECT can include predictions without any application-layer ML infrastructure.

Calling Azure OpenAI from T-SQL

Azure SQL Database’s sp_invoke_external_rest_endpoint lets you call REST APIs directly from T-SQL — including Azure OpenAI:

DECLARE @response NVARCHAR(MAX);

EXEC sp_invoke_external_rest_endpoint
    @url = 'https://your-resource.openai.azure.com/openai/deployments/gpt-4o/chat/completions?api-version=2024-02-01',
    @method = 'POST',
    @headers = '{"api-key": "your-key"}',
    @payload = '
    {
        "messages": [
            {"role": "system", "content": "Classify this support ticket into: billing, technical, account. Reply with one word."},
            {"role": "user", "content": "I was charged twice for my subscription this month"}
        ],
        "max_tokens": 10,
        "temperature": 0
    }',
    @response = @response OUTPUT;

SELECT JSON_VALUE(@response, '$.result.choices[0].message.content') AS category;

Intelligent Query Processing

Beyond explicit AI features, SQL Server 2022 uses AI internally to optimize performance:

• Intelligent query processing (IQP) — automatic plan optimization, adaptive joins, and memory grant feedback.
• Query Store hints — the engine learns from past executions and applies query-level optimizations automatically.
• Cardinality estimation feedback — the optimizer corrects its estimates based on actual execution data.
• DOP (Degree of Parallelism) feedback — automatically adjusts parallelism per query based on runtime behavior.
• Optimized plan forcing — the Query Store detects and prevents plan regressions by forcing previously good plans.

These features require no code changes — enable them at the database level and the engine self-optimizes over time.

SQL Server + AI: feature matrix

Production architecture

1. Embeddings pipeline — generate embeddings on INSERT/UPDATE using a trigger or a scheduled job that calls Azure OpenAI. Cache embeddings in the VECTOR column to avoid recomputing.
2. Index strategy — use a columnstore index on your vector column for large-scale similarity search. For smaller tables (< 100K rows), brute-force cosine distance is fast enough.
3. Security — use row-level security (RLS) to ensure users only search documents they have access to. The same RLS policies apply to vector searches.
4. Monitoring — track AI-related query costs with Extended Events. Monitor sp_invoke_external_rest_endpoint call durations and error rates.
5. Fallback — wrap external API calls in TRY/CATCH blocks. If Azure OpenAI is unavailable, return a graceful error instead of failing the entire query.

Next steps

1. Enable vector support in Azure SQL Database — it’s available in the latest compatibility level.
2. Store your first embeddings — pick a small table and add a VECTOR column.
3. Try PREDICT — export a scikit-learn model to ONNX and score it in T-SQL.
4. Read the docs: learn.microsoft.com/sql/machine-learning