Microsoft DP-800: Implement AI capabilities in database solutions Practice Questions

The cosine metric should be specified when the application computes cosine similarity (or equivalently, cosine distance), which is the standard metric used with text and multimodal embeddings from models like OpenAI's text-embedding-ada-002. The index metric must match the distance function used at query time for the ANN index to accelerate searches correctly. The euclidean (L2) metric measures straight-line distance between vectors and is appropriate for geometric or spatial embeddings but not the typical choice for text embeddings with cosine similarity. The manhattan (L1) metric measures the sum of absolute differences and is rarely used with text embeddings. dot_product measures raw inner product without normalization; while related to cosine similarity for unit-normalized vectors, specifying it when the application uses cosine similarity would produce incorrect ANN results unless vectors are pre-normalized.

The VECTOR data type is the native SQL data type introduced in Azure SQL Database specifically for storing high-dimensional floating-point vectors used in similarity search scenarios. It provides efficient storage and integrates directly with vector search functions like VECTOR_DISTANCE and VECTOR_SEARCH. VARBINARY(MAX) can technically store raw bytes but has no semantic understanding of vector operations and cannot be used with built-in vector functions. NVARCHAR(MAX) is a text type unsuitable for numeric vector data. FLOAT ARRAY is not a valid T-SQL data type.

Reciprocal Rank Fusion (RRF) is the standard algorithm for merging ranked result lists from heterogeneous retrieval methods (e.g., full-text and vector search) into a single ranking without requiring score normalization. Each result's score is computed as the sum of 1/(k + rank) across all lists. Cosine distance averaging is not meaningful because full-text relevance scores and vector distances are on incompatible scales and semantics. BM25 is the scoring algorithm used internally by full-text search, not a merging technique for combining two distinct result sets. A cross-encoder is a neural re-ranking model that is not available natively in Azure SQL Database, and VECTOR_DISTANCE only computes pairwise vector similarity — it cannot merge ranked lists from different retrieval methods.

Azure OpenAI models expect text-based context in the prompt. The idiomatic T-SQL approach is to use FOR JSON PATH or FOR JSON AUTO to serialize relational result sets into JSON strings, which can then be embedded directly in the JSON request body constructed for sp_invoke_external_rest_endpoint. CSV via BCP format directives is not a native T-SQL serialization method and would require external tooling; moreover, LLMs process JSON-structured context far more reliably than raw CSV. A VARBINARY binary stream is a raw binary format that cannot be embedded in a JSON text payload and is not interpretable by the language model. Exporting to Azure Blob Storage parquet adds unnecessary latency, requires external storage access, and the model cannot read parquet files directly — it needs text in the prompt.

VECTOR_SEARCH is the function designed for ANN (Approximate Nearest Neighbor) searches in Azure SQL Database. It leverages vector indexes to return approximate results much faster than exhaustive search, making it suitable for large-scale similarity lookups. VECTOR_DISTANCE with ORDER BY and TOP performs an exhaustive nearest neighbor (ENN) scan — it computes the exact distance to every row, which is accurate but does not scale well for large tables without an index scan shortcut. FREETEXT is a full-text search predicate for keyword-based natural language queries and has no concept of vector embeddings. CONTAINS with NEAR is a full-text search construct for proximity-based keyword matching, not semantic vector similarity.