Launch Deal · 50% off 1st month ·

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 MODEL or ALTER ANY EXTERNAL MODEL. Use AUTHORIZATION to set the owner, and grant use of the model only to the roles that need it.
  • Change the endpoint centrally: ALTER EXTERNAL MODEL updates 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 vector column (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.

See plans

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