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;@credential_name parameter, leveraging the database’s system-assigned managed identity.
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
| Feature | SQL Server 2022 | Azure SQL DB | Azure SQL MI |
|---|---|---|---|
| Vector search (VECTOR type) | Coming soon | Available | Available |
| ML Services (Python/R) | Available | Not available | Available |
| PREDICT (ONNX) | Available | Available | Available |
| REST endpoint (sp_invoke_external) | Not available | Available | Not available |
| Intelligent query processing | Available | Available | Available |
Production architecture
- 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.
- 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.
- Security — use row-level security (RLS) to ensure users only search documents they have access to. The same RLS policies apply to vector searches.
- Monitoring — track AI-related query costs with Extended Events. Monitor
sp_invoke_external_rest_endpointcall durations and error rates. - 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
- Enable vector support in Azure SQL Database — it’s available in the latest compatibility level.
- Store your first embeddings — pick a small table and add a VECTOR column.
- Try PREDICT — export a scikit-learn model to ONNX and score it in T-SQL.
- Read the docs: learn.microsoft.com/sql/machine-learning