Skip to content
← All posts
· 9 min read·Emre Yurtbay

SQL Server 2025 vs. SQL Server 2022 – the key differences

What SQL Server 2025 (17.x) adds over its predecessor 2022 (16.x): a native vector type and AI in T-SQL, a native JSON type, RegEx, optimized locking, plus changes to editions, licensing and discontinued components.

SQL Server 2025SQL Server 2022MicrosoftDatabaseT-SQLVector searchAIMigration

With SQL Server 2025 (engine version 17.x), Microsoft released the next major version on 18 November 2025 during Ignite. Its predecessor, SQL Server 2022, carries version 16.x. The two releases follow distinct themes: 2022 was the "Azure-connected" release that bridged into the cloud. Microsoft positions 2025 as the "AI-ready" release – the database engine itself becomes a building block for AI applications, from a local install all the way to the cloud.

If you are planning a migration, the support horizon matters: under the Fixed Lifecycle Policy, mainstream support ends on 6 January 2031 and extended support on 6 January 2036. The release is visibly maturing – at the time of writing, several cumulative updates (CU1 through CU4) are already available.

This article sorts out what is genuinely new, what changes for editions and licensing, and where the pitfalls of an upgrade lie.

What is genuinely new compared to 2022

AI and vectors – the central new theme

The biggest conceptual leap over 2022 is native support for vector data right inside the engine. SQL Server 2025 introduces the VECTOR data type. Vectors are stored internally in an optimized binary format but exposed as JSON arrays. By default the base type is single-precision (4 bytes per element); half-precision (float16, 2 bytes) can be specified optionally via VECTOR(…, float16), but it is Preview at GA and requires the PREVIEW_FEATURES database scoped configuration.

New scalar functions build on this type: VECTOR_DISTANCE computes the distance between two vectors using a selectable metric ('cosine', 'euclidean', 'dot'), accompanied by VECTOR_NORM, VECTOR_NORMALIZE and VECTORPROPERTY.

-- Exact similarity search (kNN) using cosine distance
DECLARE @query VECTOR(1536) = AI_GENERATE_EMBEDDINGS(N'How do I cancel my contract?' USE MODEL MyEmbeddingModel);

SELECT TOP (5)
       d.id,
       d.title,
       VECTOR_DISTANCE('cosine', d.embedding, @query) AS distance
FROM   dbo.documents AS d
ORDER  BY distance ASC;

Microsoft distinguishes two search approaches: exact search (kNN) via VECTOR_DISTANCE is recommended for collections under roughly 50,000 vectors. For larger sets there is an approximate search (ANN) based on the graph-based DiskANN algorithm, created with CREATE VECTOR INDEX and queried via VECTOR_SEARCH. Important: the vector index and VECTOR_SEARCH are Preview at GA and require the PREVIEW_FEATURES database scoped configuration.

Embeddings can be generated directly in T-SQL: AI_GENERATE_EMBEDDINGS produces vector arrays via a previously defined model, and AI_GENERATE_CHUNKS splits text into fragments (chunking). Inference endpoints are attached through external model objects: CREATE EXTERNAL MODEL (along with ALTER/DROP EXTERNAL MODEL) stores the endpoint, authentication and purpose of a model, for example an Azure OpenAI endpoint. According to the editions page, there is also support for local ONNX models (Preview). SQL Server Management Studio (SSMS) additionally integrates GitHub Copilot.

T-SQL and developer features

The language has also advanced noticeably beyond AI:

  • Native JSON data type. SQL Server 2025 introduces a dedicated, binary-stored JSON type with JSON indexing and the aggregates JSON_OBJECTAGG and JSON_ARRAYAGG. This is a real difference from 2022, which only knew JSON as nvarchar with associated functions but had no dedicated type.
  • Regular expressions in T-SQL. New functions are REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE.
-- Validate email addresses without CLR or LIKE acrobatics
SELECT customer_id, email
FROM   dbo.customers
WHERE  NOT REGEXP_LIKE(email, '^[^@\s]+@[^@\s]+\.[^@\s]+$');
  • REST from the database. sp_invoke_external_rest_endpoint lets you call REST and GraphQL endpoints. In addition, the Data API builder (DAB) provides automatically generated REST/GraphQL endpoints, and there is a SQL MCP Server.
  • Change Event Streaming (Preview). DML changes (insert, update, delete) are sent in near real time as a CloudEvent (JSON or Avro) to Azure Event Hubs. This also requires PREVIEW_FEATURES.
  • Fuzzy string matching (Preview). New functions are EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE and JARO_WINKLER_SIMILARITY – again behind PREVIEW_FEATURES.
  • Further language additions. The length parameter of SUBSTRING is now optional (ANSI-aligned), DATEADD accepts bigint, and there are UNISTR, the PRODUCT aggregate, CURRENT_DATE, BASE64_ENCODE/BASE64_DECODE and string concatenation with ||.
-- Call a REST endpoint directly from T-SQL
DECLARE @response NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint
     @url     = N'https://api.example.com/v1/status',
     @method  = N'GET',
     @response = @response OUTPUT;
SELECT @response;

Engine and performance

  • Optimized locking reduces blocking and lock memory consumption and avoids lock escalation. What is new is that it is available on on-premises SQL Server – in Enterprise and Standard, but not Express.
  • Tempdb space resource governance prevents runaway workloads from filling tempdb (Enterprise and Standard).
  • Accelerated Database Recovery (ADR) now also applies in tempdb.
  • ZSTD is added as a new, faster backup compression algorithm.
  • Optimized sp_executesql mitigates compilation storms by letting sp_executesql invocations behave like objects (such as stored procedures) from a compilation standpoint.
  • There are also persisted statistics for readable secondary replicas, columnstore improvements (ordered nonclustered columnstore indexes, online build) and time-bound extended events sessions.

