Azure AI Search vs SQL: Why I Ditched a Managed Search Service for a Database (and Why You Might Want To)

TL;DR: I built a Significant Terms AI system on top of Azure AI Search. It worked. It was powerful. It was also costing me a small fortune for a workload that, when I looked at it honestly, didn’t need half of what I was paying for. I migrated the whole thing to SQL Server and the results were better, faster, and a fraction of the price. Here’s the full breakdown of why.

A Bit of Background

The Significant Terms system is a project I’ve been building to analyse large volumes of documents and surface the terms, topics, and patterns that matter most within them. Think of it as an automated way to understand what a corpus of documents is actually about, without having to read all of them yourself.

The original architecture leaned heavily into Azure AI Search. I was using it to index processed PDF documents, run enrichment pipelines via Azure Logic Apps and Function Apps, and query the resulting index for significant term extraction. On paper, that’s exactly what Azure AI Search is designed for.

In practice, I kept running into a growing list of friction points. The kind that don’t show up in Microsoft’s documentation but absolutely show up in your Azure bill.

What Azure AI Search Is Actually Good At

Let’s be clear: Azure AI Search is genuinely impressive technology. If you’ve never used it, it’s a fully managed, cloud-scale search service built on Apache Lucene under the hood, with a very generous layer of Azure magic on top. Here’s what it brings to the table that you simply cannot replicate with a basic SQL query:

• Full-text search with BM25 ranking. Azure AI Search uses the BM25 relevance algorithm to rank search results by how closely they match a query. It handles stemming, stop words, and language analysis out of the box. You don’t configure any of that manually.
• Semantic ranking. On top of BM25, you can enable semantic ranking which uses a deep learning model to re-rank results by semantic relevance rather than just keyword frequency. For complex natural language queries, this is a significant quality improvement.
• Vector search (HNSW). Store dense vector embeddings alongside your documents and query them using approximate nearest neighbour search via the HNSW algorithm. Combined with keyword search, this gives you hybrid retrieval that’s genuinely hard to beat for RAG workloads and semantic similarity tasks.
• Cognitive Skills / AI enrichment pipelines. You can attach a skillset to your indexer that runs OCR, entity recognition, key phrase extraction, language detection, and custom Web API skills against every document as it gets ingested. The output gets written directly into your index fields. It’s a powerful way to enrich raw documents at index time without writing a separate processing pipeline.
• Faceted navigation and filtering. Facets let you build drill-down UIs where users can filter by category, date range, entity type, and so on, without writing complex SQL GROUP BY queries. It’s baked into the query syntax.
• Fully managed. No servers to patch, no index fragmentation to worry about, no vacuum jobs. You point it at a data source, configure an indexer, and Azure handles the rest.

For a Copilot Studio agent grounded against a SharePoint knowledge base (which I wrote about previously), Azure AI Search is absolutely the right tool. You need semantic ranking, you need vector search, you need the enrichment pipeline. The use case justifies the cost.

The Significant Terms project is a different beast entirely.

What Azure AI Search Is NOT Good At (For This Use Case)

Here is where I need to be honest about something that gets glossed over in a lot of Azure architecture content: managed services are not universally better. They are better at specific things. When your workload doesn’t fit the service’s strengths, you pay a premium for capabilities you aren’t using.

The Cost Problem

Azure AI Search pricing is based on the tier you provision. The Basic tier starts at around $75 USD per month. The Standard S1 tier, which you’ll need for production workloads with any decent document volume, starts at around $250 per month. That’s before you add Cognitive Services costs for the enrichment skills, storage costs for the index, and query unit costs if you’re hammering it hard.

For the Significant Terms project, I was processing documents through a Logic App pipeline that ingested PDFs, extracted text, and wrote structured term frequency data into an AI Search index. The actual search queries being run against that index were fairly simple: give me the top N terms by frequency for document set X, filtered by date range and document type.

That is a GROUP BY query. I was paying for a distributed search engine to do GROUP BY queries.

The Aggregation Problem

Azure AI Search is fundamentally a document retrieval engine. It is designed to find relevant documents given a query. It is not designed to aggregate term frequencies across a corpus of thousands of documents and return ranked statistical summaries.

