Word Search Without Full-Text: Build a Word Index Table

A word index table lets your app find tickets by word without reading every note. It is a small side table, cheap to build, and it works even on a server with no full-text component.

The search that reads everything

Picture a support team with 100,000 tickets. Every ticket has a free-text note. Someone types one word into the search box, say chargeback, and then waits.

First, fake tickets. The script builds 100,000 notes from a small word list. Then it adds special sentences: 20 tickets mention a chargeback, 40 ask for a refund, and 50 say damaged. The punctuation is on purpose. Real notes are messy.

DROP TABLE IF EXISTS dbo.TicketNotes;
CREATE TABLE dbo.TicketNotes (
    TicketId int NOT NULL CONSTRAINT PK_TicketNotes PRIMARY KEY,
    Note nvarchar(400) NOT NULL);

WITH Vocab AS (
    SELECT Id, Word FROM (VALUES
        (1, N'customer'), (2, N'called'), (3, N'about'), (4, N'the'),
        (5, N'invoice'), (6, N'again.'), (7, N'login'), (8, N'failed,'),
        (9, N'password'), (10, N'reset'), (11, N'shipping'), (12, N'delay'),
        (13, N'order'), (14, N'update'), (15, N'is'), (16, N'still'),
        (17, N'waiting'), (18, N'account'), (19, N'locked'), (20, N'thanks.'),
        (21, N'email'), (22, N'sent'), (23, N'to'), (24, N'team')) AS v (Id, Word)),
Tickets AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS TicketId
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)
INSERT dbo.TicketNotes (TicketId, Note)
SELECT t.TicketId, STRING_AGG(v.Word, N' ') WITHIN GROUP (ORDER BY s.Slot)
FROM Tickets AS t
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8)) AS s (Slot)
JOIN Vocab AS v ON v.Id = 1 + CONVERT(int, SUBSTRING(HASHBYTES('MD5', CONCAT(t.TicketId, '-', s.Slot)), 1, 3)) % 24
GROUP BY t.TicketId;

UPDATE dbo.TicketNotes SET Note += N' Card dispute, chargeback opened.' WHERE TicketId % 5000 = 1234;
UPDATE dbo.TicketNotes SET Note += N' Customer wants a refund.' WHERE TicketId % 2500 = 7;
UPDATE dbo.TicketNotes SET Note += N' Package arrived damaged.' WHERE TicketId % 2000 = 7;

SELECT COUNT(*) AS TotalTickets FROM dbo.TicketNotes;

Now the search everyone writes first, with STATISTICS IO counting pages read.

SET STATISTICS IO ON;

SELECT TicketId, Note
FROM dbo.TicketNotes
WHERE Note LIKE N'%chargeback%'
ORDER BY TicketId;

You get 20 tickets and 1,540 logical reads, which is every page of the table. Timings vary, so I count reads.

Why a leading wildcard scans

The leading percent sign is the problem. An index is sorted by the first letters of a value. A word buried mid-sentence gives SQL Server nothing to jump to, so it reads every row.

My post Leading Wildcard LIKE Searches: Why They Scan and What Helps covers what an index on the column can and cannot do. Here we take another road: a list of words.

Build the word index table

Split every note into words. Store one row per word per ticket. Index the word. A small function does the cleanup, so the first load and the later trigger follow the same rules.

CREATE OR ALTER FUNCTION dbo.NoteWords (@Note nvarchar(400))
RETURNS TABLE
AS RETURN
    SELECT DISTINCT LOWER(value) AS Word
    FROM STRING_SPLIT(TRANSLATE(@Note, N'.,;:!?()', N'        '), N' ')
    WHERE LEN(value) >= 3;

TRANSLATE turns punctuation into spaces. STRING_SPLIT cuts at each space. LOWER lowercases. The LEN test drops tiny words like “is” and “to”. DISTINCT keeps one row per word per ticket.

SET STATISTICS IO OFF;

