SQL Server 2025 Native Vector and JSON: Pragmatic ERP Search Without a Dedicated Vector DB

When enterprise teams explore Retrieval-Augmented Generation (RAG) and intelligent document search for ERP, the default recommendation is often to deploy a dedicated vector database—such as Pinecone, Qdrant, or Milvus.

For a greenfield chatbot querying public web pages, that architecture is fine. But for an enterprise ERP running general ledger accounts, customer orders, and sensitive payroll vouchers, introducing a separate vector database carries an immense operational tax.

With SQL Server 2025 (Engine Version 17.x, Compatibility Level 170) and EF Core 10, Microsoft introduced native vector data types, DiskANN vector indexes, and a true binary json data type directly into the SQL Server engine.

Before adding another stateful system to your infrastructure, it is worth examining why keeping semantic search inside your primary relational engine is usually the more disciplined engineering choice for ERP.

The Operational Tax of a Second Database

In an enterprise ERP system, your database does far more than store records. It enforces:

Transactional Integrity (ACID): When a purchase order is updated, canceled, or re-allocated, the change is atomic. If your search index lives in an external database, you must build an outbox publisher or CDC pipeline to sync vector embeddings. The moment that pipeline lags, users see stale or phantom search results.
Multi-Tenant Security and Row-Level Security (RLS): ERP systems partition data by tenant, subsidiary, and security clearance. Duplicating those authorization matrices in an external vector database is error-prone. A single misconfigured filter can leak confidential salary structures or vendor discounts across tenant boundaries.
Point-in-Time Recovery (PITR) and Disaster Recovery: If a database corruption or mistaken batch posting forces you to restore your primary database to a backup from three hours ago, an external vector database remains at the current timestamp. Re-synchronizing millions of vector embeddings across unsynchronized states is an operational nightmare.

Keeping embeddings in SQL Server means vectors back up, restore, fail over, and replicate in the exact same transaction logs as your financial ledger.

Native JSON in SQL Server 2025 (Compatibility Level 170)

In earlier versions of SQL Server, JSON was stored as plain NVARCHAR(MAX) or VARCHAR(MAX). Every JSON_VALUE or JSON_QUERY call required parsing the string repeatedly during execution.

SQL Server 2025 introduces the native json data type:

Parse-Once Binary Storage: Stored in an optimized binary format using UTF-8 encoding (Latin1_General_100_BIN2_UTF8).
Reduced Storage Footprint: Binary JSON compresses keys and structures, saving 30–50% space compared to Unicode text.
Native JSON Indexes: You can create secondary indexes directly on JSON document paths (CREATE JSON INDEX) without relying solely on computed columns.

-- Upgrading the database to compatibility level 170
ALTER DATABASE ErpProductionDb SET COMPATIBILITY_LEVEL = 170;

-- Creating an ERP document table with native JSON metadata
CREATE TABLE dbo.PurchaseRequisitions (
    RequisitionId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    TenantId UNIQUEIDENTIFIER NOT NULL,
    DocumentNumber NVARCHAR(50) NOT NULL,
    TotalAmount DECIMAL(18, 4) NOT NULL,
    Attributes JSON NOT NULL, -- Native binary JSON
    CreatedUtc DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);

-- Fast native JSON index on department code and approval tags
CREATE JSON INDEX IX_Requisitions_Attributes ON dbo.PurchaseRequisitions(Attributes);

Native Vectors: Storing Embeddings Alongside Ledger Data

