Create a Realistic Database

The AI layer is only as good as the data it reasons about. A sandbox with empty tables or identical rows will produce unreliable results and give you false confidence before production. At minimum your schema should include Customers (with status flags, geography, and segments), Orders (with dates, amounts, and statuses), Products (with categories and attributes), and OrderItems (the bridge between orders and products).

Use realistic, varied data. Volume and variety are what expose the edge cases you need to catch early.

Extract Metadata

This is the foundation everything else depends on. The goal is to make SQL Server self-describing so the AI understands structure without guessing. Use the system catalog views:

SELECT
    t.name  AS TableName,
    c.name  AS ColumnName,
    tp.name AS DataType
FROM sys.tables t
JOIN sys.columns c  ON t.object_id = c.object_id
JOIN sys.types tp   ON c.user_type_id = tp.user_type_id;

Feed this into your AI prompt as structured JSON:

{
  "table": "Orders",
  "columns": [
    {"name": "OrderDate", "type": "date"},
    {"name": "NetAmount", "type": "decimal"}
  ]
}

Add Business Meaning

This stage separates a system that technically works from one that produces correct and trustworthy results. SQL Server tells you NetAmount DECIMAL(12,2). That means nothing to an AI model. You have to define the meaning: NetAmount = revenue after discounts. Safe to SUM. Not a cost column.

Build a semantic catalog directly in your database:

CREATE TABLE AI_MetadataCatalog
(
    TableName      SYSNAME,
    ColumnName     SYSNAME,
    Description    NVARCHAR(1000),
    IsAggregatable BIT,
    IsFilterable   BIT,
    IsSensitive    BIT
);

Enforce Data Security at Every Layer

The IsSensitive flag in your metadata catalog is a start. But a flag with no enforcement behind it is just documentation. Real security requires real controls at multiple layers.

Dynamic Data Masking for columns that should be partially visible:

ALTER TABLE Customers
ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');

Column-level permissions to block access entirely:

DENY SELECT ON Customers(SSN) TO [AI_ReadOnly_User];

Row-Level Security to scope what each user or role can see:

