SQL Server 2025 public preview: what to test first
SQL Server 2025 is now in public preview. A practical test plan for vectors, AI functions in T-SQL, JSON, regular expressions, optimized locking and Fabric mirroring.
SQL Server 2025 is out in public preview, announced by Microsoft today and free to download and try. This is the first on-premises release in years where the headline is not just "faster engine" but new data types, new T-SQL functions and a different way of thinking about what belongs inside the database.
A preview is the right moment to test, not to deploy. You get to find out what fits your workloads before anyone asks you for an upgrade plan. So here is what is in the box, and how I would spend the first few evenings with it.
What Microsoft announced
The announcement on the SQL Server blog groups the release around AI, developer features, performance and cloud connections. The parts that matter most for hands-on testing:
- A native vector data type and built-in vector search, with DiskANN as the vector index.
- Model definitions in T-SQL that call AI services over REST, for example Azure OpenAI, OpenAI, Azure AI Foundry and Ollama.
- Embedding generation and text chunking directly in T-SQL.
- Native JSON support and regular expressions.
- Change Event Streaming, which streams changes from the transaction log to Azure Event Hubs.
- Optimized locking, built on transaction ID (TID) locking and lock after qualification (LAQ).
- Mirroring into Microsoft Fabric for near real-time analytics without ETL.
- Microsoft Entra managed identities, and management through Azure Arc.
SSMS 21 also went generally available on the same day, with Copilot in SSMS in preview.
Vectors and AI in T-SQL
This is the part getting the attention, and it is worth a proper look. The vector data type stores vectors in an optimized binary format but exposes them as JSON arrays. You compare them with functions such as VECTOR_DISTANCE, and you can create an approximate vector index for nearest neighbour search.
The other half is CREATE EXTERNAL MODEL. You define an AI inference endpoint as a database object, then call AI_GENERATE_EMBEDDINGS to turn text into vectors and AI_GENERATE_CHUNKS to split long text first. That means a full retrieval pipeline (chunk, embed, store, search) can live in T-SQL.
What I would test:
- Take a real table with a text column, product descriptions or support notes, not a demo dataset.
- Create an external model against an endpoint you already have access to.
- Chunk and embed a few thousand rows, and time it.
- Compare an exact VECTOR_DISTANCE query with the approximate index on the same data.
Watch the calls to the model endpoint closely. Every embedding is a round trip to an external service, and the announcement is clear that connected services can cost money based on usage.
JSON and regular expressions
These are less flashy but probably more useful to most of us next year. SQL Server 2025 adds a native JSON data type, stored in a binary format, alongside the existing JSON functions. There are also regular expression functions such as REGEXP_LIKE, REGEXP_REPLACE and REGEXP_SUBSTR.
If you have JSON sitting in nvarchar(max) columns today, copy one of those tables into the preview and convert it to the json type. Compare storage, then run your existing queries against it. For regex, find the ugliest LIKE and PATINDEX logic you own, usually in data cleansing or validation, and see how much of it collapses into one function call.
Optimized locking
With TID locking, a transaction holds a single lock on its transaction ID instead of many row and page locks until it commits. With LAQ, predicates are evaluated on the latest committed version of the row before a lock is taken, so writers updating different rows block each other less.
The documentation has a few conditions you need to know. In SQL Server 2025 it is off by default and is enabled per database with ALTER DATABASE ... SET OPTIMIZED_LOCKING = ON. Accelerated database recovery must be enabled first. The LAQ part only works when read committed snapshot isolation is on.
There is also a behaviour change to test for. With LAQ and RCSI, a workload that relies on strict ordering between concurrent transactions can produce different results than before. Microsoft documents an example where an update simply skips a row it would previously have waited for. If you have code like that, the preview is the place to find it.
Fabric mirroring and the rest
Mirroring SQL Server 2025 into Fabric is the piece that connects this release to the analytics side. If you already run Fabric, set up mirroring from a preview instance and check how the replicated data lands in OneLake. Change Event Streaming is the other integration to try if you have event-driven consumers today that rely on change data capture.
The announcement also mentions more than 50 engine enhancements. Those need a realistic workload to evaluate, so they belong in a later round.
What to do next
Keep the first round small and focused:
- Install the preview on a separate VM or container, never on a shared server.
- Restore a copy of one real database you know well.
- Run the vector and AI test on one table, with cost monitoring on the endpoint.
- Convert one JSON column and rewrite one regex-shaped query.
- Enable ADR, RCSI and optimized locking on the copy, and replay a concurrent workload.
- If you use Fabric, mirror the test database and query it from there.
Write down what you find. Preview builds change, but your notes on what fits your estate will stay useful.
Takeaway
After two decades in data, I have seen plenty of releases where the new features were for someone else. This one is different: JSON, regex and optimized locking help ordinary workloads, and the AI features are at least worth a structured look. Test against your own data, not the demos.
Which of these would you test first in your own environment?
Sources
Enjoyed this? Get the next one by email
Occasional emails about Microsoft Fabric, SQL Server, Power BI and Synapse.