What tables exist, what is written when, what the retrieval query has to carry, and how to prove it worked.
Verified 2026-08-28 against a live Azure SQL Database, General Purpose serverless, compatibility
level 170, and re-run 2026-09-03, which is where Msg 37579 comes from. Numbers not from those
runs are from Microsoft Learn, linked at the end.
Open references/pipeline.md when you sit down to write the ingest or the
re-embed: it holds the MERGE, the embedding loop, and what the two runs disagreed about.
What this owns
The tables, the ingest, what the retrieval query carries, and the grounding. Open
vector-search-azure-sql before writing any similarity query, for the vector type's restrictions
and the shape that reaches the index; embeddings-and-external-models before steps 3 and 4, for
the external model and the chunking and embedding functions; rls-multi-tenant for predicates,
with step 6's caveat. When no cloud endpoint is available, keep embedding application-side and
validate the production query and security behavior against Azure SQL Database before release.
The correction
An agent asked to build RAG here ports a vector store recipe: one table of chunk text with one embedding column, retrieved by the application's identity, top k pasted into the prompt. It works on the first demo. Two things are then wrong and neither raises an error.
1. The chunk table is a second copy of the source text. Whatever protected the original does
not follow the copy. Row level security on dbo.documents does not apply to dbo.document_chunks,
and neither do its grants, its tenant column or its soft delete flag. A retrieval that is otherwise
perfect puts text the asking user may not read into a prompt, and the model quotes it back. The
obvious fix is refused: a security policy cannot reference a table carrying a vector index,
Msg 37579, so the chunk table cannot have a predicate at all and the retrieval query's WHERE
clause is the whole boundary.
2. Nothing records which model produced a stored vector. A vector(n) column carries a
dimension and a base type and nothing else. The two most widely used embedding models both emit
1536 dimensions, so re-embedding half a corpus with another inserts cleanly and leaves two
incompatible geometries in one column. Distances across them are meaningless, no statement
fails, and the only symptom is that answers get worse.
1. Fix the dimension before the schema
The dimension follows the model, the ceiling is 1998, and it cannot be widened by ALTER COLUMN
even on an empty table. A 3072 dimension model does not fit and has to be asked for a shorter
vector at generation time. Write the number twice, as vector(n) and as the dimension
requested from the model; when they disagree the insert fails, the one loud failure here.
2. The schema
Two tables. Source rows stay authoritative and chunks are derived.
CREATE TABLE dbo.documents (
document_id INT NOT NULL IDENTITY PRIMARY KEY,
tenant_id INT NOT NULL,
title NVARCHAR(400) NOT NULL,
body NVARCHAR(MAX) NOT NULL,
updated_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE TABLE dbo.document_chunks (
chunk_id INT NOT NULL IDENTITY PRIMARY KEY, -- clustered; the index needs one
document_id INT NOT NULL REFERENCES dbo.documents (document_id),
tenant_id INT NOT NULL, -- carried, not joined for, step 6
ordinal INT NOT NULL,
chunk_text NVARCHAR(MAX) NOT NULL,
source_hash BINARY(32) NOT NULL, -- HASHBYTES over chunk_text
embed_model SYSNAME NOT NULL, -- provenance
embed_dims SMALLINT NOT NULL,
embedded_at DATETIME2(3) NULL, -- NULL exactly when embedding is
embedding VECTOR(1536) NULL
);
CREATE UNIQUE INDEX ux_chunks_identity ON dbo.document_chunks (document_id, ordinal);
CREATE INDEX ix_chunks_tenant ON dbo.document_chunks (tenant_id); -- the retrieval filter
CREATE INDEX ix_chunks_pending ON dbo.document_chunks (chunk_id)
WHERE embedded_at IS NULL; -- the work queue
Four things are load bearing:
-
chunk_idis a clustered primary key. Without a clustered index the vector index cannot be created at all, and that is discovered late. -
source_hashis how an ingest becomes idempotent. There is no equality operator on a vector, so a hash of the text is the only deduplication key.HASHBYTEStakes the wholenvarchar(max)chunk here, with no 8,000 byte limit. -
embed_modelandembed_dimsare the provenance failure 2 needs. They are the difference between re-embedding what changed and re-embedding everything because nobody knows what is in there. -
tenant_idis carried on the chunk, deliberately duplicated, so the retrieval filter needs no join. It is not backed by a predicate here and cannot be. Step 6.
embedded_at is NULL exactly when embedding is, because step 4 writes both in one statement,
which is what lets the work queue be a filtered index on an ordinary column.
3. Chunk
Chunk in the database when the text is already there, in the application when it arrives from
files. The chunking function and its size and overlap parameters are
embeddings-and-external-models.
- Chunk the same way every time. Changing chunk size later invalidates every stored embedding as surely as changing the model.
- Keep the ordinal. Grounding often needs a hit's neighbours, a cheap seek on
(document_id, ordinal)and impossible without it. - Store enough text to be an answer. A chunk too small retrieves well and grounds badly.
4. Embed in restartable batches
The model call is embeddings-and-external-models. The pipeline shape:
UPDATE TOP (200) c
SET embedding = AI_GENERATE_EMBEDDINGS(c.chunk_text USE MODEL text_embedder),
embed_model = N'text-embedding-3-small',
embed_dims = 1536,
embedded_at = SYSUTCDATETIME()
FROM dbo.document_chunks AS c
WHERE c.embedded_at IS NULL;
Loop until it affects zero rows. The filtered index makes that predicate a seek over a corpus that
is mostly done, where a set based update over the whole table re-embeds finished work and costs
money. Write the vector and its three provenance columns in the same statement: a vector
without provenance is what failure 2 is made of. It restarts safely because the identity is
(document_id, ordinal).
5. The index goes in after the load
CREATE VECTOR INDEX vi_document_chunks
ON dbo.document_chunks (embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');
- At least 100 rows carrying a non-null vector,
Msg 42266below that. So it belongs to the load, never the schema deployment: a package import creates objects before it loads rows. Drop the index before an export, create it after. - The metric is fixed at build time and the retrieval query has to name the same one.
- Everything works before the index exists, exactly and slowly, which is why an environment runs without one and only latency says so.
TRUNCATE TABLEis refused once it exists,Msg 42232. Drop, truncate, reload, recreate.
6. Retrieve with the filter in the query
One query does the similarity, the tenant filter and the ordering the prompt needs.
DECLARE @q VECTOR(1536) =
AI_GENERATE_EMBEDDINGS(N'the question' USE MODEL text_embedder);
SELECT chunk_id, document_id, ordinal, chunk_text, distance -- reading order, for the prompt
FROM (
SELECT TOP (8) WITH APPROXIMATE
c.chunk_id, c.document_id, c.ordinal, c.chunk_text, r.distance
FROM VECTOR_SEARCH(
TABLE = dbo.document_chunks AS c,
COLUMN = embedding,
SIMILAR_TO = @q,
METRIC = 'cosine'
) AS r
WHERE c.tenant_id = @tenant_id -- applied DURING the search, see below
ORDER BY r.distance
) AS hits
ORDER BY document_id, ordinal;
- Embed the question with the model that embedded the corpus. A question embedded by a different model returns results ranked by nothing.
- The filter belongs inside, not after. On a current index version the predicate is applied during the search, so a filtered top eight returns eight whenever eight qualify. On an earlier index version the search runs before any predicate: the eight nearest are chosen first and the filter then removes some, returning three, or none, with qualifying rows unread. Check 3 says which one you have.
- The inner
ORDER BYtakes the distance column ascending and nothing else. Addingc.ordinalfails withMsg 42271and dropping it fails withMsg 42248, which is why reading order is applied in the outer query. - No policy can back that
WHEREclause up.CREATE SECURITY POLICYon a table carrying a vector index is refused,Msg 37579, measured 2026-09-03 and documented nowhere on Microsoft Learn. Keep the policy ondbo.documents, where it still guards direct reads, and treat the filter above as security code: test that removing it changes the result. If a predicate on the retrieval path is non-negotiable, the sourced ways out are to drop the vector index, sinceVECTOR_SEARCHstill runs as a brute force scan without one, or to split the table so the index sits on an embeddings table carrying no policy and the protected text joins back, costing the filter its place inside the search. - Retrieve more than you ground on and re-rank the candidates, but never substitute a bigger k for a missing filter.
7. Ground the answer
- Pass the chunk text, the document identifier and the ordinal so the answer can cite it and a human can check the citation.
- Give the model permission to say the context does not contain the answer. A pipeline that always answers is a hallucination pipeline with extra steps.
- Do not paste the distance in as a confidence. It is a geometric distance, not a probability, and its scale differs by metric.
- Log the retrieved chunk identifiers with the answer. When a wrong answer is reported, the only useful question is what was retrieved, and nothing else answers it later.
8. Keep it current
When a source row changes, delete its chunks and re-chunk it: boundaries move when text
changes, so updating in place leaves orphans. MERGE on (document_id, ordinal) does it, and is
supported on a table carrying a current vector index, which maintains itself. The statement is in
the reference.
When the model or the chunking changes, everything is stale at once. Add a new column with its own provenance, backfill fully while retrieval still runs on the old, cut over, then drop the old column and index. A half migrated column is failure 2. Rebuild the vector index afterwards: the graph was built for the old distribution and recall degrades with no error.
Check it worked
Four checks. The second decides whether the retrieval is safe to expose.
-- 1. Provenance: one model across the corpus, and nothing left unembedded.
SELECT embed_model, embed_dims, COUNT(*) AS chunks,
SUM(CASE WHEN embedding IS NULL THEN 1 ELSE 0 END) AS unembedded
FROM dbo.document_chunks
GROUP BY embed_model, embed_dims;
-- 2. Which tables have a predicate and which have a vector index. Never both.
SELECT t.name AS table_name,
MAX(CASE WHEN sp.target_object_id IS NULL THEN 0 ELSE 1 END) AS has_policy,
MAX(CASE WHEN vi.object_id IS NULL THEN 0 ELSE 1 END) AS has_vector_index
FROM sys.tables AS t
LEFT JOIN sys.security_predicates AS sp ON sp.target_object_id = t.object_id
LEFT JOIN sys.vector_indexes AS vi ON vi.object_id = t.object_id
GROUP BY t.name;
-- 3. The vector index exists and is the current version.
SELECT OBJECT_NAME(v.object_id) AS table_name, i.name AS index_name,
JSON_VALUE(v.build_parameters, '$.Version') AS index_version
FROM sys.vector_indexes AS v
JOIN sys.indexes AS i ON v.object_id = i.object_id AND v.index_id = i.index_id;
-- 4. Every stored hash still agrees with the text it was taken from.
SELECT COUNT(*) AS hashes_disagreeing_with_their_text
FROM dbo.document_chunks
WHERE source_hash <> HASHBYTES('SHA2_256', chunk_text);
Run them non-interactively and keep the output:
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C -b -m-1 \
-i check-rag-pipeline.sql -o check-rag-pipeline.out
Expected: check 1 returns exactly one row with unembedded 0; two rows means two geometries
in one column and every distance across them meaningless, and a non-zero unembedded means part of
the corpus is invisible. Check 2 shows documents with has_policy 1, and document_chunks with
has_vector_index 1 and has_policy 0; that zero is the engine's rule, not an omission to fix,
which is why step 6's WHERE clause is what needs the test. Check 3 returns one row per vector
index with index_version 3; empty means every retrieval is scanning, and an older version means
step 6's filter runs after the search rather than during it. Check 4 returns 0; anything else
means the deduplication key is wrong and re-runs are re-embedding unchanged text.
-m-1 makes a severity 10 message print its number, and -b only sets a non-zero exit at severity
11 and above, so sqlcmd exiting 0 says nothing about the four. Read the output file.
-m-1 is an ODBC sqlcmd instruction, meaning the 18.x build from mssql-tools18 or the
Microsoft command line utilities. Measured 2026-09-05, go-sqlcmd 1.10.0, the 1.x build
brew install sqlcmd and winget install sqlcmd install, prints no Msg header on a severity 10
message at any -m value, so on that build a severity 10 message arrives with no number to read.
Query sys.messages or use the ODBC build when the number matters.
Do not
- Do not assume the copy inherits the source's protection, and do not try to give it a policy of its own: that is refused. The query's filter is the boundary and needs a test that fails without it, in the database rather than in application code.
- Do not store an embedding without recording which model made it, and do not mix two models' vectors in one column. Nothing will tell you.
- Do not embed the question with a different model from the corpus.
- Do not re-embed rows that have not changed. Hash the text and skip them.
- Do not treat a distance as a confidence score, and do not put it in the prompt.
- Do not create the vector index as part of a schema-only deployment, and do not ship a pipeline that cannot say "the retrieved context does not answer this".
References
- references/pipeline.md: open it while writing step 2 onwards, or when a
claim above disagrees with what you are seeing. It has the idempotent
MERGE, the embedding loop, the mixed corpus checks, and theMsg 37579disagreement in full. - VECTOR_SEARCH: read
when a filtered retrieval returns fewer rows than asked for, for iterative versus post filtering,
and for the
ORDER BYrules behindMsg 42248andMsg 42271. - CREATE VECTOR INDEX limitations:
read before promising a policy on an indexed table. As of 2026-09-03 it lists partitioning,
TRUNCATEand package import, and notMsg 37579. - Row level security:
read before putting a predicate on
dbo.documents. - HASHBYTES: read before choosing a deduplication key, for the algorithms still supported and the input limit.