SQL Server 2025 adds the VECTOR(n) data type, where n defines the dimension count (e.g., 1536 for OpenAI's text-embedding-3-small or smaller local embedding models).

Vectors are stored compactly in binary format and can be compared using native similarity functions like VECTOR_DISTANCE():

-- Adding a vector column for semantic document retrieval
ALTER TABLE dbo.PurchaseRequisitions
ADD DocumentEmbedding VECTOR(1536) NULL;

-- Calculating cosine similarity to a search embedding
DECLARE @QueryVector VECTOR(1536) = /* embedding passed from .NET service */;

SELECT TOP 10
    RequisitionId,
    DocumentNumber,
    TotalAmount,
    VECTOR_DISTANCE('cosine', DocumentEmbedding, @QueryVector) AS DistanceScore
FROM dbo.PurchaseRequisitions
WHERE TenantId = @CurrentTenantId
ORDER BY DistanceScore ASC;

For large datasets (hundreds of thousands or millions of rows), sequential table scans become a bottleneck. SQL Server 2025 supports DiskANN-backed vector indexing (CREATE VECTOR INDEX), enabling fast Approximate Nearest Neighbor (ANN) search within bounded memory budgets.

Hybrid Search: Combining Relational Constraints with Semantic Similarity

Pure vector search is rarely what business users want. If a procurement manager types "Find high-value HVAC equipment maintenance contracts from Q3," the query has both:

Relational / Exact constraints: TenantId = @TenantId, DocumentType = 'Contract', TotalAmount > 10000, PostingDate BETWEEN '2026-07-01' AND '2026-09-30'.
Semantic / Fuzzy intent: Meaning matching "HVAC equipment maintenance".

With SQL Server, you evaluate both dimensions in a single query execution plan. The database optimizer applies tenant security and date filters first, drastically narrowing the row set before calculating vector distances or traversing vector indexes:

CREATE PROCEDURE dbo.SearchProcurementDocuments
    @TenantId UNIQUEIDENTIFIER,
    @MinAmount DECIMAL(18, 4),
    @QueryVector VECTOR(1536),
    @Limit INT = 10
AS
BEGIN
    SET NOCOUNT ON;

    WITH FilteredDocuments AS (
        SELECT 
            RequisitionId,
            DocumentNumber,
            TotalAmount,
            Attributes,
            VECTOR_DISTANCE('cosine', DocumentEmbedding, @QueryVector) AS VectorDistance
        FROM dbo.PurchaseRequisitions
        WHERE TenantId = @TenantId
          AND TotalAmount >= @MinAmount
          AND DocumentEmbedding IS NOT NULL
    )
    SELECT TOP (@Limit)
        RequisitionId,
        DocumentNumber,
        TotalAmount,
        JSON_VALUE(Attributes, '$.Department') AS Department,
        VectorDistance
    FROM FilteredDocuments
    ORDER BY VectorDistance ASC;
END;

Consuming Native Vectors in .NET with EF Core 10

Entity Framework Core 10 ships with native support for SQL Server vectors via the Microsoft.EntityFrameworkCore.SqlServer.VectorSearch package. It introduces the SqlVector<float> struct, which maps directly to VECTOR(n):

public class PurchaseRequisition
{
    public Guid RequisitionId { get; set; }
    public Guid TenantId { get; set; }
    public string DocumentNumber { get; set; } = string.Empty;
    public decimal TotalAmount { get; set; }
    public SqlVector<float>? DocumentEmbedding { get; set; }
}

// In the DbContext configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<PurchaseRequisition>(entity =>
    {
        entity.Property(e => e.DocumentEmbedding)
              .HasColumnType("vector(1536)");
              
        // Enforce global multi-tenant query filter
        entity.HasQueryFilter(e => e.TenantId == _currentTenantProvider.TenantId);
    });
}

When running queries in LINQ, EF Core translates EF.Functions.VectorDistance directly into SQL Server's VECTOR_DISTANCE() function:

var queryEmbedding = new SqlVector<float>(await embeddingGenerator.GenerateVectorAsync(userPrompt));

var topMatches = await dbContext.PurchaseRequisitions
    .Where(r => r.TotalAmount >= 10000m)
    .OrderBy(r => EF.Functions.VectorDistance("cosine", r.DocumentEmbedding!, queryEmbedding))
    .Take(5)
    .Select(r => new { r.RequisitionId, r.DocumentNumber, r.TotalAmount })
    .ToListAsync(cancellationToken);

Your tenant filters, soft-delete rules, and transactional boundaries continue to work seamlessly without writing custom synchronization middleware.

When Should You Still Consider a Dedicated Vector DB?

There are genuine scenarios where a dedicated vector engine is appropriate:

When you are indexing billions of vectors with sub-millisecond nearest-neighbor requirements across unpartitioned datasets.
When your data is non-relational and lives entirely outside your transactional database (e.g. indexing millions of video transcripts or audio waveforms).
When you do not need transactional consistency or joint relational filtering.

For enterprise ERP, however, those conditions rarely apply. Most ERP document archives, product catalogs, and journal narratives range from tens of thousands to a few million rows per tenant.

Final Thought

Simplicity is a virtue in systems engineering. Every new data store added to your production stack introduces new backup schedules, network latency, failure modes, and monitoring dashboards.

With SQL Server 2025's native json data type and VECTOR(n) support, you can deliver modern semantic search and AI retrieval while preserving the ACID guarantees, backup procedures, and tenant security models your enterprise already relies on.