# SQL Server 2025 AI: Vector Search & Upgrade Guide

> Explore SQL Server 2025 AI, vector search, Copilot, security, and a safe migration path from SQL Server 2022.

## Introduction: SQL Server 2025 and the AI question

SQL Server 2025 puts vector search, embedding generation, and AI model connections inside the familiar SQL environment. This is a large change for companies whose customer, product, and operational data already lives in SQL Server 2022.

![GitHub Copilot documentation page](/assets/github-copilot-documentation.webp)

*GitHub Copilot is part of the broader AI-assisted development workflow around SQL Server projects..*

SQL Server 2025 became generally available on **November 18, 2025**, ending its product preview. Some SQL Server AI features remain in preview and should not be treated as production-ready. This guide covers:

- How native vectors and semantic search work
- What the new T-SQL AI functions actually do
- Where GitHub Copilot fits into database work
- How to test an upgrade without putting production systems at risk

**TL;DR:** SQL Server 2025 combines Microsoft SQL AI and vector search, but preview features still matter when evaluating an enterprise AI database.

## What SQL Server 2025 means as an enterprise AI database

Microsoft describes SQL Server 2025 as an enterprise AI-ready database that combines relational data with numerical representations of text, images, or other content. These **embeddings** let applications search by meaning while applying SQL filters for price, region, permissions, dates, or account status.

SQL Server does not automatically understand every table or run a general-purpose language model over all company data. Teams must still choose a model, prepare content, control access, test retrieval, and build the application.

| Capability | What it does | Current position |
|---|---|---|
| SQL Server 2025 engine | Runs relational and AI-assisted workloads | Generally available |
| `vector` type and `VECTOR_DISTANCE` | Stores embeddings and calculates exact distance | Generally available |
| AI chunking, external models, and embedding generation | Prepares text and calls configured embedding models | Generally available |
| DiskANN vector indexes and `VECTOR_SEARCH` | Speeds up approximate similarity search | Preview in SQL Server 2025 |
| Half-precision vectors | Reduces vector storage and supports more dimensions | Preview |
| GitHub Copilot in SSMS | Assists with T-SQL and database questions | Available; Agent mode remains in preview |

