Embedding Pipelines, Vector Search Architecture, Filtering, and Hybrid Relational/Vector Queries

Learning outcomes

A useful vector column is the last stage of a pipeline, not the first. ServiceHub must decide which text is embedded, how it is chunked, which model produced the vector, how changes trigger re-embedding, and how a query combines relational predicates with semantic ranking. SQL Server 2025 can generate chunks and embeddings and can define external model objects, but no database feature removes the need to govern the external model, network, credentials, latency or model-version contract.

1. Make the embedding contract explicit before generating vectors

The embedding pipeline should be idempotent: the same source content and model contract should not create uncontrolled duplicate rows. A content hash is a practical change detector. The model identifier and dimension become part of the row's data contract. If either changes, the row is stale until re-embedded.

USE ServiceHubAILab;GOIF OBJECT_ID(N'lab23.DocumentChunk', N'U') IS NULLBEGIN    CREATE TABLE lab23.DocumentChunk    (        item_id          int NOT NULL,        chunk_ordinal    int NOT NULL,        chunk_text       nvarchar(1000) NOT NULL,        content_hash     varbinary(32) NOT NULL,        embedding_model  nvarchar(100) NOT NULL,        embedding        vector(4) NULL,        embedded_at      datetime2(0) NULL,        CONSTRAINT PK_lab23_DocumentChunk PRIMARY KEY(item_id, chunk_ordinal),        CONSTRAINT FK_lab23_DocumentChunk_Item FOREIGN KEY(item_id)            REFERENCES lab23.KnowledgeItem(item_id)    );END;

In a real pipeline, the source-of-truth text can be much longer than a model's recommended input size. Chunking changes retrieval quality: chunks that are too small lose context; chunks that are too large dilute the relevant concept and can exceed model limits.

USE ServiceHubAILab;GODECLARE @doc nvarchar(max) =N'VPN troubleshooting starts by confirming the user identity and password change. '+ N'Next verify MFA and device time. Then validate network reachability and the VPN client profile.';SELECT chunk, chunk_orderFROM AI_GENERATE_CHUNKS(    SOURCE = @doc,    CHUNK_TYPE = FIXED,    CHUNK_SIZE = 70,    OVERLAP = 10)ORDER BY chunk_order;

The exact chunk boundaries are deterministic for the chosen arguments but are not a semantic guarantee. Record chunking strategy/version when it matters to reproducibility.

2. External models are database objects around a network dependency

CREATE EXTERNAL MODEL stores endpoint/model metadata. AI_GENERATE_EMBEDDINGS can call that model and return the embedding. These features are GA in SQL Server 2025, but the inference endpoint is still external: it can throttle, fail, change price, change model behavior, or be unavailable. SQL Server's external rest endpoint enabled configuration is disabled by default and must be deliberately enabled before remote inference calls.

SELECT    name,    value_in_useFROM sys.configurationsWHERE name IN (N'external rest endpoint enabled', N'allow server scoped db credentials');GOUSE ServiceHubAILab;GOSELECT    name,    location,    api_format,    model_type_desc,    model,    create_time,    modify_timeFROM sys.external_models;GOSELECT name, credential_identityFROM sys.database_scoped_credentials;

For an offline course lab, deterministic mock vectors are better evidence than pretending an external model is available. The goal is to learn SQL Server's integration boundary, not to force learners to buy an AI endpoint.

3. Exact hybrid search: filter first, rank second

Hybrid search combines relational predicates with semantic distance. For ServiceHub, “similar ticket” is still constrained by active status, tenant/region, data classification and sometimes document type. The exact query below is production-safe from a feature-status perspective because it does not require preview vector indexing.

DECLARE @query vector(4) = '[0.78,0.18,0.04,0.08]';SELECT TOP (5)    item_id,    category,    source_text,    VECTOR_DISTANCE('cosine', @query, embedding) AS distanceFROM lab23.KnowledgeItemWHERE is_active = 1  AND region_code = 'N01'  AND embedding_model = N'lab-mock-v1'  AND embedding_dimensions = 4ORDER BY distance, item_id;

A wrong pattern is calculating top-k globally and then applying tenant/security filtering in application code. That can leak unauthorized candidates into telemetry and it can return fewer than k legal rows. Keep mandatory access predicates in the database query/authorization boundary.

4. Approximate vector search is a separate preview path

CREATE VECTOR INDEX and VECTOR_SEARCH are still preview in boxed SQL Server 2025 and require PREVIEW_FEATURES. They trade exactness for search efficiency and have current limitations. The latest vector-index generation is not identical across SQL Server, Azure SQL Database and Fabric, so copy/pasting cloud examples into boxed SQL Server is unsafe.

USE ServiceHubAILab;GOSELECT name, value, value_for_secondaryFROM sys.database_scoped_configurationsWHERE name = N'PREVIEW_FEATURES';GO-- Development/test only after reviewing current limitations:-- ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;-- CREATE VECTOR INDEX IX_lab23_KnowledgeItem_embedding--   ON lab23.KnowledgeItem(embedding)--   WITH (METRIC='cosine', TYPE='DiskANN');-- VECTOR_SEARCH is then used for approximate nearest-neighbor retrieval.

Do not report an approximate-search latency number without also reporting recall/quality against an exact baseline. Faster wrong neighbors can be worse than a slower exact search. Current Microsoft documentation also notes that the newest vector-index generation is presently available only in Azure SQL Database and SQL database in Fabric, so boxed SQL Server 2025 preview behavior must be tested against its own current limitations. Some current index examples require a sufficiently sized synthetic corpus (for example at least 100 rows for the latest index generation); the eight-row mandatory lab intentionally does not pretend to be such a benchmark.

5. Re-embedding is a data migration

When the source text or model changes, decide whether to update in place, dual-write old/new embeddings during validation, or build a new table/index and cut over. A dual-version period makes A/B quality comparison possible and gives rollback. Treat model-version changes like schema/data migrations: inventory rows, backfill in batches, verify dimension and row counts, measure quality, then retire old vectors under a retention rule.

Authoritative references

• AI_GENERATE_CHUNKS
• AI_GENERATE_EMBEDDINGS
• CREATE EXTERNAL MODEL
• sys.external_models
• CREATE VECTOR INDEX (preview)
• Vector search and vector indexes