3.1 — Evaluate and Manage External Models
DP-800 Domain 3 · Skill 3.1
What You Need to Know
The database calls AI models through external model objects: a named, secured definition of where a model endpoint lives, how to authenticate, and what it's for. First you choose a suitable model, then you register it.
Evaluating models
| Dimension | Questions to ask |
|---|---|
| Task fit | Embeddings for search? Chat or completion for RAG answers? Structured output (JSON schemas) for extraction? |
| Multimodal | Does the input include images or documents as well as text? |
| Multilanguage | Are the content and queries in several languages? Use a multilingual embedding model, otherwise similarity across languages fails |
| Size and dimensions | Larger embedding models (for example 3,072 dimensions) are more accurate but cost more storage, memory and latency. Some models can reduce dimensions (such as {"dimensions": 1536}). A vector column allows up to 1,998 dimensions as float32, and 3,996 as float16 |
| Cost, latency and throughput | Price per token, rate limits, region, and the volume of rows to embed |
| Deployment | A cloud endpoint (Azure OpenAI in Microsoft Foundry, OpenAI), a self-hosted one (Ollama), or local ONNX Runtime on SQL Server for data that must stay on-premises |
One rule always applies: the same embedding model must be used for stored vectors and for query vectors. Changing the model means re-embedding everything.
Creating an external model
CREATE DATABASE SCOPED CREDENTIAL [https://contoso-ai.openai.azure.com/]
WITH IDENTITY = 'Managed Identity', SECRET = '{"resourceid":"https://cognitiveservices.azure.com"}';
CREATE EXTERNAL MODEL ProductEmbeddings
WITH (
LOCATION = 'https://contoso-ai.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-02-01',
API_FORMAT = 'Azure OpenAI', -- or 'OpenAI', 'Ollama', 'ONNX Runtime'
MODEL_TYPE = EMBEDDINGS,
MODEL = 'text-embedding-3-small',
CREDENTIAL = [https://contoso-ai.openai.azure.com/],
PARAMETERS = '{"dimensions":1536}'
);
| Element | Purpose |
|---|---|
LOCATION |
The full endpoint URL (deployment and API version included) |
API_FORMAT |
The provider's message format: Azure OpenAI, OpenAI, Ollama, ONNX Runtime |
MODEL_TYPE |
EMBEDDINGS |
CREDENTIAL |
A database scoped credential, preferably managed identity or else an API key header |
PARAMETERS |
JSON appended to each request (dimensions, retry options) |
LOCAL_RUNTIME_PATH |
For ONNX Runtime running locally on SQL Server |
Managing models
- Permissions: creating or altering models needs
CREATE EXTERNAL MODELorALTER ANY EXTERNAL MODEL. UseAUTHORIZATIONto set the owner, and grant use of the model only to the roles that need it. - Change the endpoint centrally:
ALTER EXTERNAL MODELupdates the location or credential for every query that uses the model name. Queries only reference the name. - Retries: add
{"sql_rest_options":{"retry_count":3}}in the model's or the call's parameters for transient HTTP errors (0–10 retries). - Remember which model version produced each stored vector (for example a column or table metadata), so you know when to re-embed.
Key Concept: Evaluate models for task, languages, modality, dimensions and cost. Register them once as an EXTERNAL MODEL with a managed-identity credential, and call them by name, which makes endpoints swappable and access controlled.
Exam Traps
- Embedding stored rows with one model and queries with another
- Choosing a 3,072-dimension float32 embedding for a
vectorcolumn (the float32 limit is 1,998, so reduce the dimensions or use float16) - Putting the API key in application code instead of a database scoped credential or managed identity
- Choosing an English-only embedding model for multilingual search
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- CREATE EXTERNAL MODEL, Microsoft Learn
- ALTER EXTERNAL MODEL, Microsoft Learn
- Intelligent applications and AI in SQL, Microsoft Learn
- Azure OpenAI embedding models, Microsoft Learn