SoftadminApi.AiSearchIndex_Search

Searches the HNSW index, full-text index, or both and returns relevant chunks (see AI Search). It requires one or both of the following tables, which the procedure will populate with rows.

CREATE TABLE #SearchResultChunk
(
	SchemaName sysname NOT NULL,
	TableName sysname NOT NULL,
	RowId bigint NOT NULL,
	ChunkText nvarchar(MAX) NOT NULL,
	Score float NOT NULL,
	Rank int NOT NULL,
	CosineDistance float NULL,
	FullTextRank int NULL
);

CREATE TABLE #SearchResultEntry
(
	SchemaName sysname NOT NULL,
	TableName sysname NOT NULL,
	RowId bigint NOT NULL,
	Score float NOT NULL,
	Rank int NOT NULL,
	CosineDistance float NULL,
	FullTextRank int NULL
);

SchemaName

Schema name of the chunk/entry.

TableName

Table name of the chunk/entry.

RowId

Row ID of the chunk/entry.

ChunkText

The chunk text found, prefixed by its entry's chunk prefix.

Score

How relevant the found chunk/entry is. Higher score is better. Can be used to filter the result further.

Rank

The rank of each chunk/entry from the search. Useful when merging this search result with another search result. For example, using reciprocal rank fusion.

CosineDistance

The cosine distance between the query vector and the chunk embedding. The distance is a measure of how good the semantic match is. A lower distance is better. The semantic search will always return some chunks, even if it is a bad match. This column can be used to filter out bad matches.

FullTextRank

The rank returned by the full-text index search. It is a measure of how good the full-text index match is. A higher rank is better. Can be used to filter out search results further. See CONTAINSTABLE for more details.