This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement intelligent search
      --> Design for vector data, including vector data type, vector indexes, and size

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Modern AI-enabled applications increasingly rely on vector data to represent the meaning of text, images, audio, and other unstructured information. Instead of matching exact words, vector-based search enables applications to find content based on semantic similarity.

Microsoft SQL Server 2025, Azure SQL Database, and Azure SQL Managed Instance introduce native support for vector data, allowing databases to store embeddings directly alongside relational data. Combined with AI models and vector indexes, SQL databases become powerful platforms for semantic search, Retrieval-Augmented Generation (RAG), recommendation engines, document similarity, and AI assistants.

For the DP-800 exam, candidates should understand how to:

• Design schemas that store vector embeddings
• Choose appropriate vector dimensions
• Understand vector data types
• Create and maintain vector indexes
• Balance storage, performance, and accuracy
• Select index types appropriate for AI workloads
• Understand how vector size affects database performance

What Is Vector Data?

A vector is a numerical representation of data generated by an embedding model.

Instead of storing text directly, the model converts text into hundreds or thousands of floating-point numbers.

Example:

Original text:

“Azure SQL supports AI-powered search.”

Embedding:

[0.012,
-0.553,
0.441,
...
0.318]

This numerical representation captures semantic meaning.

Documents discussing:

• AI databases
• Azure SQL
• semantic search

will produce vectors located close together within vector space.

Why Store Vectors in SQL?

Traditionally, embeddings were stored in external vector databases.

Modern SQL databases now support vectors directly, allowing organizations to:

• Keep structured and unstructured data together
• Simplify architecture
• Reduce synchronization complexity
• Improve transactional consistency
• Query relational and vector data simultaneously

Example table:

This allows applications to perform:

• SQL filtering
• joins
• semantic search

within one query.

Understanding the Vector Data Type

The new VECTOR data type stores embeddings efficiently inside SQL tables.

Example:

VECTOR(1536)

The number specifies the vector dimensions.

Examples:

VECTOR(768)
VECTOR(1024)
VECTOR(1536)
VECTOR(3072)

The dimension must exactly match the embedding model.

What Are Vector Dimensions?

Each embedding model outputs a fixed number of values.

Examples:

If an embedding model generates 1536 values:

VECTOR(1536)

must be used.

Using the wrong size causes insert failures.

Choosing the Correct Vector Size

Higher dimensions provide richer semantic meaning.

However they also require:

• more storage
• larger indexes
• slower searches
• additional memory

Example comparison:

Choosing unnecessarily large vectors wastes storage.

How Embedding Size Affects Storage

Each dimension stores a floating-point number.

Example:

1536 dimensions

≈1536 floating point values

Across one million rows:

1,000,000 vectors
×
1536 dimensions

This becomes a significant storage requirement.

Large AI applications should estimate storage before deployment.

Designing Tables for Vector Data

Common design:

Documents
------------
DocumentID
Title
Category
Content
Embedding

The embedding column stores semantic meaning.

Other columns remain relational.

This design enables hybrid queries.

Separating Embeddings from Business Data

Many organizations separate embeddings into another table.

Example:

Documents
DocumentID
Title
Content
DocumentEmbeddings
DocumentID
Embedding
ModelVersion
CreatedDate

Benefits:

• easier regeneration
• reduced locking
• independent maintenance
• multiple embedding versions

Versioning Embeddings

Embedding models evolve.

Example:

Version 1:

text-embedding-3-small

Later:

text-embedding-3-large

A model change usually requires regenerating all vectors.

Many databases store:

• Model Name
• Version
• Generation Date

This allows safe migrations.

One Embedding or Multiple?

Some applications store several embeddings.

Example:

Products

• Title embedding
• Description embedding
• Review embedding

Different searches can target different meanings.

Designing for Chunk-Level Embeddings

Large documents are usually divided into chunks.

Instead of:

Entire PDF
↓
One vector

Applications store:

Document
↓
Paragraphs
↓
One vector per paragraph

Benefits include:

• higher search precision
• better RAG responses
• smaller embeddings
• improved relevance

Vector Search vs Traditional Search

