November 20th, 2026
0 reactions

How to Chunk Documents in Azure SQL Database for RAG

Quick answer

Azure SQL Database can split text into fixed-size chunks using AI_GENERATE_CHUNKS, with the OVERLAP percentage controlling repeated text between chunks. Store embeddings generated through an external Azure OpenAI endpoint in VECTOR columns, then retrieve passages with VECTOR_DISTANCE or VECTOR_SEARCH. This walkthrough covers both techniques with validated T-SQL; chunking requires compatibility level 170 or higher.

Why use Azure SQL Database for RAG retrieval?

If product guidance already lives in Azure SQL Database, native chunking and vector search let an application retrieve focused passages with their source metadata. This is a practical way to evaluate retrieval-augmented generation without first adding a separate vector store.

Imagine a support application that already stores its product guides in Azure SQL Database. A customer asks, “How do I reconnect my gateway after changing the Wi-Fi password?” Keyword matching can miss relevant guidance, while sending an entire manual to a language model adds unrelated text and consumes context. Retrieving a few relevant passages is a more focused starting point. Retrieval-augmented generation (RAG) provides retrieved source material to a large language model (LLM) to ground its answer; here we build the retrieval part.

This article is for SQL developers and architects evaluating whether an existing Azure SQL Database can support semantic retrieval before they introduce a separate vector service. By the end, you will have a basic document-to-retrieval pipeline: stored support text, fixed-size chunks with and without overlap, embeddings, and queries that return passages with their source. It is not a complete chatbot or a production-readiness certification.

How does the document-to-retrieval pipeline work?

Documents -> chunks -> embeddings -> vector search -> retrieved context for an application or LLM. An embedding is a numeric vector representing features of text, so related text can have nearby vectors. During ingestion, AI_GENERATE_CHUNKS splits text already stored in SQL. AI_GENERATE_EMBEDDINGS sends each chunk to the configured Azure OpenAI endpoint; SQL stores the resulting vector alongside the original text and metadata. At query time, the same embedding configuration converts the customer’s question into a vector. SQL returns nearby passages, which the application can display with citations or send to a separate answer-generation model. The examples below stop at retrieval.

We use one fictional Contoso Gateway support guide throughout both techniques. Fixed-size chunking gives us a reproducible baseline; overlap is a variation to test, not an automatic quality upgrade. Chunking and search run in Azure SQL Database, while embedding inference calls the configured external Azure OpenAI endpoint.

Two flows show document ingestion and query-time retrieval. Azure SQL Database performs chunking, stores text and vectors, and runs vector search. Azure OpenAI performs chunk and query embedding inference. Retrieved passages go to an application or an optional LLM.

Figure 1. During ingestion, Azure SQL splits and stores document text, while Azure OpenAI generates chunk embeddings. At query time, Azure OpenAI embeds the question and Azure SQL retrieves passages. An application can display those passages or provide them to an LLM for a separate answer-generation step.

Which Azure SQL functions does this example use?

The examples in this article target Azure SQL Database and use:

  • VECTOR(n) data type: store embeddings as first-class columns
  • AI_GENERATE_CHUNKS(): split text into chunks directly in T-SQL
  • AI_GENERATE_EMBEDDINGS(): call an external embedding model (e.g., Azure OpenAI) from T-SQL to vectorize text
  • VECTOR_DISTANCE(): cosine, dot product, or Euclidean distance between vectors
  • CREATE VECTOR INDEX: DiskANN-based approximate nearest neighbor indexing

For this basic pattern, a separate vector database is not required. Chunking and search run in Azure SQL Database; embedding inference calls the configured external model endpoint.

_______________________________________________

What do you need before running the T-SQL examples?

Use a new test database with the permissions to create tables, database-scoped credentials, and an external model. You also need an Azure OpenAI resource with a text-embedding-3-small deployment, a reachable permitted endpoint, and authorized access to that deployment. The examples use 1,536-dimensional embeddings for both chunks and questions. Replace placeholders only through an approved, secure provisioning process.