DROP TABLE IF EXISTS dbo.TicketWords;
CREATE TABLE dbo.TicketWords (
    Word nvarchar(60) COLLATE Latin1_General_CI_AS NOT NULL,
    TicketId int NOT NULL,
    CONSTRAINT PK_TicketWords PRIMARY KEY CLUSTERED (Word, TicketId));

INSERT dbo.TicketWords (Word, TicketId)
SELECT w.Word, t.TicketId
FROM dbo.TicketNotes AS t
CROSS APPLY dbo.NoteWords(t.Note) AS w;

SELECT COUNT(*) AS WordRows, COUNT(DISTINCT Word) AS DistinctWords
FROM dbo.TicketWords;

The clustered key on Word, then TicketId, is the index. The Word column uses a case-insensitive collation, so Refund and refund count as the same word.

The load wrote 635,336 rows, about six per ticket, but only 31 different words. My fake vocabulary is tiny, so real tables will be bigger.

Search by word, then by two words

SET STATISTICS IO ON;

SELECT n.TicketId, n.Note
FROM dbo.TicketWords AS w
JOIN dbo.TicketNotes AS n ON n.TicketId = w.TicketId
WHERE w.Word = N'chargeback'
ORDER BY n.TicketId;

Same 20 tickets. The word table needs 4 reads. TicketNotes needs 60, three per ticket to pick up each note by its key. That is 64 in total, against 1,540, roughly 24 times fewer.

Support will ask for two words next: refund and damaged together. Group the word rows by ticket and keep tickets that matched both. The 2 is the number of words searched.

SELECT n.TicketId, n.Note
FROM dbo.TicketNotes AS n
WHERE n.TicketId IN (
    SELECT TicketId
    FROM dbo.TicketWords
    WHERE Word IN (N'Refund', N'damaged')
    GROUP BY TicketId
    HAVING COUNT(DISTINCT Word) = 2)
ORDER BY n.TicketId;

Ten tickets come back, with 6 reads on the word table and 30 on TicketNotes. I typed Refund with a capital R and it still matched, thanks to the collation. The notes say “refund.” with a period, yet the search finds them, because the load stripped the punctuation.

Keep it in sync

A stale word table is worse than none. Add words in your load step, or let a trigger do it. A trigger is the safer start, because no code path can forget. This one deletes the old words for any touched ticket, then adds the new ones.

CREATE OR ALTER TRIGGER dbo.trg_TicketNotes_Words
ON dbo.TicketNotes
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DELETE w
    FROM dbo.TicketWords AS w
    WHERE w.TicketId IN (SELECT TicketId FROM deleted);

    INSERT dbo.TicketWords (Word, TicketId)
    SELECT x.Word, i.TicketId
    FROM inserted AS i
    CROSS APPLY dbo.NoteWords(i.Note) AS x;
END;

To test it, I add ticket 100001 with a chargeback, search, delete the ticket, and search again.

SET STATISTICS IO OFF;

INSERT dbo.TicketNotes (TicketId, Note)
VALUES (100001, N'Customer filed a chargeback today.');

SELECT TicketId FROM dbo.TicketWords WHERE Word = N'chargeback' ORDER BY TicketId;

DELETE dbo.TicketNotes WHERE TicketId = 100001;

SELECT COUNT(*) AS ChargebackRowsAfterDelete
FROM dbo.TicketWords
WHERE Word = N'chargeback';

The new ticket shows up, 21 in all. After the delete, the count is back to 20.

Nothing here is free. The word table takes storage, and every write does extra work. If a busy system feels that, load the words in batches at night.

And this is not real full-text search. There is no stemming, so chargeback and chargebacks are different words. There is no ranking and no phrase search. For typos, see Trigram Matching: Finding Similar Words With an N-Gram Table, which uses a similar side table.

Clean up

The demo creates two tables and one function, and this block removes them. The trigger goes with its table. On your own data, swap in your names and compare reads before and after.

DROP TABLE IF EXISTS dbo.TicketWords;
DROP TABLE IF EXISTS dbo.TicketNotes;
DROP FUNCTION IF EXISTS dbo.NoteWords;

Pick one slow search box in your own system, and count the reads before you change anything.

A word index is not full-text search, it is a cheap lookup table.

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