Traditional search matches keywords.

Example:

Search:

vehicle

Document:

car

Keyword search may miss it.

Vector search recognizes semantic similarity.

It understands:

• automobile
• vehicle
• car
• SUV

are closely related.

Combining SQL Filters with Vector Search

One major benefit of SQL databases is combining structured filters with AI search.

Example:

Category = Electronics
AND
Vector similarity

Only electronics are searched semantically.

This improves both performance and relevance.

Exact Search vs Approximate Search

Vector searches generally use two approaches.

Exact Search

Compares every vector.

Advantages:

• highest accuracy

Disadvantages:

• slower
• expensive for large datasets

Approximate Search

Uses specialized indexes.

Advantages:

• much faster
• scalable

Tradeoff:

• slight reduction in accuracy

Most production AI systems use approximate search.

Understanding Vector Indexes

Without indexes:

Every vector must be compared.

1 million vectors
↓
1 million comparisons

Vector indexes dramatically reduce work.

They organize vectors based on similarity.

This enables very fast nearest-neighbor searches.

Approximate Nearest Neighbor (ANN)

Modern vector databases commonly use ANN indexing.

Instead of checking every vector:

Search
↓
Relevant region
↓
Nearby vectors
↓
Best matches

Response times become milliseconds instead of seconds.

Why Vector Indexes Matter

Benefits include:

• faster semantic search
• reduced CPU usage
• scalable AI applications
• improved RAG performance
• lower query latency

Large AI systems depend heavily on vector indexing.

Choosing Whether to Create a Vector Index

Small datasets:

A vector index may not provide significant benefit.

Large datasets:

Vector indexes become essential.

Typical guidance:

Best Practices

• Use the embedding dimensions required by the selected model.
• Store vectors in dedicated VECTOR columns.
• Keep relational data alongside embeddings whenever practical.
• Separate embeddings into dedicated tables when frequent regeneration is expected.
• Track embedding model versions.
• Chunk large documents before generating embeddings.
• Choose the smallest embedding model that delivers acceptable quality.
• Create vector indexes for large datasets.
• Combine relational filtering with semantic search.
• Monitor storage growth as embeddings increase.

Common Exam Tips

• Know that VECTOR stores embedding data.
• Understand that vector dimensions must match the embedding model.
• Remember that larger vectors increase storage and memory requirements.
• Recognize that vector indexes accelerate semantic similarity searches.
• Understand the difference between exact and approximate nearest-neighbor searches.
• Know that chunking improves retrieval quality for large documents.
• Understand that multiple embeddings may exist for a single record.
• Remember that embedding model upgrades usually require regenerating vectors.
• Understand that relational filtering and vector search can be combined.
• Expect scenario-based questions involving storage, indexing, scalability, and AI search architecture.

Practice Exam Questions

Question 1

A company is building a Retrieval-Augmented Generation (RAG) application using Azure SQL Database. They plan to store embeddings generated by the text-embedding-3-small model.

Which VECTOR data type should be used for the embedding column?

A. VECTOR(768)
B. VECTOR(1024)
C. VECTOR(1536)
D. VECTOR(3072)

Correct Answer: C

Explanation:
The text-embedding-3-small model generates 1,536-dimensional embeddings. The VECTOR column must match the number of dimensions produced by the embedding model. Using any other dimension would prevent embeddings from being stored correctly.

Question 2

A database contains 12 million product embeddings. Semantic searches are becoming increasingly slow because every query compares all vectors.

What should the database developer implement?

A. A clustered index on the VECTOR column
B. A vector index that supports Approximate Nearest Neighbor (ANN) searches
C. A nonclustered index on the product name
D. A filtered index on the category column

Correct Answer: B

Explanation:
Vector indexes using Approximate Nearest Neighbor algorithms dramatically reduce the number of comparisons required during similarity searches. Traditional SQL indexes cannot optimize vector similarity calculations.

Question 3

A developer must choose between a 768-dimensional embedding model and a 3,072-dimensional embedding model.

What is generally true about the larger embedding model?

A. It always performs searches faster.
B. It requires fewer storage resources.
C. It typically captures more semantic detail but requires additional storage and memory.
D. It cannot be indexed.