You can do faceted aggregations in AI Search using the facets query parameter, but it has hard limits. By default you’ll get a maximum of 10 facet values returned per field unless you explicitly set a higher count, and even then you’re working against a system that wasn’t designed for this pattern. Running a query like “give me the top 500 significant terms across 10,000 documents, weighted by TF-IDF score, filtered to documents processed in the last 30 days” is not what the product is optimised for.

In SQL, that’s a straightforward query with a proper index on the right columns. In Azure AI Search, it’s a workaround.

The Latency and Consistency Problem

Azure AI Search uses an indexer-based ingestion model. You push documents into a data source (Blob Storage, SQL, Cosmos DB, etc.), and the indexer polls on a schedule or is triggered manually. There is an inherent delay between writing data and it being queryable.

For the Significant Terms workflow, I was processing documents in near-real-time through a Logic App pipeline. The expectation was that as soon as a document had been processed, its terms would be available for analysis. With AI Search, even with indexer polling set to the minimum interval, you’re looking at a 5-minute or longer lag. With SQL, you write a row and it’s immediately queryable. That consistency model is a much better fit.

The Schema Rigidity Problem

Azure AI Search indexes have a fixed schema defined at creation time. Adding new fields requires rebuilding the index or using a reset-and-repopulate approach. For a project that was evolving quickly, where I was regularly iterating on what data I wanted to capture per document and per term, this was a genuine pain.

SQL schema migrations with ALTER TABLE are not glamorous, but they work. You can add a nullable column to a table with 2 million rows in seconds and immediately start writing to it. Rebuilding an AI Search index with 2 million documents takes considerably longer.

What SQL Gives You Instead

I migrated the Significant Terms storage layer to Azure SQL Database. Here’s what changed and why it mattered.

Aggregation is a First-Class Citizen

The core query pattern for Significant Terms is: across a set of documents, which terms appear significantly more often than you would expect by chance? This is essentially a statistical significance calculation on top of term frequency data.

In SQL, you write that as a proper aggregation query with GROUP BY, HAVING, ORDER BY, and window functions if you need them. The query planner can optimise it properly. You can add a covering index on the columns you’re filtering and sorting on, and the engine will use it. Here’s a simplified version of the kind of query I’m now running:

SELECT
  t.term,
  COUNT(DISTINCT dt.document_id)                          AS document_frequency,
  SUM(dt.term_count)                                      AS total_occurrences,
  CAST(COUNT(DISTINCT dt.document_id) AS FLOAT) / @total_docs AS doc_frequency_ratio
FROM
  document_terms dt
  INNER JOIN terms t    ON t.term_id     = dt.term_id
  INNER JOIN documents d ON d.document_id = dt.document_id
WHERE
  d.processed_date >= @start_date
  AND d.document_type = @doc_type
GROUP BY
  t.term
HAVING
  COUNT(DISTINCT dt.document_id) >= @min_doc_frequency
ORDER BY
  doc_frequency_ratio DESC;

Try expressing that cleanly in an Azure AI Search query. You can’t, not without multiple round trips, client-side aggregation, or both.

Cost

Azure SQL Database on the General Purpose tier with a couple of vCores costs a fraction of what I was paying for AI Search. And because the query pattern is predictable, I can right-size the compute tier confidently. I also previously switched from GPT-4o to GPT-4o-mini for the AI processing steps in this pipeline, which cut inference costs significantly. Combined with the SQL migration, the overall running cost of this system dropped dramatically.

Transactional Consistency and Immediate Queryability

When the Logic App writes processed term data to SQL, it’s immediately queryable. No indexer delay, no polling schedule, no “wait 5 minutes and retry” logic needed in the consuming application. The audit workflow I migrated from Azure Table Storage to SQL also benefits from this, because you get proper ACID transactions rather than the eventual consistency model Table Storage uses.

Proper Relational Modelling

The data in Significant Terms has natural relational structure: documents, terms, and the many-to-many relationship between them with a frequency count as an attribute on the join. SQL is literally designed for this. Azure AI Search stores documents as flat JSON blobs with some field-level analysis on top. Trying to model relational data in a document store means either denormalising everything (which wastes storage and makes updates painful) or doing multiple queries and joining client-side (which is slow and fragile).

