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
      --> Identify when to use vector-related types and functions for semantic searching, including VECTOR_NORMALIZE, VECTOR_DISTANCE, VECTORPROPERTY, and 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

Modern AI-powered database applications increasingly rely on semantic search, which retrieves information based on meaning rather than exact keyword matches. SQL Server 2025 (Preview), Azure SQL Database, and Azure SQL Managed Instance now include native vector capabilities, allowing developers to store embeddings and perform semantic searches directly inside the database.

Instead of exporting data to a separate vector database, developers can use built-in vector data types and functions to compare embeddings, calculate similarity, inspect vector metadata, normalize vectors, and perform efficient nearest-neighbor searches.

For the DP-800 certification exam, you should understand:

• When semantic search is appropriate
• The purpose of the VECTOR data type
• How VECTOR_DISTANCE measures similarity
• Why VECTOR_NORMALIZE is useful
• How VECTORPROPERTY retrieves vector metadata
• When to use VECTOR_SEARCH
• Performance considerations
• Common semantic search design patterns

Understanding Semantic Search

Traditional SQL searches compare exact values.

Example:

WHERE Description LIKE '%car%'

This search only returns rows containing the word car.

Semantic search instead compares meaning.

Searching for:

vehicle

may also return:

• automobile
• SUV
• truck
• sedan
• crossover

because their embeddings are close together within vector space.

Native Vector Support in SQL

Microsoft SQL now supports vectors as first-class database objects.

Instead of storing embeddings externally, SQL databases can store:

• relational columns
• vector columns
• AI metadata

inside one table.

Example:

This enables SQL to perform both relational filtering and semantic similarity searches.

VECTOR Data Type

The VECTOR data type stores embedding values.

Example:

Embedding VECTOR(1536)

The dimension must exactly match the embedding model.

Examples:

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

The VECTOR type is the foundation of all semantic search operations.

When to Use VECTOR_DISTANCE

VECTOR_DISTANCE measures how similar two vectors are.

Think of it as calculating the “distance” between meanings.

Smaller distance

↓

More similar

Larger distance

↓

Less similar

Example:

Customer query:

lightweight laptop

Document A

portable notebook computer

Very small distance

Document B

kitchen appliances

Very large distance

Common Uses of VECTOR_DISTANCE

Developers commonly use VECTOR_DISTANCE to:

• Rank search results
• Compare embeddings
• Measure semantic similarity
• Build recommendation engines
• Find related documents
• Identify duplicate content
• Support AI assistants

Example Scenario

Suppose a user searches:

cloud database backup

SQL compares the query embedding against stored embeddings.

Each document receives a distance score.

Example:

The smallest distance represents the best semantic match.

Choosing a Distance Metric

Several similarity calculations exist.

Common metrics include:

• Cosine similarity
• Euclidean distance
• Dot product

SQL vector functions abstract much of this complexity.

Developers simply request semantic similarity without implementing complex mathematics.

Why VECTOR_NORMALIZE Exists

Different vectors may have different magnitudes.

Normalization converts vectors into standardized lengths.

Instead of comparing:

Length + Direction

only

Direction

is compared.

This improves consistency.

When to Normalize Vectors

Normalization is commonly used when:

• comparing embeddings from different sources
• improving cosine similarity calculations
• preprocessing vectors
• preparing vectors before indexing

Many embedding models already generate normalized vectors.

Others do not.

Benefits of VECTOR_NORMALIZE

Normalization helps:

• improve comparison consistency
• reduce magnitude bias
• improve semantic similarity scoring
• produce more reliable nearest-neighbor searches

VECTORPROPERTY

VECTORPROPERTY retrieves metadata about vectors.

Rather than comparing vectors, it provides information about them.

Examples include:

• dimension count
• storage characteristics
• metadata
• vector properties

Developers often use VECTORPROPERTY for:

• validation
• diagnostics
• troubleshooting
• quality checks

Example Scenario

A developer receives embeddings from multiple AI models.

Some generate:

768 dimensions

Others generate:

1536 dimensions

Before inserting data, the developer verifies dimensions using VECTORPROPERTY.

This prevents invalid inserts.

VECTOR_SEARCH

VECTOR_SEARCH performs semantic nearest-neighbor searches.

