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
      --> Implement vector search

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

Implementing vector search is one of the foundational skills for building modern AI-enabled database applications. Vector search enables databases to retrieve information based on semantic meaning rather than exact keyword matches, making it essential for Retrieval-Augmented Generation (RAG), AI assistants, recommendation engines, semantic document search, knowledge management systems, and intelligent enterprise applications.

What Is Vector Search?

Traditional SQL queries search for exact values.

For example:

SELECT *
FROM Products
WHERE ProductName = 'Laptop';

or

WHERE Description LIKE '%wireless%'

These approaches rely on exact text matching.

However, AI applications often need to answer questions like:

“Find documents about reducing cloud costs.”

Relevant documents might contain:

• Lower Azure spending
• Optimize infrastructure expenses
• Cloud cost optimization
• Reduce operational costs

Although these documents contain different words, they share the same meaning.

Vector search enables databases to find these semantically related documents.

How Vector Search Works

Vector search consists of several stages.

User Query
↓
Embedding Model
↓
Query Vector
↓
Vector Similarity Search
↓
Nearest Neighbor Documents
↓
(Optional)
Large Language Model (LLM)

Instead of comparing text directly, the database compares numeric vector representations generated by an embedding model.

What Is a Vector?

A vector is a high-dimensional numerical representation of data.

Example:

"Azure SQL Database"
↓
[-0.134,
0.281,
0.998,
...
1536 dimensions]

Every document stored in the database has its own embedding vector.

When a user submits a query, the query is also converted into a vector.

The database then compares vectors mathematically to identify the most similar results.

Components of a Vector Search Solution

A complete vector search implementation includes several components.

1. Source Data

Examples include:

• PDF files
• Product catalogs
• Emails
• Knowledge articles
• Web pages
• Support tickets
• SQL records

2. Embedding Model

The embedding model converts text into vectors.

Popular examples include:

• Azure OpenAI Embeddings
• OpenAI text embedding models
• Sentence Transformers
• Other compatible embedding models

The embedding model should remain consistent for both indexing and querying.

3. Vector Storage

Embeddings are stored inside the database.

Example table:

Modern SQL databases increasingly support dedicated vector data types.

4. Vector Index

Searching millions of vectors without an index would require comparing every vector.

Vector indexes organize embeddings for efficient similarity searches.

Common vector indexes include:

• Flat (Exact Search)
• HNSW
• IVF
• IVF + Product Quantization (PQ)

Approximate Nearest Neighbor (ANN) indexes are commonly used in production systems because they significantly reduce search latency while maintaining high recall.

5. Similarity Function

The database determines which vectors are closest.

Common similarity metrics include:

• Cosine similarity
• Euclidean distance
• Dot product

Cosine similarity is the most common metric for semantic search.

Exact Search vs Approximate Search

Exact (Brute Force) Search

The database compares the query vector against every stored vector.

Advantages:

• Perfect accuracy
• Guaranteed nearest neighbors

Disadvantages:

• Slow
• Poor scalability

Best suited for:

• Small datasets
• Testing
• Validation

Approximate Nearest Neighbor (ANN)

ANN indexes intelligently reduce the search space.

Advantages:

• Extremely fast
• Scales to millions or billions of vectors
• Lower CPU utilization

Tradeoff:

Results are highly accurate but not mathematically perfect.

Most enterprise AI applications use ANN search.

Implementing Vector Search

A typical implementation follows these steps.

Step 1. Prepare Data

Collect the documents.

Examples:

• Product manuals
• Policies
• Emails
• Support articles

Clean the text by removing unnecessary formatting and duplicate content.

Step 2. Generate Embeddings

Use an embedding model to create vectors.

Example workflow:

Document
↓
Embedding Model
↓
1536-Dimensional Vector

Each document receives one or more embeddings.

Step 3. Store Embeddings

Store:

• Original text
• Metadata
• Embedding vector

Example:

Metadata enables additional filtering during searches.

Step 4. Create a Vector Index

The vector index accelerates similarity searches.

Without an index:

Query
↓
Compare to every vector

With an ANN index:

Query
↓
Index
↓
Small candidate set
↓
Best matches

Step 5. Convert User Query

The user’s search query is embedded using the same embedding model.

Example:

"How do I connect remotely?"
↓
Embedding Model
↓
Query Vector

Consistency is critical. Using a different embedding model for queries than for indexed documents can significantly reduce search quality.

