Published: 2026-08-25

What's New in SQL Server 2025

SQL Server 2025 (17.x) went generally available on November 18, 2025, and it is the most consequential release for developers since 2016. Two new data types carry the headline — a native binary JSON type (which ships in GA builds but which Microsoft's docs still label preview on-premises) and a VECTOR type with a distance function and approximate similarity search — alongside AI model management in T-SQL, seven REGEXP_ functions, and optimized locking. It is also a release that removes things: Data Quality Services, Master Data Services, Synapse Link and the Web edition are all gone. Below is what actually shipped, what is genuinely GA versus what still needs a preview flag, and — first-hand — what it takes for a desktop client to let you design a VECTOR column. Everything here is checked against Microsoft's own documentation as of August 2026.

The short version

Two different kinds of "preview" appear above, and the difference matters. Some features are gated — they don't work until you turn on the PREVIEW_FEATURES database-scoped configuration. Others, including everything JSON, are labelled preview in the documentation but work in a stock GA build with no flag. Neither is recommended for production, but only the first kind is something you can check for from T-SQL.

The native JSON type

For nearly a decade SQL Server's answer to JSON was "NVARCHAR(MAX) plus helper functions". SQL Server 2025 finally ships a real type. The json data type stores documents in a native binary format, internally UTF-8 with the Latin1_General_100_BIN2_UTF8 collation, and Microsoft's stated wins are more efficient reads (the document is already parsed), more efficient writes (a query can update individual values without touching the whole document), and better compression — with no compatibility break for existing code.

Microsoft's documentation still labels the type preview on SQL Server 2025 even though it needs no preview flag — see the status section below before you commit to it on-premises. It takes no parameters, and unlike VECTOR it accepts CHECK and DEFAULT constraints:

-- SQL Server 2025
CREATE TABLE dbo.Orders
(
    order_id      INT IDENTITY PRIMARY KEY,
    order_details JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(order_details, '$.basket') = 1)
);

-- In-place modification (preview on SQL Server 2025)
UPDATE dbo.Orders
SET order_details.modify('$.status', 'shipped')
WHERE order_id = 1;

The modify method is the preferred way to edit a json column and optimises for in-place edits: for strings, when the new value is no longer than the existing one; for numbers, when the new value is the same type or fits the range of the existing one. Microsoft documents it as currently in preview and only available on SQL Server 2025, so treat the sample above as forward-looking rather than production-ready. Two aggregates also arrive — JSON_OBJECTAGG and JSON_ARRAYAGG — though they too are GA only on Azure SQL and are documented as preview on SQL Server 2025. OPENJSON() does accept the json type directly in SQL Server 2025, which it does not on every other platform.

The sharp edges

• Objects and arrays only. The type conforms to IETF RFC 4627, so top-level scalars are rejected: DECLARE @x JSON = '1234.56' and '"contoso"' and 'true' are all invalid, while '{}', '[]' and a SQL NULL are valid.
• No implicit conversion, exactly like xml. You can CAST explicitly to and from char, nchar, varchar and nvarchar only. It can't go in a sql_variant, and CREATE TYPE aliases aren't allowed.
• Migration is one-way. You can ALTER TABLE an existing varchar(max) column to json, but you can't ALTER a json column back to a string or binary type.
• Drivers may not see it. sp_describe_first_result_set doesn't return the json type correctly, so many clients see varchar(max) (TDS 7.4+, with the UTF-8 binary collation) or nvarchar(max) (below TDS 7.4) instead.
• It can't be an index key column — though it can be an included column, or appear in a filtered index's WHERE clause.

Documented size limits

Is the JSON type GA or preview? (the confusing part)

This is the single most-asked question about SQL Server 2025 JSON, and the honest answer is more awkward than either "yes" or "no". Microsoft's currently-maintained documentation still labels the native json type as preview on SQL Server 2025 (17.x), while calling it generally available on Azure SQL Database and Azure SQL Managed Instance. The Work with JSON Data overview — touched as recently as August 24, 2026 — states exactly that contrast, and the json data type reference repeats it. There has been no Microsoft announcement of on-premises GA for the type.

What makes it confusing is that the type is not gated. It ships in stock GA builds, it works without enabling PREVIEW_FEATURES, and it doesn't appear in the release notes' list of flag-gated preview features. But absence from that list doesn't imply GA — CREATE JSON INDEX, JSON_CONTAINS and the .modify() method are all missing from it too, and Microsoft explicitly labels every one of them preview. The list enumerates what needs the flag, not what is generally available.