Instead of writing complex similarity calculations manually, developers can search vectors directly.

Typical workflow:

User Question

↓

Generate embedding

↓

VECTOR_SEARCH

↓

Most similar documents

↓

Return results

When to Use VECTOR_SEARCH

VECTOR_SEARCH is ideal for:

• Retrieval-Augmented Generation (RAG)
• AI chatbots
• document search
• recommendation engines
• semantic search portals
• customer support systems
• knowledge bases

VECTOR_SEARCH vs VECTOR_DISTANCE

Although related, they serve different purposes.

VECTOR_DISTANCE

• compares two vectors

VECTOR_SEARCH

• searches an entire collection

Think of it this way:

VECTOR_DISTANCE

↓

Individual comparison

VECTOR_SEARCH

↓

Database-wide search

Example Workflow

A user asks:

How do I configure Azure SQL backups?

Step 1

Generate query embedding.

↓

Step 2

VECTOR_SEARCH finds similar documents.

↓

Step 3

Top documents returned.

↓

Step 4

LLM generates an answer.

Combining SQL Filtering with VECTOR_SEARCH

One advantage of SQL databases is hybrid querying.

Example:

Return only:

Category = Documentation

AND

perform semantic search.

This combines relational filtering with AI similarity.

Benefits include:

• better accuracy
• faster searches
• improved relevance

Performance Considerations

Semantic search can become expensive.

Best practices include:

• use vector indexes
• normalize vectors when appropriate
• filter relational data first
• avoid unnecessarily large embeddings
• use approximate nearest-neighbor indexes
• limit returned results

Typical Semantic Search Architecture

Documents

↓

Generate embeddings

↓

Store vectors

↓

Create vector index

↓

User submits question

↓

Generate query embedding

↓

VECTOR_SEARCH

↓

Nearest neighbors

↓

LLM response

Choosing the Correct Function

Best Practices

• Store embeddings using the VECTOR data type.
• Match VECTOR dimensions to the embedding model.
• Use VECTOR_SEARCH for semantic retrieval.
• Use VECTOR_DISTANCE for direct similarity comparisons.
• Normalize vectors when required by the similarity metric.
• Use VECTORPROPERTY to validate vector characteristics.
• Combine relational filters with vector searches.
• Create vector indexes for large datasets.
• Store embedding model versions alongside vectors.
• Monitor storage and indexing costs.

Common DP-800 Exam Tips

• Understand when semantic search is preferable to keyword search.
• Know the purpose of each vector function.
• Understand that VECTOR_DISTANCE compares two vectors, while VECTOR_SEARCH searches an entire dataset.
• Remember that VECTOR_NORMALIZE standardizes vectors before comparison.
• Know that VECTORPROPERTY retrieves vector metadata rather than similarity scores.
• Expect scenario-based questions requiring you to choose the correct vector function for a given task.
• Understand how these functions support RAG, AI assistants, recommendation systems, and semantic search.

Practice Exam Questions

Question 1

A development team is building a Retrieval-Augmented Generation (RAG) application. They need to compare a user’s query embedding against thousands of stored document embeddings and return the most semantically similar documents.

Which SQL function is specifically designed for this purpose?

A. VECTOR_DISTANCE

B. VECTOR_SEARCH

C. VECTORPROPERTY

D. VECTOR_NORMALIZE

Correct Answer: B

Explanation:

VECTOR_SEARCH is designed to search an entire collection of stored vectors and return the nearest neighbors based on semantic similarity. VECTOR_DISTANCE compares only two vectors, VECTORPROPERTY returns metadata, and VECTOR_NORMALIZE standardizes vectors before comparison.

Question 2

An application receives embeddings from several AI models. Before storing them in SQL, developers want to verify that every embedding contains the expected number of dimensions.

Which function should they use?

A. VECTORPROPERTY

B. VECTOR_DISTANCE

C. VECTOR_SEARCH

D. VECTOR_NORMALIZE

Correct Answer: A

Explanation:

VECTORPROPERTY returns metadata about a vector, including characteristics such as its dimensions. This makes it ideal for validating vectors before they are stored.

Question 3

A developer needs to calculate how semantically similar two individual product descriptions are after generating embeddings for each.

Which function should be used?

A. VECTORPROPERTY

