Lab 11: Implement RAG solutions: VECTOR_SEARCH T-SQL update required

[chrisutter]

Section: Retrieve data using vector search and format it as JSON context

Step: 1

The code in this window needs to be updated. TOP_N function is not longer supported. It shoudl look something like this:

-- Convert a question to an embedding and find the closest matching reviews

DECLARE @userquestion NVARCHAR(1000) =
N'What mountain bike can handle really technical rocky trails?';

DECLARE @questionVector VECTOR(1536);

-- Generate embedding for the question
SELECT @questionVector =
AI_GENERATE_EMBEDDINGS(
@userquestion
USE MODEL my_embedding_model
);

-- Find the top 5 most relevant reviews using ANN vector search
SELECT TOP (5)
p.Name AS ProductName,
p.ListPrice,
pc.Name AS Category,
r.Rating,
r.ReviewTitle,
r.ReviewText,
vs.distance AS Distance
FROM VECTOR_SEARCH(
TABLE = dbo.ProductReview,
COLUMN = ReviewVector,
SIMILAR_TO = @questionVector,
METRIC = 'cosine'
) AS vs
INNER JOIN dbo.ProductReview AS r
ON r.ProductReviewID = vs.ProductReviewID
INNER JOIN SalesLT.Product AS p
ON r.ProductID = p.ProductID
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
ORDER BY vs.distance
FOR JSON PATH;
GO