Implement AI capabilities in database solutions

-- Applies to: SQL Server 2025, Azure SQL Database, Azure SQL MI (Always-up-to-date), SQL database in Fabric
CREATE EXTERNAL MODEL EmbeddingSmall
AUTHORIZATION dbo
WITH (
    LOCATION   = 'https://contoso-ai.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-02-01',
    API_FORMAT = 'Azure OpenAI',                 -- also 'OpenAI', 'Ollama', 'ONNX Runtime'
    MODEL_TYPE = EMBEDDINGS,                     -- the only accepted model type
    MODEL      = 'text-embedding-3-small',
    CREDENTIAL = [https://contoso-ai.openai.azure.com/],
    PARAMETERS = '{"dimensions": 1536, "sql_rest_options": {"retry_count": 3}}'
);

ALTER EXTERNAL MODEL EmbeddingSmall SET (MODEL = 'text-embedding-3-large');   -- swap the model behind the name
GRANT EXECUTE ON EXTERNAL MODEL::EmbeddingSmall TO app_writer;                -- needed to use it in AI functions
SELECT name, location, api_format, model_type, model FROM sys.external_models;
DROP EXTERNAL MODEL EmbeddingSmall;

-- One embedding per product, built from the columns that carry meaning
UPDATE p
SET    p.Embedding = AI_GENERATE_EMBEDDINGS(
           CONCAT(p.Name, N'. Category: ', c.Name, N'. ', p.Description)
           USE MODEL EmbeddingSmall)
FROM   dbo.Product AS p
JOIN   dbo.Category AS c ON c.CategoryID = p.CategoryID
WHERE  p.Embedding IS NULL;                        -- or rows flagged as changed

-- Chunk long text and embed every chunk in one statement (compatibility level 170)
CREATE TABLE dbo.DocChunk
(
    DocID      int           NOT NULL,
    ChunkOrder bigint        NOT NULL,
    ChunkText  nvarchar(max) NOT NULL,
    Embedding  vector(1536)  NOT NULL,
    CONSTRAINT PK_DocChunk PRIMARY KEY (DocID, ChunkOrder)
);

INSERT dbo.DocChunk (DocID, ChunkOrder, ChunkText, Embedding)
SELECT d.DocID, c.chunk_order, c.chunk,
       AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL EmbeddingSmall)
FROM dbo.Document AS d
CROSS APPLY AI_GENERATE_CHUNKS(
        SOURCE     = d.Title + N'. ' + d.Body,   -- put the context the chunk needs into the text
        CHUNK_TYPE = FIXED,                       -- the only chunk type
        CHUNK_SIZE = 1000,                        -- characters, not tokens
        OVERLAP    = 10                           -- percent of CHUNK_SIZE, 0 to 50
     ) AS c;

-- Full-text index (preview in SQL database in Fabric)
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Product (Name, Description)
    KEY INDEX PK_Product
    WITH CHANGE_TRACKING AUTO;

-- Predicates: filter only
SELECT ProductID, Name FROM dbo.Product
WHERE CONTAINS(Description, N'"waterproof" AND NEAR((jacket, hood), 5)');

-- Rowset functions: return a RANK to order by or fuse
SELECT TOP (20) p.ProductID, p.Name, ft.[RANK]
FROM FREETEXTTABLE(dbo.Product, (Name, Description), N'light rain jacket for hiking') AS ft
JOIN dbo.Product AS p ON p.ProductID = ft.[KEY]
ORDER BY ft.[RANK] DESC;

DECLARE @a vector(3) = '[1, 2, 2]', @b vector(3) = '[2, 1, 2]';

SELECT VECTOR_DISTANCE('cosine',    @a, @b) AS cosine_distance,   -- 1 - cosine similarity: 0 = same direction
       VECTOR_DISTANCE('euclidean', @a, @b) AS euclidean,         -- straight-line distance
       VECTOR_DISTANCE('dot',       @a, @b) AS neg_dot,           -- negative dot product: smaller = more similar
       VECTOR_NORM(@a, 'norm2')            AS length,             -- 3
       VECTOR_NORMALIZE(@a, 'norm2')       AS unit_vector,        -- [0.333, 0.667, 0.667]
       VECTORPROPERTY(@a, 'Dimensions')    AS dims,               -- 3
       VECTORPROPERTY(@a, 'BaseType')      AS base_type;          -- float32

DECLARE @q vector(1536) = AI_GENERATE_EMBEDDINGS(N'warm jacket for winter cycling' USE MODEL EmbeddingSmall);

-- ENN / kNN: exact, scans every row — fine up to tens of thousands of rows, or as a recall baseline
SELECT TOP (10) ProductID, Name, VECTOR_DISTANCE('cosine', Embedding, @q) AS distance
FROM dbo.Product
WHERE CategoryID = 3
ORDER BY distance;