The same overview page names the full set of JSON items that are "all currently in preview" on SQL Server 2025: the .modify() method, CREATE JSON INDEX, JSON_CONTAINS, ANSI SQL path-expression array wildcards, and the WITH ARRAY WRAPPER clause on JSON_QUERY. Separately, JSON_OBJECTAGG and JSON_ARRAYAGG are documented as GA on Azure SQL and Fabric Data Warehouse but in preview on SQL Server 2025.

The practical reading: if you are on Azure SQL Database or Managed Instance, the type and the aggregates are supported and you can build on them. If you are on-premises, everything works today, but you are using something Microsoft has not yet committed to as generally available — which matters for support agreements and for anything you would be unhappy to see change behaviour in a cumulative update.

CREATE JSON INDEX — preview, and offline-only

The JSON index is the first engine-level index SQL Server has shipped for document workloads — conceptually closer to PostgreSQL's GIN over jsonb than to a per-path B-tree. Without a FOR clause it recursively catalogs every key and value; with FOR it scopes to specific SQL/JSON paths.

CREATE JSON INDEX json_content_index
    ON dbo.docs (content)
    FOR ('$.a', '$.b') WITH (FILLFACTOR = 80);

-- Array-heavy documents get their own option
CREATE JSON INDEX CustomersJsonIndex
    ON dbo.Customers (customer_info) WITH (OPTIMIZE_FOR_ARRAY_SEARCH = ON);

The optimiser can use it for JSON_VALUE, JSON_PATH_EXISTS and JSON_CONTAINS predicates — but only with equality comparison; LIKE and IS [NOT] NULL are documented as not currently supported. Constraints worth knowing before you plan around it: the table needs a clustering key; only one JSON index per json column (up to 249 per table); creation and rebuild are offline only, taking a Sch-M lock on the table; paths in FOR can't overlap, so $.a and $.a.b together is an error; no computed columns, views, table-valued variables or memory-optimized tables; no index hints; and the DATA_COMPRESSION option isn't supported. MAXDOP is accepted syntactically but the build always uses a single processor.

For the full query-side story — OPENJSON, JSON_VALUE, the NVARCHAR(MAX) era, and how this compares to MongoDB — see our SQL Server JSON support guide, or the cross-engine JSON in relational databases comparison.

The VECTOR type

The vector data type stores embeddings in an optimised binary format but presents them as JSON arrays, so '[0.1, 2, 30]' is a valid literal. Each element is a single-precision 4-byte float by default.

CREATE TABLE dbo.Documents
(
    id        INT IDENTITY PRIMARY KEY,
    body      JSON        NOT NULL,
    embedding VECTOR(1536) NOT NULL   -- text-embedding-3-small width
);

INSERT INTO dbo.Documents (body, embedding)
VALUES ('{"title":"Alan Turing"}', '[0.1, 2, 30, ...]');

The dimension argument is mandatory and bounded. A vector must have at least one dimension, and the maximum is 1998 for the default float32 base type. There is no VECTOR(MAX). Half-precision float16 vectors — VECTOR(3, float16) — double that ceiling to 3996 dimensions and halve the storage, but they are a preview feature requiring ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON. Storage is an 8-byte header plus 4 bytes per dimension, so Microsoft's worked example puts a 1024-dimension vector at 4,104 bytes.

Distance and search

VECTOR_DISTANCE takes a metric name and two vectors, returns a float, and is always exact — it never uses a vector index, even when one exists:

DECLARE @v AS VECTOR(1536) = (SELECT embedding FROM dbo.Documents WHERE id = 1);

SELECT TOP (10) id, VECTOR_DISTANCE('cosine', @v, embedding) AS distance
FROM dbo.Documents
ORDER BY distance;

The three supported metrics are cosine (range 0–2, where 0 is identical and 2 is opposing), euclidean (0 to infinity, 0 identical), and dot — which returns the negative dot product, so smaller numbers mean more similar. Alongside it are VECTOR_NORM, VECTOR_NORMALIZE and VECTORPROPERTY (which reads back Dimensions or BaseType). For approximate nearest-neighbour search you need CREATE VECTOR INDEX and VECTOR_SEARCH — both still preview, both gated behind PREVIEW_FEATURES.