Correct Answer: C

Explanation:
Higher-dimensional embeddings generally preserve more semantic information, improving search quality. However, they increase storage requirements, memory consumption, and indexing costs.

Question 4

A database stores customer information together with vector embeddings representing customer support conversations.

Which design provides the greatest flexibility for regenerating embeddings after switching to a new embedding model?

A. Store embeddings in a separate table linked by the primary key.
B. Store embeddings inside a JSON document.
C. Store embeddings inside XML columns.
D. Store embeddings inside temporary tables.

Correct Answer: A

Explanation:
Separating embeddings into their own table simplifies regeneration, maintenance, versioning, and model migration while keeping business data unchanged.

Question 5

A development team wants to search only engineering documents while using semantic similarity.

Which approach best meets this requirement?

A. Perform only vector similarity searches across every document.
B. Filter documents by department using SQL, then perform vector similarity searches.
C. Disable relational filtering.
D. Store engineering documents in a separate SQL Server instance.

Correct Answer: B

Explanation:
One advantage of SQL databases is combining structured filtering with vector similarity search. Restricting the dataset before similarity comparisons improves both performance and relevance.

Question 6

A company stores embeddings for technical manuals that average 400 pages each.

What is the recommended design approach?

A. Generate one embedding for the entire manual.
B. Store only the title as an embedding.
C. Divide manuals into logical chunks and generate embeddings for each chunk.
D. Generate embeddings only for images.

Correct Answer: C

Explanation:
Chunking improves semantic retrieval accuracy by allowing searches to return only the most relevant portions of large documents rather than entire documents.

Question 7

A developer upgrades from one embedding model to another that produces vectors with a different number of dimensions.

What should the developer expect?

A. Existing vectors automatically resize.
B. Existing vectors remain compatible without changes.
C. SQL Server automatically converts vector dimensions.
D. Existing embeddings must be regenerated to match the new model dimensions.

Correct Answer: D

Explanation:
Embedding dimensions are fixed for each model. Changing models often changes vector size, requiring regeneration of all stored embeddings.

Question 8

An application contains approximately 3,000 embedded documents.

Which statement is most accurate regarding vector indexes?

A. Vector indexes are mandatory regardless of database size.
B. Vector indexes cannot be created until at least one million vectors exist.
C. A vector index may provide limited benefit for a very small dataset.
D. Vector indexes only work with GraphQL.

Correct Answer: C

Explanation:
Small datasets often perform adequately without vector indexes. The performance gains become much more significant as the number of vectors increases.

Question 9

A developer wants to support semantic search over product descriptions while maintaining product categories, prices, and inventory information in the same database.

Which database design best supports this objective?

A. Store embeddings in a VECTOR column while keeping relational attributes in standard SQL columns.
B. Store all relational data inside embedding vectors.
C. Replace relational tables with JSON files.
D. Store embeddings only in application memory.

Correct Answer: A

Explanation:
Keeping embeddings alongside relational data enables hybrid queries that combine SQL filtering with semantic similarity search, one of the major strengths of AI-enabled SQL databases.

Question 10

Which factor has the greatest impact on the storage requirements of vector data?

A. Database collation
B. Number of database users
C. Recovery model
D. Number of dimensions in each embedding

Correct Answer: D

Explanation:
Each embedding stores one numeric value per dimension. As the number of dimensions increases, the storage required for each vector grows proportionally, affecting table size, indexes, backups, and memory usage.

Final Exam Tips

• Ensure the VECTOR column dimension exactly matches the embedding model.
• Larger embeddings generally improve semantic quality but increase storage and computational costs.
• Use vector indexes (ANN) for large datasets to improve search performance.
• Combine relational SQL filtering with vector similarity searches for efficient hybrid queries.
• Chunk large documents before generating embeddings to improve retrieval quality.
• Store embedding model metadata and versions to simplify future migrations.
• Separate embeddings from business data when frequent regeneration is expected.
• Expect scenario-based questions comparing performance, storage, indexing strategies, and search architectures.

Go to the DP-800 Exam Prep Hub main page