-- ANN: approximate, served by a DiskANN vector index with the same metric
CREATE VECTOR INDEX vix_Product_Embedding ON dbo.Product (Embedding)
WITH (METRIC = 'cosine', TYPE = 'DiskANN');      -- latest version needs at least 100 non-NULL rows

SELECT TOP (10) WITH APPROXIMATE p.ProductID, p.Name, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.Product AS p, COLUMN = Embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r
WHERE p.CategoryID = 3                           -- iterative filtering: applied during the search
ORDER BY r.distance;                             -- ascending distance only

-- Hybrid search: fuse a keyword ranking and a semantic ranking with reciprocal rank fusion
DECLARE @text nvarchar(400) = N'light rain jacket for hiking';
DECLARE @q vector(1536) = AI_GENERATE_EMBEDDINGS(@text USE MODEL EmbeddingSmall);
DECLARE @k int = 60;                              -- RRF constant; 60 is the conventional default

WITH keyword AS (
    SELECT TOP (50) ft.[KEY] AS ProductID,
           ROW_NUMBER() OVER (ORDER BY ft.[RANK] DESC) AS rnk
    FROM FREETEXTTABLE(dbo.Product, (Name, Description), @text) AS ft
),
semantic AS (
    SELECT ProductID, ROW_NUMBER() OVER (ORDER BY distance) AS rnk
    FROM (SELECT TOP (50) WITH APPROXIMATE p.ProductID, r.distance
          FROM VECTOR_SEARCH(TABLE = dbo.Product AS p, COLUMN = Embedding,
                             SIMILAR_TO = @q, METRIC = 'cosine') AS r
          ORDER BY r.distance) AS v                -- window functions need the subquery pattern
)
SELECT TOP (10) COALESCE(k.ProductID, s.ProductID) AS ProductID,
       COALESCE(1.0 / (@k + k.rnk), 0) + COALESCE(1.0 / (@k + s.rnk), 0) AS rrf_score
FROM keyword AS k
FULL OUTER JOIN semantic AS s ON s.ProductID = k.ProductID
ORDER BY rrf_score DESC;

CREATE OR ALTER PROCEDURE dbo.usp_AskCatalog
    @Question nvarchar(1000),
    @Answer   nvarchar(max) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @q vector(1536) = AI_GENERATE_EMBEDDINGS(@Question USE MODEL EmbeddingSmall);

    -- 1. Retrieve and 2. convert structured rows to compact JSON context
    DECLARE @context nvarchar(max) = (
        SELECT Name, Color, ListPrice, Description
        FROM (SELECT TOP (5) WITH APPROXIMATE p.Name, p.Color, p.ListPrice, p.Description, r.distance
              FROM VECTOR_SEARCH(TABLE = dbo.Product AS p, COLUMN = Embedding,
                                 SIMILAR_TO = @q, METRIC = 'cosine') AS r
              WHERE p.IsActive = 1
              ORDER BY r.distance) AS hits
        ORDER BY distance
        FOR JSON PATH);

    -- 3. Build the chat request: grounding instructions + context + question
    DECLARE @payload nvarchar(max) = JSON_OBJECT(
        'messages': JSON_ARRAY(
            JSON_OBJECT('role': 'system',
                        'content': 'Answer only from the products in the context. If the answer is not there, say you do not know.'),
            JSON_OBJECT('role': 'user',
                        'content': CONCAT('Context: ', @context, ' Question: ', @Question))),
        'temperature': 0.2,
        'max_tokens': 400);

    -- 4. Send it to the chat model (an external model cannot do this: MODEL_TYPE is EMBEDDINGS only)
    DECLARE @response nvarchar(max), @rc int;
    EXECUTE @rc = sp_invoke_external_rest_endpoint
        @url         = N'https://contoso-ai.openai.azure.com/openai/deployments/gpt-chat/chat/completions?api-version=2024-10-21',
        @method      = 'POST',
        @payload     = @payload,
        @credential  = [https://contoso-ai.openai.azure.com/],
        @timeout     = 60,
        @retry_count = 3,                          -- retries 408, 429, 500, 502, 503, 504
        @response    = @response OUTPUT;

    -- 5. Extract the answer; @rc is 0 for any 2xx, otherwise the HTTP status code
    SET @Answer = CASE WHEN @rc = 0
                       THEN JSON_VALUE(@response, '$.result.choices[0].message.content')
                       ELSE CONCAT('Model call failed: HTTP ', @rc) END;
END;

-- Ask for structured output, then shred it relationally
-- (add to the payload: 'response_format': JSON_OBJECT('type': 'json_object') and say "reply in JSON")
SELECT j.ProductName, j.Reason
FROM OPENJSON(JSON_VALUE(@response, '$.result.choices[0].message.content'), '$.recommendations')
     WITH (ProductName nvarchar(200) '$.name',
           Reason      nvarchar(400) '$.reason') AS j;