XML, Spatial, Full-Text, and Vector-Oriented Indexing/Access Concepts

Learning outcomes

Not every searchable value should be forced into an ordinary rowstore B+ tree. XML documents, spatial objects, linguistic text, and high-dimensional vectors have different search semantics and therefore different access structures. ServiceHub uses all four concepts: XML vendor diagnostics, service-area geometry, technician notes, and small embedding vectors for semantic categorization. This lesson focuses on architectural differences, prerequisites, and feature status—not on pretending one specialized index replaces another.

1. XML indexes persist a shredded search structure

SQL Server stores the xml value in a binary representation. A primary XML index persists a shredded node representation so repeated XQuery does not need to shred every document at runtime. A primary XML index requires the base table to have a clustered primary key. Secondary XML indexes—PATH, VALUE, and PROPERTY—build on the primary XML index for different access patterns.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab09') IS NULL EXEC(N'CREATE SCHEMA lab09 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab09.DeviceDiagnosticXml;CREATE TABLE lab09.DeviceDiagnosticXml(  diagnostic_id int NOT NULL,  work_order_id bigint NOT NULL,  payload xml NOT NULL,  CONSTRAINT PK_DeviceDiagnosticXml    PRIMARY KEY CLUSTERED(diagnostic_id));INSERT lab09.DeviceDiagnosticXml VALUES(1,1001,N'<device model="AX7"><fault code="E17"/><temp>72</temp></device>'),(2,1002,N'<device model="BX2"><fault code="W03"/><temp>55</temp></device>');GOCREATE PRIMARY XML INDEX PXML_DeviceDiagnosticXmlON lab09.DeviceDiagnosticXml(payload);CREATE XML INDEX PXML_DeviceDiagnosticXml_PATHON lab09.DeviceDiagnosticXml(payload)USING XML INDEX PXML_DeviceDiagnosticXml FOR PATH;GOSELECT diagnostic_idFROM lab09.DeviceDiagnosticXmlWHERE payload.exist('/device/fault[@code="E17"]')=1;SELECT name,secondary_type_descFROM sys.xml_indexesWHERE object_id=OBJECT_ID(N'lab09.DeviceDiagnosticXml');GO

XML indexes add substantial storage and write maintenance. Microsoft's current guidance also documents selective XML indexes as an alternative when only known paths need acceleration. Index the query shape you actually have rather than creating every secondary XML index.

2. Spatial indexes map 2-D space into a B-tree-compatible tessellation

geometry and geography values are two-dimensional objects, but SQL Server still needs a linear search structure. Spatial indexing tessellates space into hierarchical grid cells and stores cell relationships in a B-tree-backed structure. For compatibility level 110+, auto-grid schemes are the default. A spatial predicate must also have a supported form, and matching Spatial Reference Identifiers (SRIDs) matter because many methods return NULL for mismatched SRIDs.

DROP TABLE IF EXISTS lab09.ServiceZone;CREATE TABLE lab09.ServiceZone(  zone_id int NOT NULL PRIMARY KEY,  zone_name nvarchar(80) NOT NULL,  shape geometry NOT NULL);INSERT lab09.ServiceZone VALUES(1,N'Central',geometry::STGeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))',0)),(2,N'North',geometry::STGeomFromText('POLYGON((0 10,10 10,10 20,0 20,0 10))',0));GOCREATE SPATIAL INDEX SIX_ServiceZone_ShapeON lab09.ServiceZone(shape)USING GEOMETRY_AUTO_GRID;GODECLARE @p geometry=geometry::Point(4,4,0);SELECT zone_id,zone_nameFROM lab09.ServiceZoneWHERE shape.STContains(@p)=1;GO

Even when a predicate is supported, the optimizer can choose another plan based on cost. The goal is not to force the spatial index but to validate a supported predicate, inspect showplan, and tune tessellation/cells-per-object only when real spatial data requires it.

3. Full-Text is linguistic search, not LIKE with a bigger index

Full-Text Search tokenizes text with language-aware word breakers/stemmers and supports CONTAINS, FREETEXT, CONTAINSTABLE, and FREETEXTTABLE. A full-text-enabled table needs a unique, single-column, non-nullable key index. The Full-Text component must also be installed/configured; that is a free SQL Server engine component, but it may not be present in every local installation or container image.

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS is_fulltext_installed;GO-- If 1, a free local optional lab can create a full-text catalog/index.-- If 0, do not fabricate success; study the metadata/architecture and-- install the Full-Text component in a disposable lab environment first.GO

This conditional path preserves reproducibility: no paid service is required, but the lesson does not assume a component that might not have been installed.

4. SQL Server 2025 vectors: exact search GA, ANN index preview

The SQL Server 2025 VECTOR(n) type stores fixed-dimensional vectors in an optimized binary format. VECTOR_DISTANCE performs exact distance computation and does not use a vector index. Approximate nearest-neighbor (ANN) indexing/search is a different feature: CREATE VECTOR INDEX and VECTOR_SEARCH remain preview in SQL Server 2025 CU7 and require PREVIEW_FEATURES. The current docs also note that the newest vector-index version is presently available only in Azure SQL Database/Fabric, so SQL Server 2025 on-premises capabilities must be checked against the exact build.

DROP TABLE IF EXISTS lab09.KnowledgeSnippet;CREATE TABLE lab09.KnowledgeSnippet(  snippet_id int NOT NULL PRIMARY KEY,  title nvarchar(100) NOT NULL,  embedding vector(3) NOT NULL);INSERT lab09.KnowledgeSnippet VALUES(1,N'Pump vibration','[0.95,0.10,0.05]'),(2,N'Electrical noise','[0.10,0.90,0.10]'),(3,N'Bearing wear','[0.85,0.15,0.10]');DECLARE @q vector(3)='[0.90,0.12,0.08]';SELECT snippet_id,title,       VECTOR_DISTANCE('cosine',@q,embedding) AS distanceFROM lab09.KnowledgeSnippetORDER BY distance;GO

-- SQL SERVER 2025 PREVIEW ONLY. Do not enable in production by default.-- ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;-- GO-- CREATE VECTOR INDEX IX_KnowledgeSnippet_Embedding-- ON lab09.KnowledgeSnippet(embedding)-- WITH (METRIC='cosine', TYPE='DiskANN');-- GO-- VECTOR_SEARCH syntax/version can change while preview remains active.GO

5. Production judgment and cleanup

Specialized indexes are justified by specialized query semantics. XML indexes can be storage-heavy; spatial indexes depend on geometry/geography distribution and predicate shape; Full-Text has separate population/change-tracking operations; vector ANN indexes add asynchronous maintenance and preview risk. Monitor each with its own catalog/DMVs rather than expecting sys.dm_db_index_usage_stats to describe every specialized structure—the usage DMV explicitly excludes spatial indexes.

DROP TABLE IF EXISTS lab09.KnowledgeSnippet;DROP TABLE IF EXISTS lab09.ServiceZone;DROP TABLE IF EXISTS lab09.DeviceDiagnosticXml;GO

Authoritative references

• Create XML indexes — primary/secondary requirements
• Spatial indexes overview — tessellation and predicate rules
• Create and manage Full-Text indexes — key/component requirements
• Vector data type — SQL Server 2025 vector storage
• VECTOR_DISTANCE — exact vector distance
• CREATE VECTOR INDEX (Preview) — ANN index status and PREVIEW_FEATURES
• SQL Server 2025 build versions — servicing baseline