Run setup once, then the sample-document insert and the two ingestion examples in order. The T-SQL was validated in a test deployment on 1 November 2026; substitute your own authorized endpoint and database settings when running it. Embedding calls consume service quota and can incur charges.

Key requirements and parameters

Setting Meaning or requirement This example
Compatibility level AI_GENERATE_CHUNKS requires 170 or higher. 170; test and approve changes before upgrading an existing database.
CHUNK_SIZE A character count, not a token count. 500 characters, chosen for illustration rather than as a production default.
OVERLAP An integer percentage from 0 through 50; default is 0. 15% of 500 = 75 repeated characters after the first chunk.
Embedding dimensions Stored chunk vectors and query vectors must use a matching model/configuration. 1,536 dimensions with text-embedding-3-small.
Vector index creation At least 100 non-NULL vectors are required to create the index. Skip index creation for the six-vector demo; the minimum is not a recommendation to switch to ANN.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-chunks-transact-sql?view=azuresqldb-current

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-vector-index-transact-sql?view=azuresqldb-current

Step 0: Check compatibility and endpoint access

AI_GENERATE_CHUNKS requires database compatibility level 170 or higher. Check the target database first. Changing compatibility can affect existing query plans; test the change and obtain the database owner’s approval before changing a production database.

SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

-- In the approved test database, if its level is below 170:
-- Replace YourDatabase with that database's name.
-- ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 170;

External REST endpoint invocation is enabled by default in Azure SQL Database. SQL Server and Azure SQL Managed Instance have different enablement requirements. Check both the client’s SQL access and the SQL service’s ability to reach and authenticate to the model endpoint. SQL sign-in does not automatically grant outbound Azure OpenAI access.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-embeddings-transact-sql?view=azuresqldb-current

Step 1: Configure Azure OpenAI embedding access

Create a database-scoped credential for the endpoint, then register an EMBEDDINGS external model with your actual deployment URL. The model definition connects T-SQL to inference; it does not run the embedding model inside the database engine. Keep secrets out of shared source files and provision authentication through your approved process.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-model-transact-sql?view=azuresqldb-current

Security: The API key and master-key password below are placeholders, not real credentials. Never place real secrets in shared scripts, source control, notebooks, articles, screenshots, or logs. Prefer managed identity where supported and grant only the required Azure OpenAI permissions. If a key is required, keep it in Azure Key Vault or an approved secret store and let controlled deployment tooling provision or update the database-scoped credential.

Restrict who can manage credentials and execute the external model, rotate keys through the approved process, and confirm that sending the selected document text to the model endpoint complies with your data-handling requirements. The API-key syntax is shown only to explain the object definition, not as a production secret-distribution pattern.

-- Create a master key (if not already present)
IF NOT EXISTS (
    SELECT * FROM sys.symmetric_keys
    WHERE [name] = '##MS_DatabaseMasterKey##'
)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<YourStrongPassword>';
END
GO
 