CREATE FUNCTION dbo.fn_AI_SecurityPredicate(@RegionID INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS Result
WHERE @RegionID = CAST(SESSION_CONTEXT(N'RegionID') AS INT);

CREATE SECURITY POLICY AI_RowFilter
ADD FILTER PREDICATE dbo.fn_AI_SecurityPredicate(RegionID)
ON dbo.Orders;

Build the Prompt Layer and Version It

The prompt layer is where you define the rules of engagement. Your prompt should explicitly define which tables are in scope, which columns are available, what the relationships are, what business rules apply, and what query patterns are acceptable. The AI does not decide the logic. You do.

Version your prompts. When something breaks in production, you need to know exactly what the prompt looked like at the time of the bad query.

CREATE TABLE AI_PromptVersions
(
    VersionID    INT IDENTITY PRIMARY KEY,
    VersionLabel NVARCHAR(50),
    PromptText   NVARCHAR(MAX),
    CreatedAt    DATETIME2 DEFAULT SYSUTCDATETIME(),
    IsActive     BIT DEFAULT 0
);

Store prompt versions alongside your audit log. Log which version generated each query. Without this, debugging production failures is guesswork.

Generate Read-Only SQL

Start with SELECT-only queries. No exceptions in the early stages. This is where AI-generated SQL is most valuable — ad hoc analytics, self-service reporting, exploratory data analysis — and it is also the safest boundary to enforce while you are building confidence in the system.

Validate Before Execution

Never trust generated SQL. Treat every query the AI produces the same way you would treat input from an end user — because in a meaningful sense, it is.

Minimum validation rules:

• Query must begin with SELECT
• Block INSERT, UPDATE, DELETE, DROP, ALTER, EXEC, TRUNCATE, xp_
• Allow only whitelisted tables and schemas
• Reject multiple statements separated by semicolons
• Enforce a maximum row return limit
• Reject queries referencing system tables such as sys. or INFORMATION_SCHEMA

Connection Management and Always On Routing

Use a dedicated read-only connection account with the minimum permissions required. Route AI queries to a secondary replica — AI workload should never touch your primary replica in an Always On environment.

Configure your connection string to target the AG listener with ApplicationIntent=ReadOnly:

Server=AG_Listener;Database=YourDB;ApplicationIntent=ReadOnly;

Understand your acceptable lag tolerance. For most analytics queries a few seconds of replication lag is acceptable. If users expect real-time data, document that boundary clearly and set expectations accordingly.

Define your connection pool limits explicitly and monitor for pool exhaustion. In high-concurrency scenarios, a queue-and-throttle approach at the API layer prevents connection saturation at the database level.

Isolate AI Workload with Resource Governor

Without workload isolation, a resource-intensive AI-generated query competes directly with your OLTP workload for CPU and memory. That is not acceptable in production. Configure a dedicated workload group:

CREATE RESOURCE POOL AI_QueryPool
WITH
(
    MAX_CPU_PERCENT    = 20,
    MAX_MEMORY_PERCENT = 15
);

CREATE WORKLOAD GROUP AI_WorkloadGroup
USING AI_QueryPool;

CREATE FUNCTION dbo.fn_AI_Classifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE
        WHEN SUSER_NAME() = 'AI_ReadOnly_User' THEN 'AI_WorkloadGroup'
        ELSE 'Default'
    END
END;

ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.fn_AI_Classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

AI queries are now capped at 20% CPU and 15% memory. Adjust the percentages based on your environment. The point is that the cap exists and is enforced at the engine level, not hoped for at the application level.

Prevent Runaway Queries

Resource Governor protects the instance. These controls protect individual query execution. Always inject a TOP N clause into generated queries unless pagination is explicitly implemented. Never allow unbounded result sets.

SET LOCK_TIMEOUT 5000;

Also consider enforcing SET QUERY_GOVERNOR_COST_LIMIT, MAXDOP limits, and row count caps using TOP or FETCH. Monitor TempDB spill pressure from AI-generated sorts, aggregations, and hash joins:

SELECT
    SUM(user_object_reserved_page_count)     * 8 AS user_objects_kb,
    SUM(internal_object_reserved_page_count) * 8 AS internal_objects_kb
FROM sys.dm_db_file_space_usage;

Address Execution Plan Cache Bloat

Every AI-generated SQL query is a unique string. Unlike parameterized queries or stored procedures, there is less plan reuse when query text varies heavily. Each query can compile fresh and compiled plans can accumulate in cache — potentially thousands per day.

Monitor plan cache pressure from AI-generated queries:

SELECT TOP 20
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS AvgCPU,
    SUBSTRING(st.text, 1, 200)               AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%AI_Generated%'
ORDER BY qs.total_worker_time DESC;

Mitigation strategies: tag generated queries with a comment header like /* AI_Generated */, enable Optimize for Ad Hoc Workloads, and route common query patterns through parameterized wrapper procedures.

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

Log Everything and Build Real Observability

Every query — generated, validated, executed, or rejected — should be logged. But logging is not the same as observability.

CREATE TABLE AI_QueryAudit
(
    AuditID       INT IDENTITY PRIMARY KEY,
    PromptVersion NVARCHAR(50),
    Question      NVARCHAR(1000),
    SQLText       NVARCHAR(MAX),
    Executed      BIT,
    RowsReturned  INT NULL,
    DurationMS    INT NULL,
    ErrorMsg      NVARCHAR(500) NULL,
    CreatedAt     DATETIME2 DEFAULT SYSUTCDATETIME()
);

Beyond the audit log, actively monitor:

• Query duration trends — a spike often signals schema drift or a prompt change
• Row count monitoring — a query returning 5 million rows is a signal, not just a slow query
• Validation rejection rate — a rising rejection rate means your prompt or schema has drifted
• Error rate by question category — some question types will fail more, becoming your semantic layer backlog
• API call volume and cost — every question is an API call with a dollar cost

Build in Learning

Every approved, validated, correctly executing query is a training example. Store them:

CREATE TABLE AI_QueryExamples
(
    ExampleID INT IDENTITY PRIMARY KEY,
    Question  NVARCHAR(1000),
    SQLText   NVARCHAR(MAX),
    Approved  BIT DEFAULT 0,
    CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);

Feed approved examples back into your prompt as few-shot context. The AI learns your schema patterns, naming conventions, and business logic. Over time it becomes more consistent and more aligned with how your organization actually thinks about data.

Treat rejected queries the same way. A query that failed validation or returned wrong results tells you exactly where your semantic layer needs improvement. Over time the system should shift from pure generation to retrieval-assisted generation — previously approved queries become part of a trusted library.

Design for Multi-Tenancy from the Start

If this system ever serves multiple business units, departments, or external clients, each with different data access rules, you need tenant isolation from the beginning. Retrofitting it later is painful.

Tenant isolation needs to exist at three levels: the prompt level (each tenant’s context should only expose their authorized tables and columns), the validation level (the table whitelist should be scoped per tenant), and the database level (Row-Level Security and schema permissions enforce the boundary at the engine).

EXEC sp_set_session_context N'TenantID', @TenantID;

Implement API Rate Limiting and Cost Control

Every user question is an external API call with a dollar cost. Without controls, a single enthusiastic user can generate thousands of API calls in a day. Implement per-user rate limiting, per-department budgets, hard monthly spend caps, and caching for repeated questions.

Track API costs the same way you track query costs. They are both infrastructure costs for the same system.

Include AI Tables in Your Disaster Recovery Plan

The AI_MetadataCatalog, AI_QueryAudit, AI_QueryExamples, and AI_PromptVersions tables are now critical system components — not throwaway scaffolding. Make sure they are included in your standard backup jobs, part of your DR runbook, restored and validated as part of any DR test, and documented in your data dictionary.