Full Implementation: Metadata-Driven Retrieval, Scoring, and Execution Pipeline

---

Introduction

By now, the pattern should be clear.

In Part 1, we solved control. In Part 2, we introduced retrieval. In Part 3, we refined similarity and scoring. But none of that matters unless it turns into a system that actually works end to end.

This is where most implementations stop short. They understand the pieces, but they never connect them. This post closes that gap.

Most importantly: the system improves by learning from metadata, not by retraining a model.

The Shift: From Generation to Metadata

What “AI” Actually Means Here

The intelligence is not coming from SQL Server itself. It comes from how you store query history, structure metadata, retrieve intelligently, and apply scoring and validation.

Consider this example. A user asks: monthly revenue for California. Instead of generating SQL, the system finds a query tagged revenue, monthly, confirms the relevant tables, reuses the aggregation logic, and applies a filter. That is the AI layer — not generation, but selection and adaptation.

Step 1 — Core Data Model (Metadata First)

Query History — The Learning Layer

This table captures what users ask, what SQL ran, and how it performed. It aligns with Microsoft’s observability guidance.

CREATE TABLE dbo.QueryHistory
(
    QueryHistoryId      INT IDENTITY PRIMARY KEY,
    UserQuestion        NVARCHAR(1000),
    NormalizedQuestion  NVARCHAR(500),
    GeneratedSQL        NVARCHAR(MAX),
    ExecutionDurationMs INT,
    RowCount            INT,
    Success             BIT,
    ErrorMessage        NVARCHAR(MAX),
    CreatedDate         DATETIME DEFAULT GETDATE()
);

Approved Query Library — System Memory

This is the foundation of the AI layer. Every column serves a specific retrieval or scoring purpose:

CREATE TABLE dbo.ApprovedQueryLibrary
(
    QueryId            INT IDENTITY PRIMARY KEY,
    NormalizedQuestion NVARCHAR(500),
    SQLText            NVARCHAR(MAX),
    TablesUsed         NVARCHAR(500),
    Tags               NVARCHAR(200),
    AvgDurationMs      INT,
    ExecutionCount     INT DEFAULT 0,
    SuccessRate        FLOAT,
    LastUsedDate       DATETIME
);

Column	Purpose
NormalizedQuestion	Matching anchor for retrieval
SQLText	Reusable query logic
TablesUsed	Improves structural scoring
Tags	Semantic grouping
AvgDurationMs	Performance awareness
SuccessRate	Trust signal
ExecutionCount	Confidence through usage

Step 2 — Normalization (Making Matching Work)

User input is inconsistent. Without normalization, matching fails, retrieval becomes unreliable, and scoring loses accuracy. This function strips common filler phrases to expose the core intent:

CREATE FUNCTION dbo.fn_NormalizeQuestion (@input NVARCHAR(1000))
RETURNS NVARCHAR(500)
AS
BEGIN
    DECLARE @output NVARCHAR(500)
    SET @output = LOWER(@input)
    SET @output = REPLACE(@output, 'how much did we make', '')
    SET @output = REPLACE(@output, 'what is', '')
    SET @output = REPLACE(@output, 'show me', '')
    RETURN LTRIM(RTRIM(@output))
END;

Step 3 — Retrieval (Finding Candidates)

Retrieve the top candidates from the library. Limiting results improves performance, reduces scoring cost, and aligns with SQL Server query processing behavior.

SELECT TOP 20 *
FROM dbo.ApprovedQueryLibrary
WHERE Tags LIKE '%revenue%'
   OR Tags LIKE '%monthly%'
ORDER BY LastUsedDate DESC;

Step 4 — Scoring (Choosing the Best Match)

The scoring query combines three signals: relevance from tags, structural fit from table names, and confidence from usage history.

SELECT
    QueryId,
    SQLText,
    (
        CASE WHEN Tags LIKE '%revenue%'  THEN 3 ELSE 0 END +
        CASE WHEN Tags LIKE '%monthly%'  THEN 2 ELSE 0 END +
        CASE WHEN TablesUsed LIKE '%Orders%' THEN 1 ELSE 0 END +
        (ExecutionCount / 10)
    ) AS Score
FROM dbo.ApprovedQueryLibrary
ORDER BY Score DESC;

Signal	Source	Weight
Tag match — revenue	Tags	+3
Tag match — monthly	Tags	+2
Table match — Orders	TablesUsed	+1
Usage confidence	ExecutionCount / 10	Variable