What a vector column can't do

This list matters more than the feature list, because it constrains schema design. Per Microsoft's documentation, vector columns support no column-level constraints except NULL/NOT NULL — no DEFAULT, no CHECK, no PRIMARY KEY or FOREIGN KEY, no uniqueness. They support no comparison, arithmetic, concatenation or compound-assignment operators at all. They can't live in memory-optimized tables, can't be a B-tree or columnstore index key (included column only), can't be used with Always Encrypted or sql_variant, and can't have a CREATE TYPE alias. sp_verify_database_ledger errors outright if the database contains one.

Two practical consequences. First, older drivers see vectors as varchar(max) JSON arrays — which is by design, so every language works — while native binary transport needs Microsoft.Data.SqlClient 6.1.0 or the Microsoft JDBC Driver 13.1.0 Preview, on TDS 7.4 or higher. Second, SQL Server 2025 adds vector_dimensions, vector_base_type and vector_base_type_desc columns to sys.columns — which is the only reliable way to read a column's dimension back, since max_length reports storage bytes. That detail turns out to matter a lot if you write tooling; see below.

AI models as database objects

The part that ties JSON and vectors together is that SQL Server 2025 can call an embedding endpoint itself. CREATE EXTERNAL MODEL stores the location, authentication method and purpose of an inference endpoint as a database object, and AI_GENERATE_EMBEDDINGS uses it:

CREATE EXTERNAL MODEL MyEmbeddingModel
WITH (
    LOCATION = 'https://my-endpoint.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings',
    API_FORMAT = 'Azure OpenAI',
    MODEL_TYPE = EMBEDDINGS,
    MODEL = 'text-embedding-3-small',
    CREDENTIAL = [https://my-endpoint.openai.azure.com]
);

UPDATE dbo.Documents
SET embedding = AI_GENERATE_EMBEDDINGS(JSON_VALUE(body, '$.title') USE MODEL MyEmbeddingModel);

AI_GENERATE_CHUNKS splits long text into fragments by type and size, which is the other half of a retrieval pipeline. There is also ALTER/DROP EXTERNAL MODEL, sp_invoke_external_rest_endpoint for arbitrary REST calls, a SQL MCP Server built on Data API Builder, and GitHub Copilot inside SSMS. If you want agents talking to your database, our take on why a SQL IDE needs an MCP server and the guide to connecting AI agents to a database cover the client side of that.

Regular expressions, at last

Seven functions, no CLR assembly required: REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES (tabular captured substrings) and REGEXP_SPLIT_TO_TABLE. These are GA.

SELECT email
FROM dbo.Customers
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') = 0;

Alongside them the T-SQL surface picks up the ANSI || string-concatenation operator, CURRENT_DATE, a PRODUCT() aggregate, UNISTR, BASE64_ENCODE / BASE64_DECODE, an optional length argument on SUBSTRING, and bigint support in DATEADD. Fuzzy string matching (EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE, JARO_WINKLER_SIMILARITY) is there too, but preview-gated.

Engine and operations changes worth the upgrade on their own

• Optimized locking — reduces blocking and lock memory consumption and avoids lock escalation. For a busy OLTP system this is the single biggest reason to upgrade even if you never touch a vector.
• Tempdb space resource governance — stops a runaway workload from consuming all of tempdb, plus accelerated database recovery in tempdb and tmpfs support for tempdb on Linux.
• ZSTD backup compression — a faster, more effective algorithm than the existing option; and you can now back up to immutable blob storage.
• Full and differential backups on secondary replicas. Previously secondaries were limited to copy-only backups.
• Optimized sp_executesql — lets sp_executesql batches serialise compilation like stored procedures do, blunting compilation storms.
• Persisted statistics for readable secondaries, and Query Store for secondary replicas plus DOP feedback are now on by default.
• Intelligent query processing adds cardinality-estimation feedback for expressions, optional parameter plan optimization (OPPO), and an ABORT_QUERY_EXECUTION query-store hint that blocks known-bad queries from running at all.
• Security — PBKDF2 password hashing on by default (NIST SP 800-63b), OAEP padding for RSA, per-login security cache invalidation, and TLS 1.3 with TDS 8.0 across Agent, sqlcmd, bcp, replication, log shipping and availability groups.

Editions, and what's been removed

The licensing-shaped changes are easy to miss and expensive to miss. Standard edition rises to the lesser of 4 sockets or 32 cores with a 256 GB buffer pool, and — notably — gains Resource Governor with the same functionality as Enterprise. Web edition is discontinued. Express raises its maximum database size to 50 GB and folds in everything that used to require Express with Advanced Services (itself discontinued). Two new free editions appear for development use: Standard Developer and Enterprise Developer.

Discontinued in 17.x: Data Quality Services, Master Data Services, Synapse Link (use Fabric mirroring instead) and Purview access policies. Deprecated and slated for removal: hot add CPU and lightweight pooling / fiber mode. On-premises reporting is consolidated under Power BI Report Server. If DQS is installed, the upgrade fails until you uninstall it — check the breaking changes list before you plan a migration, since linked servers, replication, log shipping and PolyBase all have behaviour changes.

What still needs PREVIEW_FEATURES

SQL Server 2025 introduced a database-scoped configuration that lets you opt into features scheduled to go GA in a later cumulative update. As of the release notes dated August 19, 2026, the full preview list is still marked "RTM" — meaning nothing has graduated yet:

ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;

• Change event streaming
• Fuzzy string matching — EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE, JARO_WINKLER_SIMILARITY
• Half-precision (2-byte) float16 vectors
• Vector index, CREATE VECTOR INDEX and VECTOR_SEARCH

That list is not the same as "everything else is GA." It enumerates what needs the flag, nothing more. The whole JSON family — the json type itself, .modify(), CREATE JSON INDEX, JSON_CONTAINS, the JSON aggregates — is absent from it and yet documented as preview on SQL Server 2025; those features simply work without a flag. Microsoft's blanket caution applies to both kinds: preview features aren't recommended for production.

Designing JSON and VECTOR columns from a desktop client

Jam SQL Studio is a cross-platform desktop client (Mac, Windows, Linux) for SQL Server, PostgreSQL, MySQL, Oracle and SQLite. New data types are exactly the kind of change that quietly breaks GUI tools, because a visual table designer has to know each type's parameter shape — and VECTOR's shape is unlike anything SQL Server had before. Here is what we actually had to do for 1.4.23, and what still doesn't work.

• Both types are in the SQL Server type list in the Table Designer. JSON is parameterless, so the designer shows no length and no precision input for it — picking it emits [Payload] JSON NOT NULL.
• VECTOR reuses the single numeric input, but relabelled. Selecting it changes the field label from Length to Dimensions, seeds it with 1536 (the output width of text-embedding-ada-002 and text-embedding-3-small), and bounds the input to the documented 1–1998 range for float32.
• It never emits VECTOR(MAX). This was the real trap. Our DDL generator treats a missing length on a character type as MAX — NVARCHAR with a null length becomes NVARCHAR(MAX). Applied naively to VECTOR, that produces syntax the engine rejects. VECTOR is now handled ahead of the length types, and when the dimension is missing or out of range the designer emits the bare type name so the server raises a clear error, rather than inventing a number and silently creating a wrong-sized column.
• The byte-halving rule doesn't apply. SQL Server reports NVARCHAR lengths in bytes, so the designer halves them for display — 200 bytes renders as NVARCHAR(100). A vector's dimension count is not a byte count, so VECTOR(768) stays 768.
• Opening an existing vector column converts bytes back to dimensions. sys.columns.max_length reports a vector's storage size, so a VECTOR(100) column arrives as 408 — a number that looks like a perfectly valid dimension count and would have quietly become VECTOR(408) on the next edit. The designer reverses the 8-byte-header-plus-4-bytes-per-element layout and accepts the result only when it divides exactly and falls inside 1–1998.
• An empty or out-of-range dimension blocks Save. Because VECTOR has no default and no MAX, clearing the field shows an inline error instead of writing a column the server would reject.
• JSON columns were already first-class on the read side. Jam renders them as collapsible trees, and the Table Explorer filter chip exposes path-aware operators against the native type just as it does against the NVARCHAR(MAX)-declared-as-JSON pattern used on SQL Server 2016–2022. See the JSON Columns guide.

What we haven't wired up

Being explicit about the gaps rather than implying coverage. Half-precision vectors don't round-trip. SQL Server reports a vector column's sys.columns.max_length in storage bytes, not dimensions — a float32 vector is an 8-byte header plus 4 bytes per element, so VECTOR(100) comes back as 408. The designer converts that back, but only for the default float32 base type, and only when the arithmetic is exact and lands inside 1–1998. A float16 vector has 2-byte elements, so the same formula would yield a plausible but wrong number; recovering it properly needs sys.columns.vector_base_type, which exists only on 17.x and would break the same metadata query against SQL Server 2016–2022 without version gating. In that case the designer reports no dimension at all and the generator emits a bare VECTOR, so the server rejects the edit rather than silently resizing the column. Since float16 is itself preview-gated, this is a narrow gap — but it is a real one.

There is also no vector-similarity UI: no embedding generator, no VECTOR_SEARCH query builder, and no visualisation of a distance ranking. CREATE JSON INDEX and CREATE VECTOR INDEX aren't surfaced in any dialog either — write them in the Query Editor.

If you're evaluating clients for SQL Server 2025 on a non-Windows machine, SSMS alternatives for Mac and running SQL Server on macOS cover the surrounding setup. For the other engines' recent releases, see What's New in PostgreSQL 19 and What's New in MySQL 26.7.

FAQ

When was SQL Server 2025 released?

SQL Server 2025 (17.x) reached general availability on November 18, 2025, announced at Microsoft Ignite. It has shipped monthly cumulative updates since: CU1 on January 29, 2026 through CU8 (build 17.0.4075.5) on August 13, 2026. Data Quality Services, Master Data Services and Synapse Link are discontinued in this release, and Web edition is gone.

What are the main new features in SQL Server 2025?

The headline additions are the native binary JSON data type with JSON_OBJECTAGG and JSON_ARRAYAGG aggregates, which Microsoft still labels preview on SQL Server 2025, and the VECTOR data type with VECTOR_DISTANCE and related vector functions, which is generally available. Alongside them: AI model management in T-SQL through CREATE EXTERNAL MODEL plus AI_GENERATE_EMBEDDINGS and AI_GENERATE_CHUNKS, and seven REGEXP_ functions. On the engine side: optimized locking, tempdb space resource governance, accelerated database recovery in tempdb, ZSTD backup compression, and full and differential backups on secondary replicas. Standard edition also gains Resource Governor and rises to 32 cores and a 256 GB buffer pool.

Is the SQL Server 2025 JSON data type generally available?

Not on-premises, according to Microsoft's current documentation. The native json type is generally available on Azure SQL Database and Azure SQL Managed Instance, but both the Work with JSON Data overview and the json data type reference still label it in preview for SQL Server 2025 (17.x), and no on-premises GA has been announced. The type is not gated, though: it works in a stock GA build without enabling PREVIEW_FEATURES. The .modify() method, CREATE JSON INDEX, JSON_CONTAINS, and the JSON_OBJECTAGG and JSON_ARRAYAGG aggregates are all likewise documented as preview on SQL Server 2025.

How many dimensions can a SQL Server 2025 VECTOR column have?

A vector must have at least one dimension and the maximum is 1998 for the default float32 base type, where each element is a single-precision 4-byte float. Half-precision float16 vectors allow up to 3996 dimensions, but they are a preview feature that requires the PREVIEW_FEATURES database scoped configuration. The syntax is VECTOR(dimensions) or VECTOR(dimensions, base_type), and the dimension argument is mandatory: there is no VECTOR(MAX).

Which SQL Server 2025 features still require PREVIEW_FEATURES?

As of the release notes dated August 19, 2026: change event streaming, fuzzy string matching with EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE and JARO_WINKLER_SIMILARITY, half-precision float16 vectors, and approximate vector search through the vector index, CREATE VECTOR INDEX and VECTOR_SEARCH. All are still at RTM status, so none has graduated in a cumulative update yet. Preview features are not recommended for production.

Can I create JSON and VECTOR columns from a GUI on Mac or Linux?

Yes. Jam SQL Studio 1.4.23 adds both types to the SQL Server list in its visual Table Designer on macOS, Windows and Linux. JSON takes no parameters so no length input appears; picking VECTOR relabels the numeric input to Dimensions, seeds it with 1536 and bounds it to the supported 1 to 1998 range. The generated DDL reads VECTOR(1536), and because VECTOR has no MAX form the designer emits the bare type name rather than an invented dimension when the value is missing or out of range.