Here’s my second post on new T-SQL features in SQL Server 2025, the first post is here.
I’m running the 2025 evaluation edition (17.0.700.9)
I’ve posted a script to GitHub with SQL for the new T-SQL features that I’ve covered.

*) Vector Data Type

The first feature we’ll cover in this post is the Vector data type.
I wrote a post on Vector Databases, which gives a short introduction to vector types. Microsoft also has an introduction to their vector features.
Basically, a vector is an array of numbers. Words and objects can be represented as a vector and searched or compared to one another.
We’ll be storing an array of floats with this data type.

We can either use a string with the array values, or use the JSON_ARRAY function to create that for us. We’ll cover both methods in this simple example:

DROP TABLE IF EXISTS dbo.VectorExample;

CREATE TABLE dbo.VectorExample(
	Id INT NOT NULL IDENTITY(1,1),
	VectorValue VECTOR(5) NOT NULL
);

INSERT INTO dbo.VectorExample(VectorValue)
VALUES ('[1, 2, 3, 4, 5]');

INSERT INTO dbo.VectorExample(VectorValue)
VALUES (JSON_ARRAY(6,7,8,9,10));

SELECT * FROM dbo.VectorExample;

When we define the vector column, we specify the max number of elements (Up to 1998). Each element is a single precision float.
We can’t create a standard index on a Vector column (But can be added as an included column). However, we can create a vector index.
No defaults, primary or foreign keys, no uniqueness enforced on vector columns.

There are also Vector Functions we can use to compare our values.

*) JSON Data Type

SQL Server 2025 introduces a JSON data type.
There was some JSON functionality in earlier SQL Server versions (I covered some in this post).
Before, we had to store JSON in a string column, but now there will be a JSON type that will require correct JSON.

Here’s an example of defining a column with the JSON data type, with a valid and an invalid insert attempt.
The subsequent examples use the same temp table declared in this step.

DROP TABLE IF EXISTS #JsonTest;

CREATE TABLE #JsonTest(
   ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   Document JSON NOT NULL
);

INSERT INTO #JsonTest(Document)
VALUES ('{ "name": "Jane Doe", "ID": 1, "Location": "Atlanta, GA, USA" }');

-- Invalid JSON - Insert will fail

INSERT INTO #JsonTest(Document)
VALUES ('{ "name": Jane Doe, "ID": 1, "Location": "Atlanta, GA, USA" }');

SELECT * FROM #JsonTest;

GO

We can also modify a value within the JSON document, rather than replacing the entire document.

-- Modify a value within JSON document

UPDATE #JsonTest SET
    Document.Modify('$.Location', 'Chattanooga, TN, USA')
WHERE ID = 1;

SELECT * FROM #JsonTest;

Existing JSON functions will accept the JSON data type.

-- Return 1 if document is valid JSON

SELECT ID, Document, ISJSON(Document) AS [IsJSON]
FROM #JsonTest;

We can parse document into one row for each attribute.

DECLARE @Document JSON;
SELECT @Document = Document FROM #JsonTest WHERE ID = 1;
SELECT * FROM OpenJSON(@Document);

GO

Microsoft has this list of available JSON functions.

*) Product

The last item to be covered is the PRODUCT command. We can use this to multiply all of the values in a specified column and return the result.

DROP TABLE IF EXISTS #ProductTest;

CREATE TABLE #ProductTest ([Value] INT NULL);

INSERT INTO #ProductTest ([Value])
VALUES
    (2), (4), (6), (8), (NULL);

SELECT PRODUCT([Value]) AS ValueProduct -- Returns 384
FROM #ProductTest;

GO

These were the T-SQL additions for SQL Server 2025 that I found most interesting. However, there are plenty of other features to check out. You can see my earlier post on installing the preview edition of SQL Server 2025, if you want to check it out for yourself.