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
      --> Evaluate vector index types and metrics

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

Understanding vector indexes and similarity metrics is essential when building AI-enabled database applications that perform semantic search, retrieval-augmented generation (RAG), recommendation engines, and AI-powered document retrieval. Selecting the correct vector index type and similarity metric has a major impact on search accuracy, scalability, latency, and infrastructure costs.

Traditional database indexes are designed to efficiently locate exact values or values within a range.

Examples include:

• Primary key indexes
• Clustered indexes
• Nonclustered indexes
• Full-text indexes

These indexes perform extremely well for queries such as:

WHERE CustomerID = 123

or

WHERE LastName LIKE 'Smith%'

However, AI applications frequently need to answer questions based on meaning rather than exact text.

For example:

User query:

“Hotels close to the beach with great seafood.”

Documents may contain:

“Oceanfront resort featuring fresh local cuisine.”

There are no matching keywords, yet both sentences describe the same concept.

This is where vector search becomes essential.

What Is a Vector?

A vector is a numerical representation of text, images, audio, or other data generated by an embedding model.

Instead of storing text as characters, AI models convert information into hundreds or thousands of numeric dimensions.

Example:

"The cat sat on the mat."
↓
[0.183,
-0.442,
0.913,
...
1536 dimensions]

Documents discussing similar concepts produce vectors that are mathematically close together.

Why Vector Indexes Are Needed

Suppose a database contains 10 million document embeddings.

Without an index:

• every query compares against every vector
• search complexity becomes enormous
• latency may reach several seconds

Vector indexes organize vectors to reduce the number of comparisons dramatically while preserving high search quality.

Exact vs Approximate Search

Vector search generally falls into two categories.

Exact Search

Also known as:

• Brute-force search
• Exhaustive search

Process:

1. Compare query vector to every stored vector.
2. Calculate similarity score.
3. Sort results.
4. Return best matches.

Advantages:

• 100% accurate
• Always finds nearest neighbor
• Simple implementation

Disadvantages:

• Slow
• Poor scalability
• High CPU usage

Best for:

• Small datasets
• Testing
• Benchmarking

Approximate Nearest Neighbor (ANN)

ANN algorithms search intelligently instead of comparing every vector.

Advantages:

• Extremely fast
• Scales to millions or billions of vectors
• Lower resource consumption

Tradeoff:

• Results are extremely close to optimal but not always mathematically perfect.

Most enterprise AI systems use ANN indexes.

Common Vector Index Types

1. Flat Index (Brute Force)

Every vector is scanned.

Query
↓
Compare with Vector 1
↓
Compare with Vector 2
↓
Compare with Vector 3
↓
...
↓
Best Match

Advantages

• Perfect accuracy
• No preprocessing
• Easy to maintain

Disadvantages

• Slow
• Doesn’t scale well

Best for

• Small datasets
• Testing

2. HNSW (Hierarchical Navigable Small World)

One of the most popular ANN indexes.

Rather than checking every vector, HNSW creates multiple graph layers.

High-level layers:

A
↓
B
↓
C

Lower layers:

A — D — E — F
 \      |
  G — H

The search begins at higher levels and progressively narrows the search.

Advantages

• Extremely high recall
• Very low latency
• Excellent scalability

Disadvantages

• More memory required
• Longer index creation time

Commonly used in:

• Azure SQL vector search
• AI search engines
• Modern vector databases

3. IVF (Inverted File Index)

Vectors are grouped into clusters.

Cluster A
Cluster B
Cluster C
Cluster D

Instead of searching every cluster:

1. Identify closest cluster.
2. Search only that cluster.

Advantages

• Very fast
• Efficient memory usage

Disadvantages

• Search quality depends on clustering accuracy.

4. Product Quantization (PQ)

PQ compresses vectors into compact representations.

Instead of storing:

1536 floating-point numbers

it stores compressed codes.

Advantages

• Huge storage savings
• Faster searches
• Lower memory usage

Disadvantages

• Slight loss of precision

Often combined with IVF.

5. Disk-Based Indexes

Some systems keep indexes primarily on disk instead of RAM.

Advantages

• Supports enormous datasets

Disadvantages

• Higher latency

Useful when memory is limited.

Comparing Index Types

Understanding Similarity Metrics

A vector index determines how vectors are organized.

A similarity metric determines how closeness is measured.

