Azure SQL and SQL Server 2025 both have a native VECTOR type, a DiskANN index, and VECTOR_SEARCH. The documentation covers the syntax for each one. It does not cover what happens when you put them together and point them at a real codebase, which is where most of the work turned out to be.
This is the whole path: source files in, ranked answers out. C# and .NET, one database, no separate vector store. Everything here runs on both platforms, and the one place they genuinely diverge is section 5, which is the section worth reading. The live deployment this came out of is Azure SQL serverless. The same build runs against self-hosted SQL Server 2025 on a workstation.
What you end up with
A query runs three retrieval legs at once. Approximate nearest neighbour over a DiskANN index. Full-text over the chunk table. Full-text over the file table. The three ranked lists get fused with reciprocal rank fusion inside a stored procedure, a cross-encoder reranks the survivors, and a relevance gate throws out results that are nearest-neighbour artifacts rather than answers.
Everything lives in one database. That is the point of doing it this way. No Qdrant to run, no sync job, no second thing to back up.
1. The schema
Two tables. Files, and chunks of files.
CREATE TABLE dbo.CodeChunks (
Id INT IDENTITY(1,1) PRIMARY KEY,
CodeFileId INT NOT NULL REFERENCES dbo.CodeFiles(Id) ON DELETE CASCADE,
ChunkKey NVARCHAR(500) NOT NULL UNIQUE,
ChunkContent NVARCHAR(MAX) NOT NULL,
Embedding VECTOR(1536) NULL
);
The dimension is baked into the column type. Change embedding models and you change the schema. Plan for that now, because the failure mode later is silent: a corpus written at 1024 and queried at 1536 does not error, it returns nonsense.
Write vectors by casting JSON server-side.
INSERT INTO dbo.CodeChunks (..., Embedding)
VALUES (..., CAST(@embedding AS VECTOR(1536)));
@embedding is a float[] serialized with System.Text.Json. There is no client-side vector parameter type to reach for.
2. Chunking
Fixed-size windows are the obvious approach and they are wrong for code. A window that splits a method in half produces two chunks, neither of which is the method.
Use the syntax tree. Roslyn hands you MethodDeclarationSyntax and friends, so chunk on those boundaries.
- One shell chunk per type: usings, namespace, XML docs, the declaration line, fields, constants, auto-properties. Method bodies excluded.
- One chunk per member with a real body.
- A token budget with a floor and a ceiling. 200 to 400 estimated tokens worked here.
Two cases break the clean rule.
Methods over the ceiling. Split them, but split on top-level StatementSyntax boundaries, never mid-statement. Greedily pack statements until the next one would blow the budget. A chunk that runs slightly fat beats a chunk that lost a line. Prefix each part with a comment carrying the signature and name them Foo~part2of3. The embedding text does not have to be valid C#.
Members under the floor. A three-line property embeds to noise. Coalesce runs of tiny members into one chunk.
Then give each chunk roughly fifty tokens of the next chunk's opening, under a marker comment. Cheap continuity, and it means a question whose answer straddles two members still hits one of them.
Estimate tokens with word count times a constant rather than running the real tokenizer. This runs once per chunk inside a build that is already slow. It does not need to be exact, it needs to be fast and never under-count.
3. Embedding
Two things matter and neither one is the model choice.
The dimension is a single global and it has to match everywhere. If your indexer prefers a local ONNX model and your server prefers a hosted endpoint, and those two have different widths, you will index at one width and query at another. Nothing throws. Put a loud warning at startup when both are configured.
A failed parse must throw, not return zeros. An all-zero vector survives L2 normalization untouched, because a magnitude of zero short-circuits, and then produces garbage cosine distances against every row in the table. Validate the length at the source.
On batching, measure it. Batching pads every sequence to the longest one in the batch. On a corpus with uneven chunk sizes that lost to one-at-a-time here, roughly three minutes against four and a half. Your corpus may differ. The point is that the obvious optimization is not free.
On concurrency, pick the limit from whichever side is the bottleneck. Local model on your machine, parallelize across cores. Remote endpoint, cap at what the server can take. A strong client pointed at a small server makes it thrash and throughput drops.
4. The DiskANN index, and when to build it
CREATE VECTOR INDEX IX_CodeChunks_Embedding
ON dbo.CodeChunks(Embedding)
WITH (metric = 'cosine', type = 'diskann');
Build this after the data is loaded, not alongside the schema. DiskANN needs a meaningful number of non-null vectors before it will build at all.
Before you spend an hour embedding, prove the server can actually do this. Do not read ProductVersion, because Azure SQL is evergreen and reports 12.0 forever. Cast a literal instead.
SELECT CAST('[1,2,3]' AS VECTOR(3));
Then create a throwaway table with a real DiskANN index on it and drop it. Ten seconds of preflight against an hour of wasted embedding.
5. Querying, and the one place the two platforms diverge
VECTOR_SEARCH has two spellings, and which one you need depends on the DiskANN index build version, not the product. In practice that splits along platform lines: Azure SQL and Fabric build version 3 indexes, self-hosted SQL Server 2025 builds earlier ones.
-- version 3 and up (Azure SQL, Fabric)
SELECT TOP (200) WITH APPROXIMATE ...
-- earlier builds (SQL Server 2025 self-hosted)
... VECTOR_SEARCH(..., TOP_N = 200)
If you only ever deploy to one of them you can hardcode the right one and stop reading here. If you develop locally and deploy to Azure, which is the normal arrangement, you need both in the same codebase.
Send the wrong one and you get Msg 42274. Both spellings are documented. Three things about them are not obvious from the docs.
One. WITH APPROXIMATE is load-bearing. A bare TOP (200) parses fine and runs fine, and performs an exact kNN scan of the whole table without touching the index. The engine raises a warning, but if your data layer is not surfacing warnings you will never see it. You get correct results, slowly, forever.
Two. You cannot reliably branch on the version. It comes from JSON_VALUE(sys.vector_indexes.build_parameters, '$.Version'), and self-hosted SQL 2025 writes StartId, L, M, R and no Version key at all. A missing key means the older spelling. Default it the other way and every self-hosted install silently degrades to full-text only, which is exactly what happened here.
Three. Because the two spellings are mutually unparseable, you cannot put them in one procedure body behind an IF. The branch the engine cannot parse fails at compile time. Build both as strings and dispatch with sp_executesql. Treat the version as a hint for which to try first, and catch the failure to try the other.
WITH APPROXIMATE also forces ORDER BY on the distance column alone, so tie-breaking has to happen afterward. Land the hits in a temp table and apply ROW_NUMBER() there. That has a second benefit: it keeps the dynamic SQL out of an INSERT ... EXEC, so callers can still INSERT ... EXEC your procedure, which cannot nest.
6. Fusing three legs
Reciprocal rank fusion, k = 60, entirely in T-SQL.
@VectorWeight * (1.0 / (60 + VectorRank))
+ @ChunkFtsWeight * (1.0 / (60 + ChunkFtsRank))
+ @FileFtsWeight * (1.0 / (60 + FileFtsRank))
Rank each leg independently, union the candidates, sum the contributions. RRF only needs ranks, so you never have to make a cosine distance and a full-text RANK comparable, which is the thing that makes score-normalization approaches fragile.
Keep the three components as separate output columns. You will want them in section 7.
7. Reranking, and why the gate matters more
A cross-encoder over the top thirty candidates was the single largest quality win here. Measured against swapping the embedding model, it was not close.
But reranking alone does not fix the real failure. Vector search always returns its nearest neighbours, however far away they are. Search for a term your corpus has never seen and you get back five confident-looking files containing none of the words.
So gate the results, in three parts.
- A relative floor, as a fraction of the top score. This one survives a model swap, because it only depends on the shape of one result set.
- An absolute floor on the top score itself. This one has to be recalibrated per reranker. Log the observed top score so you can tune it.
- A higher absolute floor applied only to hits with no lexical corroboration. This is what the separate RRF columns are for. A hit that no full-text leg found should clear a higher bar than one two legs agree on.
When everything fails the gate, return nothing. Empty is the correct answer. Do not fall back to the unfiltered list.
One caveat that bit me. If the reranker is unavailable, skip the gate entirely. Otherwise an outage turns into every search returning zero results, which looks like a data problem and is not. Relatedly, when you fall back to the pre-rerank order, emit descending pseudo-scores just under 1.0 rather than zeros. Zeros make an outage indistinguishable from "nothing is relevant."
8. Two things that only bite on Azure SQL serverless
The database reports 12.0 forever. Covered in section 4, and it is the reason you capability-test instead of version-check.
Full-text is not ready when the connection is. After a serverless resume, the engine accepts connections before the full-text filter daemon has come up. Your connection-level retry policy never sees this, because the connection succeeded. The command fails instead, and the engine reports it under more than one error number, so match on the message text for "filter daemon" or "FDHost" and retry at the command level. Log the actual SqlException.Number so you can add codes later.
Without that retry the affected queries are the first ones after a wake. On a public demo that is every cold visitor, and what they get is the vector leg silently missing.
9. Rebuilding without downtime
Build into CodeFiles_Staging and CodeChunks_Staging, then swap. Generate both table definitions from one parameterized string so live and staging cannot drift.
The swap is more steps than you would guess, because the full-text indexes, the vector index and the stored procedure are all bound to the live table's object id. Drop the dependents, rename live out, rename staging in, sp_rename every constraint and index back to the live names, rebuild the dependents.
Guard the promotion. If staging holds less than 95% of the files you listed, do not swap. Leave the good index alone and say so in the log. That guard is the entire recovery story: a build that dies partway never becomes live, so recovery is "run it again."
Make your unique keys stable across runs. Truncating a long key to fit the column and appending a hash of the full value works, as long as the hash is deterministic. FNV-1a is fine. Do not use GetHashCode, which is randomized per process.
10. What I would do differently
Instrument the build from day one. Every number worth having, stage timings, error counts, how close the staged count came to the promotion threshold, was already computed and written to a console nobody reads. The query side had telemetry in a table. The build side had nothing, so there was no way to answer "is this getting slower."
Surface warnings. Two of the worst failures here, the silent full scan and the silent full-text-only degradation, were both things the system knew about and did not say.
And measure before you tune. Swapping the embedding model changed recall and did nothing for ranking. The reranker changed everything. I would have guessed the other way around.
Working code, MIT: https://github.com/disisnoturbusiness/AzureDevOpsForager Four-minute walkthrough: https://www.loom.com/share/644b73ea68e34ded81a490c82a4cb99f
Originally published at azuredevops.aidataforager.com.
Top comments (0)