The Direct Comparison

When You Should Keep Azure AI Search

I want to be absolutely clear that this is not an “Azure AI Search is bad” post. There are workloads where it is unambiguously the right choice and SQL would be the wrong one.

Keep Azure AI Search if you are building a semantic search or RAG system where users submit natural language queries and expect ranked, contextually relevant results. The BM25 + semantic reranker + vector HNSW combination is genuinely powerful and not something you replicate easily in SQL.

Keep it if you are using the AI enrichment skillset pipeline to run OCR, entity extraction, or custom ML skills over documents at ingest time. That pattern is architecturally elegant and saves you from building a separate enrichment service.

Keep it if you need faceted navigation for a user-facing search UI. Keep it if your data is unstructured and you can’t define a clean relational schema ahead of time.

When You Should Consider SQL Instead

Consider SQL if your core query pattern is aggregation rather than document retrieval. If you are grouping, counting, summing, and ranking structured fields, a relational database will be faster, cheaper, and easier to maintain than a search index.

Consider SQL if your data has natural relational structure that you are fighting against by shoving it into a flat document model. Denormalisation in a search index is a symptom that you might be using the wrong tool.

Consider SQL if you need write consistency and your consumers need to query data immediately after it is written. If you are building an event-driven pipeline where data flows in continuously and needs to be available for analysis in real time, an indexer-based pull model is the wrong architecture.

And consider SQL if the cost of Azure AI Search is hard to justify against what you’re actually getting from it. Every Azure service has a cost/value point. If you’re not extracting value from the semantic ranking, the vector search, and the enrichment pipeline, you’re paying for features you don’t need.

The Migration in Practice

The Schema DDL

The schema I landed on is a normalised three-table design. Nothing clever, nothing exotic. Just correct relational modelling.

-- Documents table: one row per processed source document
CREATE TABLE documents (
    document_id       INT IDENTITY(1,1) PRIMARY KEY,
    source_filename   NVARCHAR(512)    NOT NULL,
    document_type     NVARCHAR(100)    NOT NULL,
    processed_date    DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    total_term_count  INT              NOT NULL DEFAULT 0,
    processing_status NVARCHAR(50)     NOT NULL DEFAULT 'pending',
    model_version     NVARCHAR(50)     NULL
);
-- Terms table: unique term strings, deduplicated globally
CREATE TABLE terms (
    term_id           INT IDENTITY(1,1) PRIMARY KEY,
    term_value        NVARCHAR(256)    NOT NULL,
    CONSTRAINT UQ_term_value UNIQUE (term_value)
);
-- Junction table: many-to-many with frequency as an attribute on the relationship
CREATE TABLE document_terms (
    document_id       INT NOT NULL REFERENCES documents(document_id),
    term_id           INT NOT NULL REFERENCES terms(term_id),
    term_count        INT NOT NULL DEFAULT 1,
    CONSTRAINT PK_document_terms PRIMARY KEY (document_id, term_id)
);
-- Index to support the top-N terms query pattern efficiently
CREATE INDEX IX_document_terms_term ON document_terms (term_id, term_count DESC)
    INCLUDE (document_id);
CREATE INDEX IX_documents_date_type ON documents (processed_date DESC, document_type)
    INCLUDE (document_id, total_term_count);

A few things worth calling out here. The primary key on document_terms is a composite (document_id, term_id), which is the clustered index and covers lookups by document efficiently. The non-clustered index on (term_id, term_count DESC) with document_id as an included column is what makes the top-N aggregation query fast. The query planner can seek into that index by term_id, scan in descending term_count order, and stop as soon as it has enough rows. Without that index, you’d get a full table scan on document_terms every time.

The UNIQUE constraint on terms.term_value is important too. It lets you use MERGE or INSERT…WHERE NOT EXISTS safely from the Logic App without worrying about race conditions creating duplicate term entries when multiple documents are being processed in parallel.

The Logic App SQL Insert Pattern