-- Create database scoped credential for Azure OpenAI
CREATE DATABASE SCOPED CREDENTIAL [https://<your-resource>.openai.azure.com/]
WITH IDENTITY = 'HTTPEndpointHeaders',
SECRET = '{"api-key":"<YOUR_AZURE_OPENAI_KEY>"}';
GO
 
-- Register the embedding model
CREATE EXTERNAL MODEL MyEmbeddingModel
WITH (
    LOCATION = 'https://<your-resource>.openai.azure.com/openai/deployments/<your-deployment-name>/embeddings?api-version=2024-06-01',
    API_FORMAT = 'Azure OpenAI',
    MODEL_TYPE = EMBEDDINGS,
    MODEL      = 'text-embedding-3-small',
    CREDENTIAL = [https://<your-resource>.openai.azure.com/]
    -- Optional: PARAMETERS = '{"dimensions":1536}'
);
GO

Embedding dimensions must match the stored and query vector types. This example uses the default 1,536 dimensions from text-embedding-3-small. If requesting fewer dimensions, such as 768, keep all relevant vector types aligned. The deployment name in LOCATION is your deployment’s name, not necessarily the underlying model name. PARAMETERS stays commented out here. Only when enabling that optional property, add its comma separator after CREDENTIAL; keep the default without a trailing comma.

Step 2: Store source documents, chunks, and vectors

Keep the source text and source metadata in Documents, and store each chunk alongside its embedding in DocumentChunks. The versioned source key and chunk-method label let the two strategies be compared without mixing their results.

CREATE TABLE dbo.Documents (
    doc_id INT IDENTITY(1,1) PRIMARY KEY,
                source_key NVARCHAR(200) NOT NULL UNIQUE,
                title NVARCHAR(500),
    full_text NVARCHAR(MAX),
    source_url NVARCHAR(1000),
    created_at DATETIME2 DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.DocumentChunks (
    chunk_id INT IDENTITY(1,1) PRIMARY KEY,
    doc_id INT NOT NULL REFERENCES dbo.Documents(doc_id),
    chunk_text NVARCHAR(MAX),
    chunk_order BIGINT,
    chunk_offset BIGINT,
    chunk_length INT,
    chunk_method NVARCHAR(50),
    embedding VECTOR(1536),
    created_at DATETIME2 DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_DocumentChunks_MethodOrder
        UNIQUE (doc_id, chunk_method, chunk_order)
);
GO

Sample Support Document

Load this fictional product guide once. The unique, versioned source_key identifies the same document in every example below; source_url is display metadata, not a uniqueness guarantee. The .example address is illustrative, not a real support site. Retain the document ID, source URL, chunk order, and offsets so retrieved passages can be traced back to their source.

-- Fictional product data; the .example URL is not a live site.
IF NOT EXISTS (
    SELECT 1 FROM dbo.Documents
    WHERE source_key = N'contoso-gateway-reconnect-v1'
)
BEGIN
    INSERT INTO dbo.Documents (source_key, title, full_text, source_url)
    VALUES (
        N'contoso-gateway-reconnect-v1', N'Contoso Gateway: Reconnect Wi-Fi',
        CAST(N'Contoso Gateway support guide. This is a fictional product example. ' AS NVARCHAR(MAX))
        + N'After the Wi-Fi password changes, the gateway can go offline even '
        + N'when the router is working. Open the Contoso app, choose Settings > '
        + N'Network > Reconnect, select your Wi-Fi network, and enter the new '
        + N'password. Wait up to 60 seconds for the status light to turn green. '
        + N'Reconnect changes only network settings and keeps saved schedules. If '
        + N'the gateway stays offline, move it near the router and restart it '
        + N'once. Do not factory-reset the gateway as a first step because a '
        + N'reset removes saved schedules. If reconnect and restart both fail, '
        + N'contact support with the device ID and the displayed error code. '
        + N'Never send your Wi-Fi password to support. Before contacting support, '
        + N'check that the app and gateway use the same account and that the '
        + N'router is accepting other devices. A router restart can temporarily '
        + N'disconnect every device on that network, so arrange an appropriate '
        + N'time before doing it. Keep the gateway connected to power while '
        + N'checking its status. For an unrelated billing issue, use the billing '
        + N'help page rather than resetting the device.'
        , N'https://docs.contoso.example/gateway/reconnect'
    );
END;
GO

The unique (doc_id, chunk_method, chunk_order) key separates the baseline from the overlap experiment. The ingestion examples skip a document/method already loaded, so rerunning them does not append duplicate chunks. These examples assume immutable source text for a given source_key. Give a changed source a new versioned key and re-chunk it in a production ingestion process.

_______________________________________________

How do you implement fixed-size chunking in Azure SQL Database?

Use AI_GENERATE_CHUNKS with CHUNK_TYPE = FIXED and CHUNK_SIZE expressed in characters. For the Contoso Gateway example, a smaller passage can isolate the reconnect procedure instead of returning the entire guide, including unrelated billing and escalation advice. A fixed boundary can still split an instruction from its warning.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-chunks-transact-sql?view=azuresqldb-current

Start with fixed-size chunking because it is straightforward to reproduce, inspect, and cost before comparing more complex strategies. AI_GENERATE_CHUNKS uses a character count, not a token count. We use 500 characters only as an example; it is not a universal production recommendation.

What does AI_GENERATE_CHUNKS return?

The function returns chunk text with its order, source offset, and length. The chunk column retains the source expression’s text type. Order and offset are BIGINT values; length is INT. The optional chunk_set_id groups chunks generated from one source in a call.

The function signature:

AI_GENERATE_CHUNKS (
    SOURCE     = <text_expression>,
    CHUNK_TYPE = FIXED,
    CHUNK_SIZE = <number_of_characters>
)

It returns a table with the following columns:

Column Type Description
chunk Same as source The text fragment (matches the data type of the source expression)
chunk_order bigint Sequence number starting at 1
chunk_offset bigint Character position in the source text
chunk_length int Character count of the chunk
chunk_set_id bigint Groups chunks from same source (optional, enable via parameter)

How can you preview chunks before generating embeddings?

Preview how a document will be chunked:

SELECT d.title, c.chunk_order, c.chunk_offset,
       c.chunk_length, LEFT(c.chunk, 80) AS chunk_preview
FROM dbo.Documents AS d
CROSS APPLY AI_GENERATE_CHUNKS (
    SOURCE = d.full_text,
    CHUNK_TYPE = FIXED,
    CHUNK_SIZE = 500
) AS c
WHERE d.source_key = N'contoso-gateway-reconnect-v1'
ORDER BY c.chunk_order;

Illustrative 1-based character slices (not executed output):

order offset length chunk_preview
1 1 500 Contoso Gateway support guide. This is a fictional product example. After the Wi
2 501 500 he gateway as a first step because a reset removes saved schedules. If reconnect
3 1001 115 le checking its status. For an unrelated billing issue, use the billing help pag

These previews are character-slice illustrations of the sample text, not captured database or embedding-service output. Inspect the returned full chunks before choosing a size: a fixed boundary can cut a sentence or detach an instruction from its warning.

How do you chunk, embed, and store text in one statement?

The real power is combining everything in a single INSERT statement:

INSERT INTO dbo.DocumentChunks
    (doc_id, chunk_text, chunk_order, chunk_offset,
     chunk_length, chunk_method, embedding)
SELECT d.doc_id, c.chunk, c.chunk_order, c.chunk_offset,
       c.chunk_length, N'fixed_500',
       AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL MyEmbeddingModel)
FROM dbo.Documents AS d
CROSS APPLY AI_GENERATE_CHUNKS (
    SOURCE = d.full_text,
    CHUNK_TYPE = FIXED,
    CHUNK_SIZE = 500
) AS c
WHERE d.source_key = N'contoso-gateway-reconnect-v1'
  AND NOT EXISTS (
      SELECT 1 FROM dbo.DocumentChunks AS existing
      WHERE existing.doc_id = d.doc_id
        AND existing.chunk_method = N'fixed_500'
  );
GO

This statement reads the guide, splits it into chunks, calls the external embedding endpoint, and stores text, provenance, and vectors together. The method label separates the baseline from the overlap version. Batch and monitor a large ingestion workload rather than treating one successful statement as evidence of production throughput.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-embeddings-transact-sql?view=azuresqldb-current

How should you choose a chunk size?

Treat chunk size as a hypothesis to test. Document structure, query intent, the embedding model, and the application’s context budget determine what works. Larger chunks can retain complete instructions but include unrelated topics; smaller chunks can be more focused but lose the surrounding conditions or warning. Neither outcome follows from a fixed character threshold.

For an initial experiment, compare 500, 1,000, and 2,000 characters on representative customer questions, then inspect which chunks contain the needed instruction and its qualifiers. These values are an illustrative test grid, not a documented sweet spot. Verify the embedding model’s token limit separately: characters and tokens are different units.

Pros: No separate splitter application; one T-SQL statement; straightforward to inspect and maintain; predictable chunk counts for storage planning. Measure billable embedding tokens separately.

❌ Cons: Splits mid-word and mid-sentence; ignores document structure; chunks may break semantic meaning at boundaries.

_______________________________________________

How do you add overlap between chunks?

Set the OVERLAP percentage while keeping the same source and chunk size. The example uses 15% overlap with 500-character chunks, repeating 75 characters after the first chunk. That can preserve local context around a reconnect instruction and warning, but it does not guarantee that every complete concept fits in one chunk.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-chunks-transact-sql?view=azuresqldb-current

How is overlap calculated?

OVERLAP is a percentage of CHUNK_SIZE, not a number of characters. At 15% of 500, the repeated region is 75 characters and the start of the next window advances 425 characters. The first chunk has no preceding chunk; the final chunk can be shorter.

For a 500-character chunk and 15% overlap, 75 characters are repeated and the next window starts 425 characters later. The final chunk can be shorter. The ranges below illustrate that arithmetic using the same support text. Ranges for this support document (inclusive, 1-based):

No overlap: 1-500 | 501-1000 | 1001-1115

15% overlap: 1-500 | 426-925 | 851-1115

Implement fixed-size chunking with 15% overlap

INSERT INTO dbo.DocumentChunks
    (doc_id, chunk_text, chunk_order, chunk_offset,
     chunk_length, chunk_method, embedding)
SELECT d.doc_id, c.chunk, c.chunk_order, c.chunk_offset,
       c.chunk_length, N'fixed_500_overlap15',
       AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL MyEmbeddingModel)
FROM dbo.Documents AS d
CROSS APPLY AI_GENERATE_CHUNKS (
    SOURCE = d.full_text,
    CHUNK_TYPE = FIXED,
    CHUNK_SIZE = 500,
    OVERLAP = 15
) AS c
WHERE d.source_key = N'contoso-gateway-reconnect-v1'
  AND NOT EXISTS (
      SELECT 1 FROM dbo.DocumentChunks AS existing
      WHERE existing.doc_id = d.doc_id
        AND existing.chunk_method = N'fixed_500_overlap15'
  );
GO

The chunking change is OVERLAP = 15. The distinct chunk_method label keeps the experiment separate from fixed_500, so retrieval comparisons do not mix the two chunk sets.

How should you choose an overlap percentage?

Overlap % Chars (at 500) What to evaluate
0% 0 Baseline without duplication; check whether boundary context is missing.
10-15% 50-75 A candidate test range; inspect continuity, relevant-passage retrieval, and duplication.
20-25% 100-125 Test whether additional context offsets the greater embedding and storage volume.
30-50% 150-250 Compare the added duplication with simply increasing chunk size; do not assume higher overlap is better.

Cost trade-off: For long text and full-sized windows, overlap fraction p reduces the step to chunk_size x (1 – p), so chunk count approaches 1 / (1 – p) times the no-overlap count. At 15%, that is approximately 17.6% more chunks, not 15%; short documents and final partial chunks change the exact count. Measure billable embedding tokens, vector storage, ingestion time, and duplicated retrieval context. A larger index is not evidence of a better answer.

How do you preserve provenance across multiple documents?

ENABLE_CHUNK_SET_ID = 1 adds a grouping column for chunks generated from the same source in a call. Keep d.doc_id, the versioned source_key, and source_url as durable provenance; do not use a generated chunk_set_id as a permanent replacement for your document key.

SELECT d.doc_id, d.source_key,
       c.chunk, c.chunk_order, c.chunk_set_id
FROM dbo.Documents AS d
CROSS APPLY AI_GENERATE_CHUNKS (
    SOURCE = d.full_text,
    CHUNK_TYPE = FIXED,
    CHUNK_SIZE = 500,
    OVERLAP = 15,
    ENABLE_CHUNK_SET_ID = 1
) AS c
ORDER BY d.doc_id, c.chunk_order;
GO

Pros: One additional chunking parameter, no separate splitter application, and potentially better local continuity across boundaries. Validate the benefit on the support questions your application actually receives.

Cons: More chunks and repeated embedding input; still character-based rather than sentence-aware. Extra overlap can cause near-duplicate results to consume a retrieval budget, so inspect and deduplicate context where appropriate.

_______________________________________________

How do you retrieve relevant chunks?

Embed the user’s question with the same configuration used for the chunks, then compare that query vector with stored vectors. VECTOR_DISTANCE supports exact nearest-neighbor retrieval. VECTOR_SEARCH is an ANN-capable query form, but a query using WITH APPROXIMATE may still select an exact plan. Lower cosine distance means greater vector similarity, not answer confidence.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/sql-server/ai/vectors?view=azuresqldb-current

When should you use VECTOR_DISTANCE for exact search?

Exact k-nearest-neighbor (kNN) search calculates distances for the qualifying vectors and returns their nearest neighbors without ANN approximation. That does not guarantee that the retrieved text answers the question. Approximate nearest-neighbor (ANN) search trades some neighbor recall for potential latency or resource savings. For cosine distance, lower values mean nearer, more similar vectors. Microsoft gives fewer than 50,000 candidate vectors as general guidance, including after relational filtering, not a hard cutoff or latency promise. Benchmark latency, concurrency, and workload impact on your database.

DECLARE @query NVARCHAR(MAX) =
    N'How do I reconnect my gateway after changing the Wi-Fi password?';
DECLARE @qv VECTOR(1536) = AI_GENERATE_EMBEDDINGS(
    @query USE MODEL MyEmbeddingModel
);

-- Number of items to return (k) k = 2 is illustrative; tune it with representative questions.
SELECT TOP (2)
    dc.chunk_id, dc.chunk_text, dc.chunk_order,
    d.title AS source_doc, d.source_url,
    VECTOR_DISTANCE('cosine', @qv, dc.embedding) AS distance
FROM dbo.DocumentChunks AS dc
JOIN dbo.Documents AS d ON d.doc_id = dc.doc_id
WHERE dc.chunk_method = N'fixed_500_overlap15'
  AND dc.embedding IS NOT NULL
ORDER BY distance ASC;
GO

When should you use VECTOR_SEARCH and a DiskANN index?

Use this optional path when evaluation shows that indexed retrieval is useful for your workload. Skip index creation for the six-vector teaching example: it requires at least 100 non-NULL vectors. The later validation used a larger fictional corpus to exercise the index. Reaching the minimum row count alone is not a reason to switch.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-vector-index-transact-sql?view=azuresqldb-current

⚠️ Important: Vector indexes require at least 100 rows with non-NULL vector values before the index can be created. Attempting to create an index on fewer rows fails with error Msg 42266. For development with small datasets, use VECTOR_DISTANCE (exact kNN search) instead it works without an index.

-- Create DiskANN index (requires 100+ rows with non-NULL embeddings)
CREATE VECTOR INDEX IX_Embedding_DiskANN
ON dbo.DocumentChunks(embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');
GO

Use SELECT TOP (N) WITH APPROXIMATE with the latest index format, and order only by the returned distance in ascending order. The optimizer may still choose exact search for a small or selective candidate set. Check the actual execution plan before asserting that DiskANN was used.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-search-transact-sql?view=azuresqldb-current

DECLARE @qv VECTOR(1536) = AI_GENERATE_EMBEDDINGS(
    N'How do I reconnect my gateway after changing the Wi-Fi password?'
    USE MODEL MyEmbeddingModel
);

-- Keep the illustrative k = 2 consistent with the exact-search demo.
SELECT TOP (2) WITH APPROXIMATE
    t.chunk_id, t.doc_id, t.chunk_text, t.chunk_method,
    s.distance
FROM VECTOR_SEARCH(
    TABLE = dbo.DocumentChunks AS t,
    COLUMN = embedding,
    SIMILAR_TO = @qv,
    METRIC = 'cosine'
) AS s
WHERE t.chunk_method = N'fixed_500_overlap15'
ORDER BY s.distance ASC;
GO

⚠️ Syntax Note: The TOP_N parameter in VECTOR_SEARCH is deprecated. Latest vector indexes require SELECT TOP (N) WITH APPROXIMATE syntax. Using TOP_N with latest indexes returns error Msg 42274.

DiskANN: Developed by Microsoft Research, DiskANN is a graph-based approximate nearest-neighbor algorithm optimized for SSD storage. Evaluate latency, memory, and throughput for your workload; the small fixture does not establish production performance.

_______________________________________________

How do you compare both techniques with the same question?

Run the prerequisites, sample-document insert, and both ingestion statements before this comparison. The query embeds one customer question once, then retrieves up to two passages per method from the same source document. k = 2 is illustrative; the small example demonstrates retrieval wiring, not superiority of either strategy.

DECLARE @query NVARCHAR(MAX) =
    N'How do I reconnect my gateway after changing the Wi-Fi password?';
DECLARE @qv VECTOR(1536) = AI_GENERATE_EMBEDDINGS(
    @query USE MODEL MyEmbeddingModel
);

SELECT methods.chunk_method,
       hits.chunk_order, hits.chunk_text,
       hits.source_doc, hits.source_url, hits.distance
FROM (VALUES (N'fixed_500'), (N'fixed_500_overlap15'))
    AS methods(chunk_method)
CROSS APPLY (
    SELECT TOP (2)
        dc.chunk_order, dc.chunk_text,
        d.title AS source_doc, d.source_url,
        VECTOR_DISTANCE('cosine', @qv, dc.embedding) AS distance
    FROM dbo.DocumentChunks AS dc
    JOIN dbo.Documents AS d ON d.doc_id = dc.doc_id
    WHERE d.source_key = N'contoso-gateway-reconnect-v1'
      AND dc.chunk_method = methods.chunk_method
      AND dc.embedding IS NOT NULL
    ORDER BY distance ASC
) AS hits
ORDER BY methods.chunk_method, hits.distance;
GO

Inspect the top two passages together for Settings > Network > Reconnect, the 60-second wait, and the warning that a factory reset removes saved schedules. These are the relevant facts in our fictional source, not a promised ranking or a fabricated LLM answer. Distances and ordering depend on the deployed embedding configuration.

The application can show a passage with its source link or pass selected text to an answer-generation model with the source metadata. Production applications must enforce the caller’s document-access rules before exposing context; these single-document demo queries do not implement tenant authorization. Handle missing or irrelevant results explicitly rather than forcing an unsupported answer.

Scaling up: Keep exact search as an evaluation reference. A DiskANN index requires at least 100 rows with non-NULL vectors to be created, but reaching 100 rows is not a recommendation to switch search strategies. Move to ANN when measured latency and resource use justify it, and compare recall against the exact-search baseline.

_______________________________________________

What did the live validation show?

The T-SQL examples were executed against Azure SQL Database and Azure OpenAI on 1 November 2026. The default code blocks passed with the documented environment substitutions. Fourteen executable blocks and 61 assertions covered actual outputs, not just syntax inspection.

Check Observed result
Model and query embeddings text-embedding-3-small, model version 1, API 2024-06-01; chunk and query vectors had 1,536 dimensions.
Small document The exact 1,115-character fictional guide produced three fixed-size chunks and three overlapping chunks.
Guarded reruns The guarded source insert and ingestion retained one source and six chunks without duplicates. Setup CREATE statements were not treated as rerunnable.
Indexed retrieval A 51-document fictional fixture provided 256 non-NULL vectors. Index version 3 was verified. The default query selected an exact scan; a separate forced-index diagnostic showed a Vector Index Seek.
Scope The results establish execution in the tested environment, not a production latency, authorization, grounded-answer, or universal retrieval-quality benchmark.

The optional PARAMETERS property remains commented out exactly as in the validated code. The default statement needs no trailing comma. The troubleshooting section explains the separator requirement only for readers who enable that optional property.

When is this approach a good fit?

This approach is a good candidate for SQL developers and architects whose application already stores its documents and business metadata in Azure SQL Database and benefits from retrieving them together. The support example demonstrates a basic path from stored guidance to relevant passages without adding a separate vector store. It does not establish a production default for every corpus.

Evaluate Before Calling It Production-Ready

Use a representative set of customer questions with known relevant passages, including paraphrases, multi-step questions, boundary cases, and questions the corpus cannot answer. Hold the embedding model, retrieval k, and candidate-document set constant while comparing chunk size and overlap. Check passage-level relevance and whether the required warning survives, then record retrieval latency, SQL CPU/I/O, ingestion time, embedding-token usage, and stored-vector count. If an LLM generates answers, evaluate grounded answer quality and citation correctness separately.

For this support case, inspect whether the selected passages together contain the reconnect procedure and the factory-reset warning. Define the required relevance, latency, and cost targets before selecting a configuration. Use structure-aware chunking when fixed boundaries repeatedly break important procedures or qualifiers; consider separate search capacity if measured retrieval load interferes with transactional objectives.

_______________________________________________

Troubleshooting and frequently asked questions

Does this pattern require a separate vector database?

No separate vector store is required for this example: Azure SQL Database stores source text, metadata, and vectors and performs retrieval. Azure OpenAI remains an external dependency for embedding inference. Choose a different architecture if your measured capacity, ranking, ingestion, or governance requirements call for it.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/sql-server/ai/vectors?view=azuresqldb-current

Why does AI_GENERATE_CHUNKS not resolve?

Check the database compatibility level first. AI_GENERATE_CHUNKS requires level 170 or higher. Verify that you are using a supported SQL product and version, and obtain the database owner’s approval before changing an existing database’s compatibility.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-chunks-transact-sql?view=azuresqldb-current

Are CHUNK_SIZE values characters or tokens?

In this function’s FIXED mode, CHUNK_SIZE is a character count. It is not a tokenizer budget. Check the embedding model’s token limit independently; the number of tokens depends on the content and language.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-chunks-transact-sql?view=azuresqldb-current

Why can CREATE VECTOR INDEX fail on the small example?

Index creation needs at least 100 rows with non-NULL vectors. The six-vector example does not meet that minimum and returns the documented error 42266. Use the exact retrieval example for a small fixture and add an index only when your larger workload and evaluation justify it.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-vector-index-transact-sql?view=azuresqldb-current

Does WITH APPROXIMATE force the vector index?

No. The optimizer can select an exact search plan even when a compatible vector index exists. Inspect the actual plan. FORCE_ANN_ONLY is an explicit diagnostic option, not a required addition to the validated query.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-search-transact-sql?view=azuresqldb-current

Can you rerun the entire example safely?

The source insert and both ingestion statements are guarded to avoid duplicate sample data for an unchanged source key. The CREATE statements are setup-once statements. Use an empty test database for first-time setup; do not interpret the ingestion guards as making every statement idempotent.

What changes if you enable the optional PARAMETERS property?

It is disabled by a comment in the code shown here. If you enable the property, include its required comma separator after CREDENTIAL. Do not leave a trailing comma when PARAMETERS remains commented out. Stored and query vector types must remain consistent if you request a different embedding dimension.

Microsoft Learn: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-model-transact-sql?view=azuresqldb-current

References

_______________________________________________

Author

Dhruv Malik

0 comments