DP-800: Developing AI-Enabled Database Solutions

Question

You have a SQL database in Microsoft Fabric that contains a table named dbo.Products. The table contains product catalog data.

You need to create a stored procedure that performs hybrid search and meets these requirements:

Use approximate nearest neighbor (ANN) to retrieve the top 20 candidate products.
Re-rank only candidates that also match a full-text query.
Generate the query embedding.

How should you complete the Transact-SQL code?

Select

CREATE OR ALTER PROCEDURE dbo.SearchProducts
    @query_text NVARCHAR(4000),
    @keywords NVARCHAR(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @qv VECTOR(1536) = (@query_text USE MODEL Ada2Embeddings);

    ;WITH ann AS
    (
        SELECT t.product_id, t.product_name, s.distance
        FROM (
            TABLE = dbo.Products AS t,
            COLUMN = embedding,
            SIMILAR_TO = @qv,
            TOP_N = 20,
            METRIC = 'cosine'
        ) AS s
    )
    SELECT TOP (20)
        t.product_id,
        t.product_name,
        ann.distance,
        fts.RANK AS text_rank
    FROM ann
    JOIN dbo.Products AS t
        ON t.product_id = ann.product_id
    JOIN (dbo.Products, description, @keywords) AS fts
        ON t.product_id = fts.[KEY]
    ORDER BY (ann.distance * 0.6) + ((1.0 - fts.RANK/1000.0) * 0.4);
END;
GO

Explanation

AI_GENERATE_EMBEDDINGS creates the vector for the text query. VECTOR_SEARCH retrieves approximate nearest-neighbor candidates from the vector column. CONTAINSTABLE returns full-text matches with KEY and RANK, enabling the join to retain text-matching candidates and use their rank in the final ordering.

Learn more

DP-800: Developing AI-Enabled Database Solutions

AstroTutor

Notes

Readiness

QuestionQ95

Community Discussion