In Intelligent Query Processing (IQP), Optional Parameter Plan Optimization (OPPO) builds on the multiplan infrastructure of Parameter Sensitive Plan Optimization (PSPO) introduced with 2022 and generates multiple plans per statement. Added to that are cardinality estimation feedback for expressions, DOP feedback on by default, Query Store for readable secondary replicas (on by default) and the new ABORT_QUERY_EXECUTION hint.

Availability and security

For high availability, full and differential backups are now possible on any secondary replica – previously only copy-only backups were allowed there. Backups can be written to immutable blob storage (backup to URL), and TLS 1.3 with TDS 8.0 is broadly rolled out (availability groups, failover cluster instances, replication, log shipping, tools). A distributed AG is now also possible between two contained AGs.

On the security side, password hashes are produced with PBKDF2 by default (aligned with NIST SP 800-63b), there is OAEP padding for RSA, and security cache invalidation is more targeted per login. Managed identity with Microsoft Entra authentication is possible but requires "SQL Server enabled by Azure Arc".

Microsoft Fabric

In place of Synapse Link comes mirroring in Fabric: continuous, near-real-time replication of a SQL Server 2025 database (on-premises or VM) to OneLake. The result is a read-only copy for analytics without a classic ETL pipeline. Important for existing environments: Synapse Link is discontinued in 2025, and Microsoft explicitly names Fabric mirroring as the replacement.

Editions and licensing – what changes

The edition and licensing changes are at least as relevant to an upgrade decision as the new features:

Area SQL Server 2022 SQL Server 2025
Standard – max. compute 4 sockets / 24 cores lesser of 4 sockets or 32 cores
Standard – buffer pool per instance previous value 256 GB
Resource Governor Enterprise only also Standard (same functionality)
Express – max. relational DB size 10 GB 50 GB
Express with Advanced Services separate variant discontinued (features now in Express)
Web Edition available discontinued
Free dev/test licenses Developer Standard Developer and Enterprise Developer

Notably, the AI features are broadly available: the native vector type, embedding generation, external models, the DiskANN index and chunking are available in all editions according to the editions page – including Express and Standard, while the DiskANN index, local ONNX models, change event streaming and fuzzy string matching require PREVIEW_FEATURES. The native JSON type with indexing, regular expressions and external REST endpoint invocation are also available in all editions. Optimized locking and tempdb space resource governance remain reserved for Enterprise and Standard.

Discontinued in 2025 are, among others: Data Quality Services (DQS), Master Data Services (MDS), Synapse Link, Purview access policies, the Web Edition and "Express with Advanced Services". Deprecated and slated for future removal are Hot Add CPU as well as lightweight pooling / fiber mode; database mirroring remains deprecated as before. On-premises reporting services are consolidated under Power BI Report Server starting with 2025.

Advantages of moving to 2025

  • AI without a separate vector store. Embeddings, vector search and external model integration run within the database engine. Vectors sit transactionally consistent alongside business data, and a dedicated specialized vector store becomes unnecessary for many scenarios.
  • More modern T-SQL. A native JSON type, regular expressions and direct REST calls reduce the workarounds that previously required CLR, external services or awkward string logic.
  • More performance and stability out of the box. Optimized locking, optimized sp_executesql, ZSTD compression and the IQP enhancements act on existing workloads, often without code changes.
  • More capability in cheaper editions. Standard gains more cores and memory plus the Resource Governor; Express quintuples the maximum database size to 50 GB. The free Developer editions make clean test and staging environments easier.
  • More robust availability and security. Full and differential backups on secondary replicas, immutable backups, end-to-end TLS 1.3 and PBKDF2 password hashes raise the protection level.

Disadvantages and pitfalls

  • Many headline features are Preview. The vector index and VECTOR_SEARCH, change event streaming, fuzzy string matching and local ONNX models all require the PREVIEW_FEATURES database scoped configuration. For production use with a support commitment, this needs careful review.
  • Discontinued components force rework. DQS, MDS, Synapse Link, Purview access policies and the Web Edition are gone. Anyone building on them needs a replacement plan before upgrading – for the analytics path, for instance, Fabric mirroring instead of Synapse Link.
  • Breaking changes. SQL Server 2025 introduces breaking changes in areas including linked servers, replication, log shipping and PolyBase. These areas should be tested thoroughly before an upgrade.
  • Azure Arc dependency. Managed identity with Microsoft Entra authentication requires "SQL Server enabled by Azure Arc". Purely isolated on-premises environments cannot easily benefit from it.
  • External models mean data, latency and cost. Sourcing embeddings or inference via an external endpoint (such as Azure OpenAI) means accounting for data egress, response times and ongoing cost.
  • Maturity of a fresh release. Despite the available cumulative updates, 2025 is still a young major version. Critical systems benefit from a validation period before being switched over.

Conclusion

For teams that want to implement AI and vector scenarios close to their data, or that benefit from native JSON, regular expressions and the engine improvements, SQL Server 2025 is a worthwhile step forward. The gains in Standard and Express also make the move attractive for many mid-sized environments.

Caution is warranted where systems build on discontinued components such as DQS, MDS or Synapse Link, where linked servers, replication, log shipping or PolyBase are involved, or where production use of the still-Preview AI features is planned. The rule here: test first, then migrate. Anyone planning should factor in the support horizon (mainstream until January 2031) and the still-young maturity of the release.

Do you have questions about migration, edition choice or introducing vector search into your existing data architecture? Feel free to write to us at info@yurtbay.dev.

Sources (Microsoft)

Discuss your project