Step 5 — Decision Logic (Reuse vs Generate)

A clear threshold prevents bad matches and inconsistent results. Define the boundary explicitly:

• Score ≥ 5 → Reuse query from the library
• Score < 5 → Generate new SQL

This single decision point is what separates a controlled system from a guessing system.

Step 6 — Validation (Production Safety)

Validation is mandatory before any execution. At minimum, enforce these rules:

IF @sql NOT LIKE 'SELECT%'
BEGIN
    RAISERROR('Only SELECT statements allowed', 16, 1);
    RETURN;
END;

Also enforce: no system table access, no cross-database queries, and row return limits. This aligns with Microsoft’s SQL injection prevention guidance.

Step 7 — Execution

Once validated, execute using sp_executesql for parameterized, safe dynamic SQL:

EXEC sp_executesql @sql;

Step 8 — Logging (The Feedback Loop)

Every execution is logged. This is how the system improves over time — not through retraining, but through accumulated history.

INSERT INTO dbo.QueryHistory
(
    UserQuestion,
    NormalizedQuestion,
    GeneratedSQL,
    ExecutionDurationMs,
    RowCount,
    Success
)
VALUES
(
    @question,
    @normalized,
    @sql,
    @duration,
    @rows,
    1
);

Step 9 — Promotion (Controlled Learning)

Only high-quality, proven queries get promoted to the library. This query aggregates history and inserts validated patterns as reusable intelligence:

INSERT INTO dbo.ApprovedQueryLibrary
(
    NormalizedQuestion,
    SQLText,
    TablesUsed,
    Tags,
    AvgDurationMs,
    SuccessRate
)
SELECT
    NormalizedQuestion,
    GeneratedSQL,
    'Orders, Customers',
    'revenue, monthly',
    AVG(ExecutionDurationMs),
    AVG(CAST(Success AS FLOAT))
FROM dbo.QueryHistory
GROUP BY NormalizedQuestion, GeneratedSQL;

Step 10 — Orchestration (The Full Pipeline)

Instead of running steps manually, everything flows through one controlled stored procedure. This ensures consistent execution, centralized control, enforceable validation, and complete observability.

CREATE PROCEDURE dbo.usp_ProcessUserQuery
    @UserQuestion NVARCHAR(1000)
AS
BEGIN
    DECLARE @Normalized NVARCHAR(500)
    DECLARE @SQL       NVARCHAR(MAX)

    -- Step 1: Normalize
    SET @Normalized = dbo.fn_NormalizeQuestion(@UserQuestion)

    -- Step 2: Retrieve best match
    SELECT TOP 1 @SQL = SQLText
    FROM dbo.ApprovedQueryLibrary
    ORDER BY ExecutionCount DESC

    -- Step 3: Validate
    IF @SQL NOT LIKE 'SELECT%'
        RAISERROR('Invalid SQL', 16, 1)

    -- Step 4: Execute
    EXEC sp_executesql @SQL
END;

End-to-End Example

User input: monthly revenue for California

1. Normalize question → strip filler phrases
2. Retrieve candidates → tag match on revenue, monthly
3. Score results → select highest-scoring match
4. Validate SQL → confirm SELECT-only
5. Execute → return results
6. Log results → store duration, row count, success
7. Promote → add to library if quality threshold met

Performance Optimization and Scaling

Index the Library for Fast Retrieval

CREATE NONCLUSTERED INDEX IX_QueryLibrary_Search
ON dbo.ApprovedQueryLibrary (Tags, NormalizedQuestion);

This supports faster retrieval, better scaling, and reduced latency as the library grows.

Scaling Beyond Metadata

As usage grows, the system can be extended with:

• Embeddings — semantic vector matching alongside tag-based retrieval
• Hybrid retrieval — metadata scoring combined with vector similarity
• Expanded scoring models — weight recency, user feedback, or query complexity

Microsoft’s RAG architecture guide and vector search overview provide a roadmap for this evolution.

Conclusion

What you’ve built is not a query generator. It’s a controlled intelligence layer on top of SQL Server.

Instead of relying on randomness, it relies on structured metadata, proven query patterns, and measurable performance. The system improves not by retraining, but by remembering.

References

• Microsoft Docs – Azure Architecture Observability
• Microsoft Docs – SQL Server Query Processing Architecture Guide
• Microsoft Docs – SQL Injection Prevention
• Microsoft Docs – RAG Architecture Overview
• Microsoft Docs – Vector Search Overview