The Logic App that handles document term extraction uses the SQL Server connector to write data to these tables. The key pattern is a three-step write per document: upsert the document record, bulk-upsert the term strings to get their IDs back, then insert the junction rows.

// Step 1: Insert or update the document record
{
  "Insert_Document_Record": {
    "type": "ApiConnection",
    "inputs": {
      "method": "post",
      "path": "/datasets/<YOUR_SQL_CONNECTION>/procedures/@{encodeURIComponent('[dbo].[usp_UpsertDocument]')}",
      "body": {
        "SourceFilename": "@{triggerBody()?['filename']}",
        "DocumentType":   "@{triggerBody()?['documentType']}",
        "ModelVersion":   "gpt-4o-mini"
      }
    }
  }
}

-- Step 2: Upsert each term - HOLDLOCK prevents duplicate rows under parallel processing
MERGE terms WITH (HOLDLOCK) AS target
USING (SELECT @TermValue AS term_value) AS source
ON    target.term_value = source.term_value
WHEN NOT MATCHED THEN INSERT (term_value) VALUES (@TermValue);
SELECT term_id FROM terms WHERE term_value = @TermValue;
-- Step 3: Insert into junction table
INSERT INTO document_terms (document_id, term_id, term_count)
VALUES (@DocumentId, @TermId, @TermCount);

The HOLDLOCK hint on the MERGE is important. Without it, under high concurrency (multiple Logic App instances processing documents in parallel), you can get a race condition where two instances both read “term not found”, both try to INSERT, and one fails with a unique constraint violation. HOLDLOCK serialises concurrent merges against the same term row and avoids that entirely.

The Logic App iterates over the extracted terms array with a For Each action, with concurrency on the For Each set to 1 to keep writes serial per document. Documents themselves are processed in parallel across Logic App instances with higher concurrency at the workflow level.

Query Performance and Execution Plans

One of the biggest practical advantages of SQL over Azure AI Search for this workload is that you can actually see what the query engine is doing. In AI Search, relevance scoring is a partially opaque BM25 calculation. You can see scores, but tuning why a result ranked where it did requires working through scoring profiles, boosting weights, and field-level analysis configuration. It’s doable but not transparent.

In SQL, you run SET STATISTICS IO ON and check the execution plan. Here’s what a well-optimised significant terms query should look like:

• Index Seek on IX_documents_date_type — the outer loop. The query planner should seek directly into the documents filtered by date range and document_type rather than scanning the full table. If you’re seeing a table scan here, your filtered result set is too large and you need a tighter date range or more selective filter.
• Nested Loop Join to document_terms — joining from the document result set into the junction table using the clustered primary key (document_id, term_id). This should be a seek, not a scan.
• Key Lookup or Index Seek to terms — resolving term_id back to the term_value string. If term_value is not in a covering index and you’re fetching it frequently, consider including it in the junction table index.
• Stream Aggregate or Hash Aggregate on term_id — the GROUP BY. For large result sets the planner will choose Hash Aggregate. For smaller, pre-sorted result sets it may use Stream Aggregate which is cheaper. Adding an ORDER BY term_id to the underlying data access can tip it towards Stream Aggregate in the right scenarios.

In practice, with a few hundred thousand documents and tens of millions of document_terms rows, the top-N significant terms query runs in under 500ms on a General Purpose 2 vCore Azure SQL Database instance.

Final Thoughts

The lesson I keep relearning on this project is that managed cloud services are not automatically better just because they are newer or more specialised. Azure AI Search is an outstanding product for the workloads it was designed for. But when you find yourself working around a service’s design rather than with it, that’s usually a signal that the tool selection needs revisiting.

For the Significant Terms system, SQL was the boring-but-correct choice. The queries are simpler, the cost is lower, the consistency model is better, and the operational overhead is not meaningfully higher given that I’m using Azure SQL Database as a PaaS service anyway.

If you’re running Azure AI Search for a workload that’s primarily doing aggregation analytics over structured term data, it’s worth asking yourself: is this actually a search problem, or is it a data problem? If it’s the latter, SQL will serve you better.

Drop any questions in the comments below. Happy to go deeper on the stored procedure design, the Logic Apps concurrency model, or the query tuning if that’s useful.