AI_GENERATE_CHUNKS in SQL Server 2025: Splitting Long Text Into Pieces

AI_GENERATE_CHUNKS cuts one long piece of text into smaller pieces, called chunks, right inside a query. It is the first step before search by meaning. It calls no model, so you can try it today with nothing but SQL Server 2025.

Why Cut Text at All

Long text is a poor unit for search. A model that turns text into a list of numbers (an embedding) accepts only so much input per call. One list for a whole article also blurs every topic in it. Small pieces keep one idea each, so a search can land on the paragraph that answers the question.

AI_GENERATE_CHUNKS returns rows, so you use it in the FROM clause or with CROSS APPLY. Everything below ran on SQL Server 2025, build 17.0.5005.3. Nothing leaves the server, and no key or model is needed.

Think of a help page that explains three different tasks. If you store it as one piece, a search for the second task still returns the whole page. If you store it in pieces, the search returns the one piece that matters. The pieces are the chunks, and this function makes them.

It Is a Preview Feature

Microsoft lists AI_GENERATE_CHUNKS as a preview feature. Its behavior can change before it becomes final, so do not build a production job on it yet. Preview features sit behind a database scoped setting named PREVIEW_FEATURES. I switch it on in the test database only.

One measured detail: a new database had the setting at 0, and the function still ran on my build. I turn the setting on anyway, because a later build can start to enforce it.

IF DB_ID(N'SqlChunksDemo') IS NULL CREATE DATABASE SqlChunksDemo;
GO
USE SqlChunksDemo;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;

The First Call

The smallest test is a sentence in a literal. The source is the text. The chunk type is FIXED, which cuts after a set number of characters. The chunk size is that number, here 20.

SELECT c.chunk_order, c.chunk_offset, c.chunk_length, c.chunk
FROM AI_GENERATE_CHUNKS(source = N'Fresh basil grows best in a sunny window with plenty of water.', chunk_type = FIXED, chunk_size = 20) AS c;

Four columns come back. The chunk column holds the piece. The chunk_order column counts the pieces from 1. The chunk_offset column says where the piece starts in the source, also counting from 1. The chunk_length column says how many characters it holds.

Look at the cuts. The first piece ends in “be” and the third in “wate”. The function counts characters and does not care about words. The last piece is whatever is left, two characters here.

A Table of Articles

Real work starts from a table. This one holds three short how-to articles and an empty draft.

CREATE TABLE dbo.Articles
(
    ArticleID int IDENTITY(1,1) PRIMARY KEY,
    Title nvarchar(100) NOT NULL,
    Body nvarchar(max) NOT NULL
);
INSERT INTO dbo.Articles (Title, Body) VALUES
(N'Cooking Rice', N'Rinse the rice until the water runs clear. Add two cups of water for each cup of rice. Bring it to a boil, then cover the pot and lower the heat. Let it cook for fifteen minutes and rest for five more.'),
(N'Watering Tomatoes', N'Tomato plants like deep, steady watering. Water early in the morning so the leaves dry before night. Aim the water at the soil, not at the leaves.'),
(N'Packing Light', N'Pick one bag. Choose clothes that mix and match. Roll them, do not fold them.'),
(N'Empty Draft', N'');

To chunk every article, pass the column as the source and use CROSS APPLY. It runs the function once for each row.

SELECT a.ArticleID, c.chunk_order, c.chunk_offset, c.chunk_length, c.chunk
FROM dbo.Articles AS a
CROSS APPLY AI_GENERATE_CHUNKS(source = a.Body, chunk_type = FIXED, chunk_size = 60) AS c
ORDER BY a.ArticleID, c.chunk_order;

The empty draft produced no rows. An empty text gives no chunks, so CROSS APPLY drops the article. We come back to that below.

Does the function lose any text? A quick check says no. For each article, the chunk lengths add up to the length of the body.

SELECT a.ArticleID, LEN(a.Body) AS BodyLength, SUM(c.chunk_length) AS ChunkLength
FROM dbo.Articles AS a
CROSS APPLY AI_GENERATE_CHUNKS(source = a.Body, chunk_type = FIXED, chunk_size = 60) AS c
GROUP BY a.ArticleID, LEN(a.Body)
ORDER BY a.ArticleID;

Finding a Chunk in the Source

Store each chunk with its ArticleID and chunk_order. That pair identifies the piece, because chunk_order starts again at 1 for every article. When a search later returns a chunk, you can show the article title. You can also point to the right place in the text.

