External AI Models: Choose, Create, Manage

Bringing AI into your database

Evaluating external models

When choosing a model for your SQL-based AI solution, evaluate across four dimensions:

Four evaluation dimensions for external models

Dimension	What to Evaluate	Trade-off
Modality	Text-only, multimodal (text+image+audio), or embedding models	Multimodal models are more versatile but larger and more expensive
Language	Monolingual (English) or multilingual (100+ languages)	Multilingual models handle global data but may be less precise in each language
Size	Small (fast, cheap), medium (balanced), large (most capable)	Larger models produce better results but cost more per token and have higher latency
Structured output	Can the model return JSON, XML, or structured formats reliably?	Essential for database integration — unstructured text is hard to INSERT into tables

Model size guide

Size Category	Examples	Tokens/sec	Best For
Small	GPT-4o-mini, Phi-3	Very fast	Classification, simple extraction, embeddings
Medium	GPT-4o	Fast	General Q&A, summarisation, code generation
Large	GPT-4, o1-series	Slower	Complex reasoning, multi-step analysis
Embedding	text-embedding-3-small/large	Very fast	Vector search, semantic similarity

💡 Exam tip: Structured output is critical for SQL integration

When calling an AI model from T-SQL (via sp_invoke_external_rest_endpoint), you need the response in a parseable format — typically JSON. Models that reliably produce structured JSON output are essential. If the model returns free-form text, you cannot reliably extract values into table columns.

Look for models that support JSON mode or structured output schemas in their API. The exam may present a scenario where the model returns inconsistent formats — the fix is configuring structured output in the API call.

Creating external models in SQL

External models are database objects that register an AI endpoint:

-- Create an external model definition
CREATE EXTERNAL MODEL EmbeddingModel
WITH (
    MODEL_TYPE = 'EMBEDDINGS',
    ENDPOINT = 'https://vaultbank-ai.openai.azure.com/',
    DEPLOYMENT_NAME = 'text-embedding-3-small',
    API_KEY_SECRET_NAME = 'AzureOpenAI_Key',
    API_FORMAT = 'OPENAI'
);

-- Create a model for text generation
CREATE EXTERNAL MODEL ChatModel
WITH (
    MODEL_TYPE = 'COMPLETIONS',
    ENDPOINT = 'https://vaultbank-ai.openai.azure.com/',
    DEPLOYMENT_NAME = 'gpt-4o',
    API_KEY_SECRET_NAME = 'AzureOpenAI_Key',
    API_FORMAT = 'OPENAI'
);

Managing models

-- View all registered models
SELECT * FROM sys.external_models;

-- Alter model (e.g., change deployment)
ALTER EXTERNAL MODEL ChatModel
SET DEPLOYMENT_NAME = 'gpt-4o-2024-11-20';

-- Drop model
DROP EXTERNAL MODEL EmbeddingModel;

Security for model endpoints

Model endpoints require authentication. Options:

• API keys stored as database-scoped credentials
• Managed Identity (preferred for Azure-hosted databases)
• Key Vault references for centralised secret management

-- Using managed identity (no secrets needed)
CREATE EXTERNAL MODEL SecureModel
WITH (
    MODEL_TYPE = 'COMPLETIONS',
    ENDPOINT = 'https://vaultbank-ai.openai.azure.com/',
    DEPLOYMENT_NAME = 'gpt-4o',
    CREDENTIAL = [ManagedIdentityCredential],
    API_FORMAT = 'OPENAI'
);

💡 Scenario: Priya's model selection at Vault Bank

Priya needs three AI capabilities for the banking platform:

1. Fraud detection — classify transactions as suspicious or legitimate. She chooses a small model (GPT-4o-mini) because it is fast and cheap for binary classification at high volume.
2. Customer support summarisation — summarise complaint emails in English and Japanese. She chooses a multilingual medium model (GPT-4o) for balanced quality and language support.
3. Semantic search — find similar customer queries. She chooses an embedding model (text-embedding-3-small) for generating vector representations.

Three different models, three different use cases — each optimised for its task.

Question

What is an external model in SQL Server?

Click or press Enter to reveal answer

Answer

A database object (CREATE EXTERNAL MODEL) that registers an AI model endpoint — like Azure OpenAI or Microsoft Foundry. It stores the endpoint URL, deployment name, authentication, and model type so T-SQL code can invoke the model directly.

Click to flip back

Question

Why is structured output important for SQL + AI integration?

Click or press Enter to reveal answer

Answer

When calling AI models from T-SQL, responses must be parseable to extract values into table columns. Structured output (JSON mode) ensures the model returns consistent, machine-readable formats instead of free-form text that is difficult to parse reliably.

Click to flip back

Question

What are the main model types used in SQL AI solutions?

Click or press Enter to reveal answer

Answer

Three types: COMPLETIONS (text generation, Q&A, summarisation), EMBEDDINGS (generate vector representations for semantic search), and MULTIMODAL (process text + images together). Choose based on your use case: search uses embeddings, RAG uses completions.

Click to flip back

Knowledge Check

Leo at SearchWave needs to add semantic product search. The system must generate vector embeddings for 2 million product descriptions nightly, and latency per embedding must be under 50ms. Which model choice is best?

AGPT-4o (large, high-quality completions model)Btext-embedding-3-small (small, fast embedding model)Ctext-embedding-3-large (large, high-dimensional embedding model)DGPT-4o-mini (small completions model with JSON output)

Check Answer

🎬 Video coming soon

Next up: Embeddings: Design, Chunk, and Generate — turn your data into vectors that AI can search and compare.