Choosing the wrong metric can significantly reduce search quality.

Cosine Similarity

The most widely used similarity metric.

Measures the angle between vectors.

Formula (conceptually):

Similarity = cos(angle)

Identical direction:

1.0

Perpendicular:

0

Opposite direction:

-1

Advantages

• Ignores vector magnitude
• Excellent for semantic search
• Very common in embedding models

Typical uses

• Document search
• Chatbots
• RAG
• Azure OpenAI embeddings

Euclidean Distance

Measures straight-line distance.

Distance = √((x₂−x₁)²...)

Smaller distance means greater similarity.

Advantages

• Easy to understand
• Works well for spatial data

Disadvantages

• Sensitive to vector magnitude

Dot Product

Calculates the mathematical product of vectors.

Useful when embedding magnitude carries meaning.

Often used by recommendation systems.

Advantages

• Computationally efficient
• Good with normalized embeddings

Manhattan Distance

Also called:

L1 distance

Measures movement along axes.

|x1-x2| + |y1-y2|

Less common in vector databases.

Hamming Distance

Used for binary vectors.

Measures the number of differing bits.

Common in binary embeddings.

Choosing the Right Similarity Metric

Matching Metrics to Embedding Models

Many embedding models are trained assuming a particular similarity metric.

Examples:

• OpenAI embeddings → Cosine similarity
• Azure OpenAI embeddings → Cosine similarity
• Sentence Transformer models → Cosine similarity (commonly)
• Some recommendation models → Dot product

Using the incorrect metric can reduce retrieval quality.

Tradeoffs When Evaluating Vector Indexes

Database developers evaluate multiple characteristics.

Search Accuracy

Higher recall produces better retrieval quality.

Higher accuracy often requires:

• more memory
• more CPU
• larger indexes

Query Latency

AI chat applications typically require responses within milliseconds.

Approximate indexes dramatically reduce latency.

Recall

Recall measures how many true nearest neighbors are returned.

Example:

Actual nearest neighbors:

A
B
C
D
E

Returned:

A
B
C
X
Y

Recall:

3/5 = 60%

Higher recall improves RAG quality.

Memory Usage

HNSW indexes often consume substantial memory.

Compressed indexes require much less.

Build Time

Some indexes build quickly.

Others may require extensive preprocessing.

Large enterprise indexes may take hours to create.

Update Performance

Questions to evaluate:

• How quickly can vectors be inserted?
• Can vectors be deleted efficiently?
• Is index rebuilding required?

Applications with frequent updates may favor indexes that support incremental maintenance.

Vector Index Selection Guidelines

Small Collections (<100K vectors)

Recommended:

• Flat index

Reason:

• Simplicity
• Maximum accuracy

Medium Collections (100K–10M)

Recommended:

• HNSW

Reason:

• Excellent speed
• Excellent recall

Massive Collections (100M+)

Recommended:

• IVF
• IVF + PQ

Reason:

• Reduced storage
• Excellent scalability

Memory-Constrained Systems

Recommended:

• Product Quantization
• Disk-based indexes

Vector Indexes in SQL-Based AI Solutions

Modern SQL platforms increasingly support vector capabilities.

Examples include:

• SQL databases with vector data types
• Vector indexes
• Embedding storage
• Similarity search functions

These capabilities enable developers to combine structured SQL queries with semantic AI search within a single database solution.

Best Practices

• Match the similarity metric to the embedding model.
• Use cosine similarity for most semantic search workloads.
• Prefer ANN indexes for production systems.
• Benchmark recall, latency, and throughput before deployment.
• Monitor index performance as datasets grow.
• Rebuild or optimize indexes when fragmentation or large-scale updates reduce efficiency.
• Evaluate memory consumption alongside query performance.
• Test retrieval quality using realistic user queries.

DP-800 Exam Tips

Remember these key points for the exam:

• Vector indexes optimize similarity search rather than exact matching.
• ANN indexes trade a small amount of accuracy for significant performance gains.
• HNSW is a leading ANN algorithm due to its high recall and low latency.
• IVF clusters vectors before searching.
• Product Quantization reduces storage requirements.
• Cosine similarity is the preferred metric for most semantic search scenarios.
• Choosing the appropriate similarity metric is just as important as choosing the index type.
• Retrieval quality depends on embeddings, similarity metrics, and index configuration working together.