Step 6. Perform Similarity Search

The database compares the query vector with stored vectors.

Example SQL pseudocode:

SELECT TOP 5
    DocumentID,
    SimilarityScore
FROM Documents
ORDER BY VECTOR_DISTANCE(Embedding, @QueryVector);

The exact syntax varies depending on the database platform and vector search implementation.

Step 7. Return Results

The application retrieves the closest documents.

Example:

Vector Search Workflow

Documents
↓
Generate Embeddings
↓
Store Vectors
↓
Create Vector Index
↓
User Query
↓
Generate Query Embedding
↓
Similarity Search
↓
Top Matching Documents

Filtering Vector Search Results

Many applications combine vector search with traditional SQL filtering.

Example:

Semantic Search
+
WHERE Department = 'Finance'
+
ORDER BY Similarity

This approach is often called hybrid filtering, allowing organizations to limit searches by structured metadata while still leveraging semantic similarity.

Examples of filters include:

• Department
• Date
• Customer
• Region
• Security classification
• Language

Hybrid Search

Hybrid search combines:

• Keyword search
• Full-text search
• Vector search

Example:

Keyword Search
+
Vector Search
↓
Combined Ranking
↓
Final Results

Benefits include:

• Higher relevance
• Better handling of synonyms
• Stronger ranking
• Improved user satisfaction

Many enterprise AI search systems use hybrid search instead of vector search alone.

Using Vector Search in RAG

Retrieval-Augmented Generation relies heavily on vector search.

Workflow:

User Question
↓
Embedding
↓
Vector Search
↓
Relevant Documents
↓
LLM
↓
Grounded Response

Instead of relying solely on the LLM’s training data, the model uses retrieved documents as grounding data.

Benefits:

• More accurate responses
• Reduced hallucinations
• Access to current organizational knowledge

Common Vector Search Scenarios

Enterprise Knowledge Search

Users ask natural language questions.

Example:

“How do I reset my VPN password?”

The database retrieves the most semantically relevant documentation.

Customer Support

Support engineers search:

“Printer won’t connect.”

Relevant troubleshooting documents are retrieved even if they use different wording.

Product Recommendation

Customers searching for:

“Comfortable running shoes”

may receive products described as:

• Lightweight trainers
• Cushioned athletic footwear
• Marathon shoes

Legal Document Search

Law firms search by legal concepts rather than exact wording.

Healthcare Knowledge Bases

Clinicians retrieve similar cases based on symptoms rather than identical terminology.

Performance Considerations

Database developers should evaluate:

Search Latency

Users expect responses within milliseconds.

ANN indexes dramatically reduce latency.

Recall

Recall measures how many of the true nearest neighbors are returned.

Higher recall generally improves RAG quality.

Index Size

Larger indexes often improve retrieval quality but require more memory.

Memory Consumption

HNSW indexes typically consume more RAM than compressed indexes.

Index Build Time

Large vector indexes may require significant time to build.

Plan for maintenance windows when rebuilding indexes.

Update Frequency

Applications with frequent inserts and deletes should use index types that efficiently support incremental updates.

Common Implementation Mistakes

Using Different Embedding Models

Documents embedded with one model should not be searched using vectors generated by a different model.

Using the Wrong Similarity Metric

Many embedding models assume cosine similarity.

Using Euclidean distance or dot product incorrectly may reduce search accuracy.

Not Creating a Vector Index

Searching without an index performs poorly on large datasets.

Ignoring Metadata

Metadata filtering significantly improves result quality.

Returning Too Many Documents

Retrieving excessive documents increases latency and may overwhelm downstream LLMs in RAG systems.

Best Practices

• Use the same embedding model for indexing and querying.
• Choose a similarity metric recommended for the embedding model.
• Use ANN indexes for production environments.
• Combine vector search with metadata filters when appropriate.
• Consider hybrid search for the highest-quality results.
• Benchmark recall, latency, and throughput using realistic workloads.
• Monitor index growth and rebuild or optimize indexes when necessary.
• Store both embeddings and the original source content.

DP-800 Exam Tips

Remember these key points for the exam:

• Vector search retrieves data based on semantic similarity rather than exact text.
• Embeddings are numerical representations generated by AI models.
• The same embedding model should be used for both indexing and querying.
• Vector indexes improve search performance by reducing the number of vector comparisons.
• Approximate Nearest Neighbor (ANN) indexes provide fast searches with high recall.
• Cosine similarity is the most commonly used metric for semantic search.
• Hybrid search combines keyword search with vector search to improve relevance.
• Vector search is a core component of Retrieval-Augmented Generation (RAG).

