Question
You have an Azure SQL database named SalesDB that contains a table named dbo.Articles. dbo.Articles contains two million articles with embeddings. The articles are updated frequently throughout the day.
You query the embeddings by using VECTOR_SEARCH.
Users report that semantic search results do NOT reflect the updates until the following day.
You need to ensure that the embeddings are updated whenever the articles change. The solution must minimize CPU usage on SalesDB.
Which embedding maintenance method should you implement?
- A. Modify the query to use VECTOR_DISTANCE instead of VECTOR_SEARCH.
- B. Enable change data capture (CDC) on dbo.Articles and use an Azure Functions app to process CDC changes. Correct
- C. Run an hourly Transact-SQL job that regenerates embeddings for all the rows in dbo.Articles.
- D. On dbo.Articles, create a trigger that calls AI_GENERATE_EMBEDDINGS for each inserted or updated row.
Explanation
CDC records which rows changed by reading the transaction log asynchronously, and an Azure Functions app then generates embeddings for only those rows outside the database, so SalesDB does little of the work. A trigger that calls AI_GENERATE_EMBEDDINGS makes an external call from the engine inside every write, which slows a frequently updated table and uses database resources. An hourly job regenerating two million rows is wasteful and still leaves results out of date. VECTOR_DISTANCE only changes how the search runs; it does not refresh any embedding.