Microsoft reported **10,000 preview participants** and **100,000 active SQL Server 2025 databases** when it announced general availability. Those figures show substantial testing, but they do not remove the need to check the [current SQL Server 2025 release notes](https://learn.microsoft.com/en-us/sql/sql-server/sql-server-2025-release-notes?view=sql-server-ver17) before adopting a specific feature.

## Vector search in SQL Server 2025

Keyword search matches words; vector search in SQL Server 2025 matches similar numerical representations. For example, *shipments arriving behind schedule* may match *late delivery* despite different wording.

A basic SQL Server AI retrieval flow:

1. Divide a document, ticket, product description, or campaign asset into manageable text chunks.
2. Send each chunk to an embedding model, which returns a fixed-length vector.
3. Store the vector beside the original text and its business metadata.
4. Convert a user's question into a vector and compare it with stored vectors.
5. Filter the matches using ordinary SQL conditions before returning the best results.

SQL Server 2025 supports `float32` vectors with **1 to 1,998 dimensions**. Preview `float16` supports **3,996 dimensions** with half the bytes per value and lower numerical precision. The [vector data type documentation](https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type?view=sql-server-ver17) explains the storage and conversion rules.

| Search method | Best fit | Trade-off |
|---|---|---|
| Exact search with `VECTOR_DISTANCE` | Small datasets, evaluation sets, and accuracy checks | Calculates distances across candidate rows and becomes expensive at scale |
| Approximate search with `VECTOR_SEARCH` and DiskANN | Large collections where low latency matters | Faster, but may not return the mathematically closest result and remains in preview |

Hybrid search is especially useful: a retailer can match *quiet shoes for city walking* while requiring inventory above zero, a specific market, and an approved price range. The vector finds meaning; SQL enforces business facts.

## Native Microsoft SQL AI functions and model connections

Native Microsoft SQL AI functions reduce application-side plumbing but still send text to an approved model endpoint and store the returned embedding.

A practical pipeline:

1. Enable external REST endpoint access for the database instance after a security review.
2. Register an endpoint with `CREATE EXTERNAL MODEL`, including its purpose, authentication method, and embedding model name.
3. Use `AI_GENERATE_CHUNKS` to split long text into fixed-size pieces with an optional overlap of up to **50%**.
4. Call `AI_GENERATE_EMBEDDINGS` to obtain vectors from the registered model.
5. Store the source ID, chunk order, text, model version, and vector together.

`AI_GENERATE_CHUNKS` requires database compatibility level **170**. Microsoft documents the full syntax in its guides to [AI text chunking](https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-chunks-transact-sql?view=sql-server-ver17) and [embedding generation](https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-embeddings-transact-sql?view=sql-server-ver17).

You can point the external model to Azure OpenAI, OpenAI-compatible services, or Ollama for model flexibility.

The documented local ONNX runtime currently requires Windows, SQL Server Machine Learning Services, and preview features, so I would treat it as a laboratory option until its operating and security requirements are thoroughly tested.

Because model calls add latency, failure risk, and usage costs, store embeddings when content changes instead of regenerating them for every search. Also record the model and dimensions; model changes usually require controlled re-embedding.

## GitHub Copilot in SSMS for SQL Server AI workflows

GitHub Copilot in SSMS helps database professionals write, explain, fix, document, and improve T-SQL, using the active editor and connected database for more specific answers.

Use concrete, focused prompts:

- Explain this execution plan and identify the largest estimated cost.
- Draft a read-only query showing monthly revenue by market, using the existing schema.
- Check this migration script for objects that require compatibility level 170.
- Show current blocking without changing sessions or database settings.

SSMS **22.2** introduced autocomplete. In SSMS **22.7** and later, Agent mode can handle higher-level tasks, inspect execution plans, run queries, and propose schema changes.

Preview Agent mode requests approval before modifications. The [GitHub Copilot in SSMS overview](https://learn.microsoft.com/en-us/ssms/github-copilot/overview) also warns that generated answers can be inaccurate and require qualified review.

Copilot uses the connected login's privileges, so a read-only account remains read-only. This helps but does not replace reviewing generated SQL, estimated plans, transaction boundaries, and affected row counts.

Microsoft says this SSMS integration neither retains prompts or responses nor uses them to train models, but it can send prompts and selected database context to the chosen model. Organizations should approve this data path before using Copilot with confidential schemas. Because Copilot supports SQL Server back to 2014, it alone does not justify upgrading from SQL Server 2022.

## Four practical SQL Server AI applications

A sensible pilot starts with one retrieval problem and measurable success criteria, not every text column in the company.

| Application | SQL Server AI design | Metric to record | Main guardrail |
|---|---|---|---|
| Customer support knowledge | Embed approved manuals and solved cases, then filter by product, version, and customer entitlement | Recall at 5, median resolution time, and incorrect-answer rate | Return source links and exclude unresolved or outdated cases |
| Product discovery | Match conversational requests to product descriptions, then apply inventory, market, language, and price filters | Zero-result rate, click-through rate, and conversion rate | Never let similarity override availability or legal restrictions |
| Marketing content reuse | Find campaign assets similar to a new brief while filtering by channel, territory, rights, and expiration date | Time spent finding an asset and percentage of reused content | Keep approval status and usage rights as structured fields |
| Policy and contract lookup | Retrieve relevant clauses and policy passages for an employee's question | Precision at 5, unanswered-question rate, and reviewer acceptance | Display the original passage and require human review for decisions |

In a public early-adopter example, Microsoft reports that Ivanti has used SQL Server 2025 with Azure OpenAI to develop tools intended to help customers find knowledge and resolve incidents faster. This suggests a direction, not a published controlled benchmark.

For an internal pilot, create 50 to 200 representative questions answered by subject-matter experts, then compare exact vector results, keyword search, and a hybrid approach. Measure retrieval accuracy, p95 latency, endpoint cost, and unsafe or irrelevant answers. A polished demonstration can hide weak retrieval; a fixed evaluation set cannot.

## Security and governance for an enterprise AI database

An embedding is an array of numbers derived from business data; converting text into vectors does not make it anonymous. The source text, embedding endpoint, credentials, logs, and retrieved passages require an explicit security design.

| Item | What to check | Why it matters |
|---|---|---|
| Data boundary | Approved columns, classification, endpoint region, and logging policy | Sensitive text may leave the SQL Server process during embedding generation |
| Identity | Managed identity or scoped database credentials with minimal permissions | Shared API secrets and broad roles increase exposure |
| Model record | Provider, model name, version, vector dimensions, and distance metric | Mixing incompatible embeddings produces misleading results |
| Access control | `EXECUTE` rights on external models and normal row-level data permissions | AI retrieval should not bypass existing authorization rules |
| Quality tests | Exact expected passages, Recall at k, latency, and failure thresholds | Similar-looking answers are not proof of reliable retrieval |
| Operations | Endpoint errors, retry count, p95 response time, token usage, and embedding backlog | AI services can fail or become expensive independently of SQL Server |
| Preview boundary | Databases using `PREVIEW_FEATURES`, owners, expiry dates, and rollback steps | Preview syntax and behavior can change in a cumulative update |

Managed identity authentication to Azure OpenAI is available through an Azure Arc-connected SQL Server identity. Otherwise, use a scoped credential and rotate it through the normal secrets process. The [external model documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-model-transact-sql?view=sql-server-ver17) describes the permissions and supported authentication patterns.

Do not send raw personal data to unapproved endpoints, regenerate embeddings per request, give Copilot `sysadmin` access for convenience, or switch embedding models without versioning and rebuilding stored vectors. Keep DiskANN vector indexes out of production promises while Microsoft labels them preview. Architects must test current deployment and table-design constraints against their release process.

## SQL Server 2022 versus SQL Server 2025

SQL Server 2022 can support an AI application by reading rows, calling an embedding service, storing vectors in JSON or binary columns, or sending search requests to a separate vector database. SQL Server 2025 reduces custom integration.

| Area | SQL Server 2022 | SQL Server 2025 |
|---|---|---|
| Vector storage | No native vector type | Native validated `vector` columns with binary storage |
| Similarity calculation | Application code or an external search service | Built-in exact distance functions |
| Approximate vector index | External system required | DiskANN index available as a preview feature |
| Text preparation | Application or ETL code | Native fixed-size chunking at compatibility level 170 |
| Model management | Application configuration | External model objects and database permissions |
| Embedding generation | Application calls model endpoint | T-SQL can call a registered embedding model |
| Copilot | Supported through current SSMS releases | Supported; no upgrade required solely for Copilot |
| Developer data types | Text-based JSON support | Native JSON type plus vector, regular-expression, and REST additions |
| Engine improvements | Mature SQL Server 2022 behavior | Improved locking and more than 50 documented engine improvements |

Microsoft cited an early Entain test where an ordered nonclustered columnstore change improved one analytical workload by more than **63%**. This customer-specific result is not universal. The broader list is available in [What is new in SQL Server 2025](https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2025?view=sql-server-ver17).

An upgrade is most compelling when an organization wants vectors beside relational data, governed through existing SQL permissions, backups, and operations. A specialized vector database may remain better for very large search-only workloads, globally distributed retrieval, or teams with a mature search platform. SQL Server 2025 reduces system sprawl without making every alternative obsolete.

## Migrating an enterprise AI database from SQL Server 2022 to SQL Server 2025

SQL Server 2025 supports direct in-place upgrades from SQL Server 2022, but a side-by-side migration is often easier to test and reverse for an important enterprise AI database.

Side-by-side migration also lets teams compare both environments under the same representative workload.

1. **Define the reason for moving.** Name one business problem, such as semantic support search, and specify its data volume, users, latency target, accuracy measure, and data classification. Without one, the upgrade is difficult to evaluate.

2. **Record the SQL Server 2022 baseline.** Record CPU, memory, storage latency, waits, job duration, p95 query time, Query Store plans, backup duration, and recovery tests. Capture at least one full business cycle if the workload varies by day or month.

3. **Run an upgrade assessment.** The [SSMS migration component](https://learn.microsoft.com/en-us/ssms/migrate/upgrade-sql-server) checks breaking changes, deprecated features, compatibility issues, and feature parity. Review linked servers, CLR code, replication, availability groups, drivers, Agent jobs, and monitoring integrations separately.

4. **Build a representative test environment.** Restore a recent production backup to SQL Server 2025, apply current updates, mask sensitive data where necessary, and replay normal plus peak workloads. Confirm support for the target operating system and all drivers.

5. **Keep compatibility level 160 during initial validation.** Restored and upgraded databases normally retain their existing compatibility level. This isolates the engine move from level 170 changes; once stable, move selected databases to 170 to test new optimizer behavior and `AI_GENERATE_CHUNKS`.

6. **Use Query Store during the compatibility change.** Compare plans and regressions before and after level 170. Microsoft's [Query Tuning Assistant workflow](https://learn.microsoft.com/en-us/sql/relational-databases/performance/upgrade-dbcompat-using-qta?view=sql-server-ver17) can guide this process, but it does not generate a representative workload for you.

7. **Create a separate SQL Server AI pilot.** Start with the generally available vector type and exact distance calculations. Enable `PREVIEW_FEATURES` only in a named test database if DiskANN indexing, `VECTOR_SEARCH`, half-precision vectors, or local ONNX execution must be evaluated.

8. **Plan cutover and reversal.** Test backups, restores, logins, permissions, jobs, connection strings, monitoring, disaster recovery, and the final synchronization method. Keep the SQL Server 2022 environment recoverable until the acceptance period is complete.

Microsoft's [supported upgrade paths](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2025?view=sql-server-ver17) also note that SQL Server 2025 is 64-bit only and that pending restarts can block setup. Because SQL Server 2022 was the final Web edition release, users must choose a supported SQL Server 2025 edition or another hosting model.

## Conclusion: choose SQL Server AI through a measured pilot

SQL Server 2025 lets teams keep vectors, business records, and SQL filters in one database while native chunking, external model definitions, embedding generation, and Copilot reduce custom work. However, key scaling features, including DiskANN vector indexes and Agent mode, remain in preview.

Start with three controlled actions:

- Restore a SQL Server 2022 database to SQL Server 2025 and validate it at compatibility level 160.
- Move one test database to level 170 and measure a narrow semantic-search use case.
- Keep preview features separate until their limits, upgrade behavior, and recovery process meet production standards.

This turns the Microsoft SQL AI strategy into evidence for architects. An enterprise AI database should earn approval through retrieval quality, security, cost, and recoverability, not a product label.

## Frequently asked questions

### Is SQL Server 2025 ready for production AI workloads?

The core engine, native vector type, exact distance calculations, chunking, external models, and embedding generation are generally available. DiskANN indexes, approximate `VECTOR_SEARCH`, half-precision vectors, and some local model capabilities remain in preview and should be isolated from production commitments.

### Do we need SQL Server 2025 to use GitHub Copilot in SSMS?

No. GitHub Copilot in current SSMS releases supports older SQL Server versions, so Copilot alone is not a reason to upgrade. Review generated SQL carefully and connect through an account with only the permissions needed for the task.

### When should we use exact versus approximate vector search?

Use `VECTOR_DISTANCE` for smaller datasets, evaluation baselines, and situations where finding the closest matches matters most. Approximate search can reduce latency on larger collections, but it may miss the mathematically nearest result and its SQL Server 2025 implementation remains in preview.

### Should embeddings be generated for every search request?

Generate and store embeddings when source content is added or changed rather than repeatedly embedding the same material. A user's new query still needs its own embedding, but caching and controlled retries can reduce endpoint cost and latency. Record the model, version, dimensions, and distance metric so stored vectors remain compatible.

### Do embeddings remove privacy and access-control concerns?

No. Embeddings can still represent sensitive business or personal information and should be protected alongside the source content. Approve the model endpoint and its region, use scoped credentials, restrict external-model permissions, and ensure retrieval respects existing row-level authorization.

### What is the safest way to migrate from SQL Server 2022?

For important systems, a side-by-side migration usually provides clearer testing and an easier reversal path than an in-place upgrade. Validate the restored database at compatibility level 160 first, then test level 170 separately with Query Store before cutover. Keep the SQL Server 2022 environment recoverable until acceptance and recovery tests pass.

### How should we evaluate a first SQL Server AI pilot?

Choose one retrieval problem and build a fixed set of representative questions with expert-approved answers. Compare keyword, exact vector, and hybrid search using measures such as recall, precision, p95 latency, endpoint cost, and unsafe-result rate. Keep structured filters for permissions, availability, territory, and other business rules in every relevant query.

---

[View the canonical page](https://dbsilk.com/blog/sql-server-2025-ai-features/) · [Browse llms.txt](https://dbsilk.com/llms.txt)