Practice Exam Questions

Question 1

A company is building a chatbot that answers employee questions using internal policy documents. The solution converts both documents and user queries into embeddings before searching for relevant information.

What is the primary purpose of generating embeddings?

A. To compress documents for storage

B. To represent text numerically so semantic similarity can be measured

C. To encrypt sensitive information

D. To improve SQL transaction performance

Answer: B

Explanation:
Embeddings convert text into high-dimensional numerical vectors that capture semantic meaning. These vectors enable similarity comparisons that go beyond exact keyword matching.

Question 2

A developer plans to implement vector search against a database containing 30 million document embeddings.

Which approach provides the best balance between scalability and query performance?

A. Sequentially compare every vector

B. Use a clustered index

C. Use an Approximate Nearest Neighbor (ANN) vector index

D. Create additional foreign keys

Answer: C

Explanation:
ANN indexes are specifically designed to support efficient vector similarity searches across very large datasets while maintaining high recall and low latency.

Question 3

A user searches for:

“Affordable cloud storage”

The returned documents discuss:

• Cost-effective cloud backup
• Low-cost online storage
• Budget-friendly data storage

Why were these documents returned?

A. SQL wildcard matching

B. Lexical keyword matching

C. Primary key lookup

D. Semantic similarity using vector search

Answer: D

Explanation:
Vector search retrieves content based on semantic meaning rather than identical words, enabling related concepts and synonyms to be found.

Question 4

Which statement best describes hybrid search?

A. It combines vector search with keyword or full-text search.

B. It stores vectors in multiple databases.

C. It replaces embeddings with SQL indexes.

D. It searches only relational columns.

Answer: A

Explanation:
Hybrid search combines traditional lexical search with semantic vector search, often producing more relevant and comprehensive search results.

Question 5

Why should the same embedding model be used for both document indexing and query generation?

A. It reduces storage costs.

B. It eliminates the need for vector indexes.

C. It ensures vectors exist in the same semantic space for meaningful comparisons.

D. It automatically creates SQL indexes.

Answer: C

Explanation:
Embeddings generated by different models may occupy different vector spaces, making similarity calculations unreliable and reducing retrieval quality.

Question 6

What is the primary function of a vector index?

A. Encrypt embedding vectors

B. Reduce the number of vector comparisons during searches

C. Compress relational tables

D. Replace SQL indexes

Answer: B

Explanation:
Vector indexes organize embeddings so the search engine evaluates only the most promising candidates instead of comparing every stored vector.

Question 7

A Retrieval-Augmented Generation (RAG) application performs vector search before sending retrieved documents to a large language model.

Why is this retrieval step important?

A. It reduces SQL storage requirements.

B. It converts SQL tables into vectors.

C. It grounds the model with relevant information, improving response accuracy.

D. It eliminates the need for embeddings.

Answer: C

Explanation:
RAG retrieves relevant documents that provide context to the LLM, helping produce accurate, current, and evidence-based responses while reducing hallucinations.

Question 8

Which SQL capability is most commonly combined with vector search to narrow search results to specific business data?

A. Metadata filtering using WHERE clauses

B. ALTER TABLE statements

C. Transaction logging

D. Foreign key constraints

Answer: A

Explanation:
Combining vector search with structured SQL filters allows applications to restrict results by attributes such as department, region, or document type while maintaining semantic relevance.

Question 9

A developer performs vector similarity searches without creating a vector index.

What is the most likely consequence?

A. Embeddings become corrupted.

B. Query performance decreases significantly as the dataset grows.

C. SQL transactions stop working.

D. Documents cannot be embedded.

Answer: B

Explanation:
Without a vector index, the system typically performs an exhaustive comparison against every stored vector, resulting in much slower query performance on large datasets.

Question 10

Which statement best summarizes the role of vector search in AI-enabled database applications?

A. It replaces relational databases.

B. It removes the need for SQL queries.

C. It automatically generates embeddings.

D. It enables retrieval of information based on semantic meaning instead of exact text matching.

Answer: D

Explanation:
Vector search is designed to retrieve semantically similar information by comparing embedding vectors, making it a foundational capability for intelligent search, recommendation systems, and RAG-based applications.

Go to the DP-800 Exam Prep Hub main page