B. VECTOR_SEARCH

C. VECTOR_DISTANCE

D. VECTOR_NORMALIZE

Correct Answer: C

Explanation:

VECTOR_DISTANCE calculates the similarity or distance between two vectors. It is appropriate when directly comparing one embedding against another rather than searching an entire dataset.

Question 4

A machine learning engineer wants to eliminate differences caused by varying vector magnitudes before calculating cosine similarity.

Which function is most appropriate?

A. VECTORPROPERTY

B. VECTOR_SEARCH

C. VECTOR_DISTANCE

D. VECTOR_NORMALIZE

Correct Answer: D

Explanation:

VECTOR_NORMALIZE scales vectors to a consistent length while preserving their direction. This improves similarity calculations that rely on normalized vectors, particularly cosine similarity.

Question 5

A customer support chatbot first filters documentation to only include networking articles and then performs semantic retrieval over those documents.

What is the primary advantage of this approach?

A. It removes the need for embeddings.

B. It combines relational filtering with semantic search for improved relevance.

C. It converts keyword search into full-text search.

D. It prevents vector indexing.

Correct Answer: B

Explanation:

Filtering relational data before performing vector search reduces the search space and increases the relevance of returned results, improving both performance and accuracy.

Question 6

A SQL developer needs to rank five candidate documents according to how closely each one matches a user’s question.

Which function should be applied repeatedly against each candidate vector?

A. VECTOR_DISTANCE

B. VECTOR_SEARCH

C. VECTORPROPERTY

D. VECTOR_NORMALIZE

Correct Answer: A

Explanation:

VECTOR_DISTANCE computes similarity between two vectors. Developers can compare the query vector against multiple document vectors and rank the results by the smallest distance.

Question 7

Which scenario is the best use case for VECTOR_SEARCH?

A. Determining the number of dimensions stored within a vector

B. Standardizing vectors before storage

C. Finding the most similar documents across an entire knowledge base

D. Comparing only two vectors for similarity

Correct Answer: C

Explanation:

VECTOR_SEARCH is optimized for nearest-neighbor retrieval across an entire vector collection, making it ideal for semantic search applications such as RAG systems and AI assistants.

Question 8

An organization stores millions of embeddings inside Azure SQL Database.

Which action provides the greatest improvement in semantic search performance?

A. Increasing the embedding dimensions

B. Eliminating relational filtering

C. Replacing vectors with VARCHAR columns

D. Creating vector indexes

Correct Answer: D

Explanation:

Vector indexes significantly improve nearest-neighbor search performance over large datasets. Without indexing, vector searches become increasingly expensive as data volumes grow.

Question 9

A developer mistakenly uses VECTOR_SEARCH when they simply need to compare two embeddings generated during a unit test.

Which function would have been the more appropriate choice?

A. VECTORPROPERTY

B. VECTOR_DISTANCE

C. VECTOR_NORMALIZE

D. VECTOR_SEARCH

Correct Answer: B

Explanation:

VECTOR_DISTANCE compares two vectors directly. VECTOR_SEARCH is intended for searching an entire vector collection and would introduce unnecessary overhead for a simple comparison.

Question 10

Which statement best describes VECTORPROPERTY?

A. It calculates semantic similarity between vectors.

B. It searches vector indexes for nearest neighbors.

C. It retrieves metadata about stored vectors.

D. It converts text into embeddings.

Correct Answer: C

Explanation:

VECTORPROPERTY returns information about vectors, such as their dimensions or other characteristics. It does not calculate similarity, generate embeddings, or perform semantic searches.

DP-800 Exam Tips

• Know the distinction between VECTOR_DISTANCE (two-vector comparison) and VECTOR_SEARCH (collection-wide nearest-neighbor search).
• Use VECTORPROPERTY to inspect or validate vector metadata before processing.
• Apply VECTOR_NORMALIZE when your similarity metric or embedding workflow benefits from normalized vectors.
• Combine relational filtering with semantic search to improve performance and relevance.
• Create vector indexes for large datasets to optimize semantic search operations.
• Expect scenario-based exam questions that require selecting the appropriate vector function based on a real-world AI application, such as RAG, semantic search, recommendation systems, or AI chatbots.

Go to the DP-800 Exam Prep Hub main page