Practice Exam Questions

Question 1

A development team is building a Retrieval-Augmented Generation (RAG) solution containing over 15 million document embeddings. The application requires low query latency while maintaining high retrieval accuracy.

Which vector index type is the most appropriate?

A. Flat index

B. HNSW

C. Clustered index

D. Full-text index

Answer: B

Explanation:
HNSW is designed for Approximate Nearest Neighbor (ANN) search and offers excellent recall with very low latency, making it a common choice for large-scale RAG implementations. Flat indexes become too slow at this scale, while clustered and full-text indexes are not vector indexes.

Question 2

Which similarity metric is most commonly used with modern text embedding models for semantic search?

A. Manhattan Distance

B. Euclidean Distance

C. Cosine Similarity

D. Hamming Distance

Answer: C

Explanation:
Cosine similarity compares the angle between vectors rather than their magnitude, making it ideal for semantic search. Many embedding models, including Azure OpenAI embeddings, are designed to work effectively with cosine similarity.

Question 3

A database developer wants mathematically perfect nearest-neighbor results regardless of execution time.

Which search method should be selected?

A. Approximate Nearest Neighbor

B. Product Quantization

C. Exhaustive (Flat) Search

D. IVF

Answer: C

Explanation:
Exhaustive or flat search compares the query against every stored vector, guaranteeing the exact nearest neighbors. This approach is computationally expensive but provides maximum accuracy.

Question 4

What is the primary purpose of Product Quantization (PQ)?

A. Improve SQL joins

B. Increase transaction throughput

C. Normalize embeddings

D. Reduce storage and memory requirements

Answer: D

Explanation:
Product Quantization compresses vectors into compact representations, reducing storage and memory usage while enabling efficient searches. The tradeoff is a small reduction in precision.

Question 5

Which statement best describes Approximate Nearest Neighbor (ANN) indexing?

A. It guarantees perfect search accuracy.

B. It searches every vector sequentially.

C. It balances retrieval accuracy with search performance.

D. It only supports binary vectors.

Answer: C

Explanation:
ANN algorithms reduce search time by avoiding exhaustive comparisons. They provide high-quality results with much better performance than exact search, making them suitable for production AI systems.

Question 6

A team notices that their semantic search results have degraded after switching from cosine similarity to Euclidean distance while using the same embedding model.

What is the most likely cause?

A. The embedding model was trained assuming cosine similarity.

B. Euclidean distance always produces identical results.

C. Vector indexes require clustered tables.

D. SQL Server does not support vectors.

Answer: A

Explanation:
Embedding models are often optimized for specific similarity metrics. Using a different metric than the one assumed during training can reduce retrieval quality even if the vectors themselves remain unchanged.

Question 7

Why do vector indexes improve search performance?

A. They reduce the dimensionality of every embedding.

B. They organize vectors so fewer comparisons are needed.

C. They convert vectors into relational tables.

D. They eliminate the need for embeddings.

Answer: B

Explanation:
Vector indexes structure embeddings so that searches examine only promising candidates instead of every stored vector, significantly reducing query latency.

Question 8

A company has a small proof-of-concept application containing 25,000 document embeddings. Search accuracy is more important than performance.

Which index is the best choice?

A. IVF + PQ

B. HNSW

C. Flat index

D. Disk-based ANN index

Answer: C

Explanation:
For relatively small datasets where absolute accuracy is the priority, a flat index is often the simplest and most accurate solution. Performance remains acceptable because the collection size is limited.

Question 9

Which evaluation metric indicates how many true nearest neighbors are successfully returned during a vector search?

A. Latency

B. Precision

C. Throughput

D. Recall

Answer: D

Explanation:
Recall measures the proportion of actual nearest neighbors that are retrieved by the search algorithm. Higher recall generally leads to better retrieval quality in semantic search and RAG systems.

Question 10

When evaluating different vector index types for a production AI solution, which combination of factors is most important?

A. File size and backup frequency

B. Number of SQL tables and views

C. Search latency, recall, memory usage, and index maintenance

D. Number of stored procedures and triggers

Answer: C

Explanation:
Production vector indexes should be evaluated based on their ability to deliver fast queries, high recall, efficient memory utilization, and manageable maintenance as data volumes grow. These characteristics directly affect the performance and scalability of AI-enabled database solutions.

Go to the DP-800 Exam Prep Hub main page