The offset and the length make that possible. SUBSTRING on the original body, with the same two numbers, should give back the chunk. The query below checks it, with an overlap of 20 percent so the offsets are not simple multiples of 60.

SELECT a.ArticleID, COUNT(*) AS Chunks,
    SUM(CASE WHEN SUBSTRING(a.Body, c.chunk_offset, c.chunk_length) = c.chunk THEN 1 ELSE 0 END) AS FoundInSource
FROM dbo.Articles AS a
CROSS APPLY AI_GENERATE_CHUNKS(source = a.Body, chunk_type = FIXED, chunk_size = 60, overlap = 20) AS c
GROUP BY a.ArticleID
ORDER BY a.ArticleID;

Every chunk was found in its source at the stated place. The overlap argument is the subject of the next section.

Overlap

A hard cut can split an idea in two. The overlap argument repeats the end of one chunk at the start of the next. It is a percentage of the chunk size, from 0 to 50. Setting enable_chunk_set_id to 1 adds a chunk_set_id column, which marks the chunks that came from the same source text.

SELECT c.chunk_set_id, c.chunk_order, c.chunk_offset, c.chunk_length, c.chunk
FROM dbo.Articles AS a
CROSS APPLY AI_GENERATE_CHUNKS(source = a.Body, chunk_type = FIXED, chunk_size = 60, overlap = 20, enable_chunk_set_id = 1) AS c
WHERE a.ArticleID = 1
ORDER BY c.chunk_order;

Twenty percent of 60 is 12 characters. Each chunk now starts 48 characters after the one before, so the offsets run 1, 49, 97 and 145. The word cut at the end of chunk 1 shows up whole in chunk 2: “water”.

Values outside the range fail with a clear message. The first block below is the query, and the second shows the message it returns.

SELECT c.chunk
FROM AI_GENERATE_CHUNKS(source = N'abc', chunk_type = FIXED, chunk_size = 20, overlap = 60) AS c;

Msg 43201, Level 16, State 1, Line 1
The value 60 is invalid for overlap. Value must be between 0 and 50.

Chunk Size Changes Everything

The chunk size decides how many rows you create, and every row later needs its own embedding. The query below counts the chunks for three sizes with a small subquery for each. The subquery keeps the empty draft in the result with a count of 0.

SELECT a.ArticleID, LEN(a.Body) AS Characters,
    (SELECT COUNT(*) FROM AI_GENERATE_CHUNKS(source = a.Body, chunk_type = FIXED, chunk_size = 60)) AS Size60,
    (SELECT COUNT(*) FROM AI_GENERATE_CHUNKS(source = a.Body, chunk_type = FIXED, chunk_size = 100)) AS Size100,
    (SELECT COUNT(*) FROM AI_GENERATE_CHUNKS(source = a.Body, chunk_type = FIXED, chunk_size = 200)) AS Size200
FROM dbo.Articles AS a
ORDER BY a.ArticleID;

Small pieces match a question closely but cost more rows and lose the surrounding context. Large pieces keep context but blur the topic again. A good starting point is the smallest piece that still makes sense alone, which is usually a short paragraph. Test two or three sizes on your own text. Compare the pieces by eye before you load thousands of rows.

The Fair Complaint

You could say that cutting inside a word is careless, and that a text function should respect sentences. Fair point. I tried chunk_type = SENTENCE and got a syntax error. FIXED is the only type that ran on my build.

SELECT c.chunk
FROM AI_GENERATE_CHUNKS(source = N'One. Two.', chunk_type = SENTENCE, chunk_size = 20) AS c;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'SENTENCE'.

Overlap is the workaround you have today. It does not give clean sentences. It does make sure a word cut at one edge appears whole in the next chunk.

A Short Checklist

• Turn on PREVIEW_FEATURES in a test database, never first on a production one.
• Pick a chunk size from the smallest piece that still reads well alone.
• Use overlap so a cut word or idea survives in the next chunk.
• Count with a subquery, or use OUTER APPLY, when an empty or NULL text must stay visible.
• Check that the chunk lengths add up to the text length before you trust a load.

The next step after AI_GENERATE_CHUNKS is turning each chunk into an embedding. That needs a model, so it belongs to another post. When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlChunksDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlChunksDemo;

AI_GENERATE_CHUNKS is not a smart reader, it is a ruler.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.