One SQL Anywhere – Part 3: Building a RAG Pipeline Right Inside SQL Database in Fabric

[rajendraongole1]

At the end of Part 2, where we put Copilot through its paces inside the SQL query editor, I promised we'd get into RAG next. So here we are.

if you missed part 2: One SQL Anywhere – Part 2: Copilot in SQL Database... - Microsoft Fabric Community

If you've been hearing "Retrieval-Augmented Generation" thrown around in every AI conversation for the last year and wondered what it actually looks like when it's wired into a database you already know how to query — this post is for you. No separate vector database, no new service to provision and babysit. Just Azure SQL Database in Fabric, a couple of Azure OpenAI model deployments, and T-SQL doing the heavy lifting.

By the end of this walkthrough, you'll have generated embeddings for real product data, run similarity searches that understand meaning rather than just matching keywords, and wrapped the whole thing in a stored procedure ready to sit behind a GraphQL API.

Let's get into it.

Task 1: Spinning up an Azure OpenAI resource

First stop, the Azure Portal. If you're already signed in with the same credentials you used for the Fabric portal, this is a one-click hop.

Search for OpenAI in the top search bar, select it, and hit Create → Azure OpenAI. Pick your resource group, set the region — I went with swedencentral — and give it a name (mine was azure-openai-test-63149671). For pricing, Standard S0 is the tier you want here.

Click through Networking and Tags with the defaults, review, and hit Create. Deployment takes a minute or two. Once it's done, go to the resource, expand Resource Management, and open Keys and Endpoint. Copy both — you'll be pasting them into T-SQL more than once in this exercise, so keep them somewhere handy.

Task 2: Deploying the models — text-embedding-ada-002 and GPT-4.1

From the resource Overview tab, jump into the Foundry portal, then head to Deployments in the left nav.

Click + Deploy model → Deploy base model, search for text-embedding-ada-002, confirm, and deploy — keep the deployment name exactly as-is, since we'll reference it by name in every query from here on. Give it a few seconds and you'll see it show up as Succeeded in the deployments list. Grab the Target URL and save it.

Repeat the same steps for gpt-4.1, making sure the Standard version is selected. Copy that Target URL too.

Two models deployed, two URLs saved. On to the database.

Task 3: Setting up the database credential

Here's where it starts feeling like home turf again — back in the Fabric query editor.

A database scoped credential is basically a stored, encrypted way for the database to authenticate against something outside of itself — in our case, the Azure OpenAI endpoint. First, a master key, then the credential itself:

sql-- Create a master key for the database
if not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##')
begin
create master key encryption by password = N'V3RYStr0NGP@ssw0rd!';
end
go

-- Create the database scoped credential for Azure OpenAI
if not exists(select * from sys.database_scoped_credentials where [name] = 'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/')
begin
create database scoped credential [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/]
with identity = 'HTTPEndpointHeaders', secret = '{"api-key":"YOUR_OPENAI_KEY"}';
end
go

Swap in your own endpoint and key, run it, and the database now knows how to talk to Azure OpenAI.

To prove it actually works, here's a fun little sanity check using sp_invoke_external_rest_endpoint — SQL Database in Fabric calling out to an external REST API directly from a query window:

sqldeclare @url nvarchar(4000) = N'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/openai/deployments/gpt-4/chat/completions?api-versio...';
declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"You are an expert joke teller."},
{"role":"system","content":"tell me a joke about a llama walking into a bar"}]}'
declare @ret int, @response nvarchar(max);

exec @ret = sp_invoke_external_rest_endpoint
@url = @url,
@method = 'POST',
@payload = @payload,
@Credential = [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/],
@timeout = 230,
@response = @response output;

select json_value(@response, '$.result.choices[0].message.content') as "Amazing, Awesome, Stupendous Joke";

Run it, and GPT-4.1 answers back straight into your result grid. Mine came back with a decent llama-and-alpaca joke — not going to win any awards, but the point isn't the punchline. The point is that a plain SQL query just called an LLM and got a response back in the same breath you'd use to run a SELECT. That's the whole premise this exercise builds on.

Task 4: Turning product data into embeddings

This is the core of the exercise, so let's slow down a bit.

An embedding is just a way of representing meaning as numbers — a vector of floating-point values that captures the semantic content of a piece of text. Two phrases that mean similar things end up with vectors that sit close to each other, even if the actual words are completely different. "Cat" and "kitty" don't share a single letter in common past the first, but semantically they're neighbors, and a good embedding model knows that.

We're going to generate embeddings for every row in the SalesLT.Product table and store the resulting vectors right alongside the product data.

First, a quick test call to the embeddings endpoint to see the shape of what comes back:

sqldeclare @url nvarchar(4000) = N'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/openai/deployments/text-embedding-ada-002/embeddings...';
declare @message nvarchar(max) = 'Hello World!';
declare @payload nvarchar(max) = N'{"input": "' + @message + '"}';

declare @ret int, @response nvarchar(max);

exec @ret = sp_invoke_external_rest_endpoint
@url = @url,
@method = 'POST',
@payload = @payload,
@Credential = [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/],
@timeout = 230,
@response = @response output;

select json_query(@response, '$.result.data[0].embedding') as "JSON Vector Array";

You'll get back a long JSON array of numbers — that's your embedding. Now let's give the Product table somewhere to actually store these:

sqlalter table [SalesLT].[Product]
add embeddings VECTOR(1536), chunk nvarchar(2000);

Notice the native VECTOR(1536) data type — this is one of the newer additions to SQL Database in Fabric, and it's what makes storing and querying embeddings directly in the database practical instead of clunky. We're also adding a chunk column, which holds the actual text we sent to get each embedding — useful for both debugging and for showing users what was actually matched later.

Next, a reusable stored procedure that wraps the embedding call so we're not repeating this logic every time:

sqlcreate or alter procedure dbo.create_embeddings
(
@input_text nvarchar(max),
@embedding vector(1536) output
)
AS
BEGIN
declare @url varchar(max) = 'https://AI_ENDPOINT_SERVERNAME.openai.azure.com/openai/deployments/text-embedding-ada-002/embeddings...';
declare @payload nvarchar(max) = json_object('input': @input_text);
declare @response nvarchar(max);
declare @retval int;

-- Call to Azure OpenAI to get the embedding of the search text
begin try
exec @retval = sp_invoke_external_rest_endpoint
@url = @url,
@method = 'POST',
@Credential = [https://AI_ENDPOINT_SERVERNAME.openai.azure.com/],
@payload = @payload,
@response = @response output;
end try
begin catch
select
'SQL' as error_source,
error_number() as error_code,
error_message() as error_message
return;
end catch
if (@retval != 0) begin
select
'OPENAI' as error_source,
json_value(@response, '$.result.error.code') as error_code,
json_value(@response, '$.result.error.message') as error_message,
@response as error_response
return;
end
-- Parse the embedding returned by Azure OpenAI
declare @json_embedding nvarchar(max) = json_query(@response, '$.result.data[0].embedding');

-- Convert the JSON array to a vector and set return parameter
set @embedding = CAST(@json_embedding AS VECTOR(1536));
END;

With that in place, a simple loop walks every product, builds a text "chunk" out of the name, color, category, model, and description, sends it off for embedding, and writes both the chunk and the vector back to the table:

sqlSET NOCOUNT ON
DROP TABLE IF EXISTS #MYTEMP
DECLARE @ProductID int
declare @text nvarchar(max);
declare @Vector vector(1536);
SELECT * INTO #MYTEMP FROM [SalesLT].Product
SELECT @ProductID = ProductID FROM #MYTEMP
SELECT TOP(1) @ProductID = ProductID FROM #MYTEMP
WHILE @@ROWCOUNT <> 0
BEGIN
set @text = (SELECT p.Name + ' '+ ISNULL(p.Color,'No Color') + ' '+ c.Name + ' '+ m.Name + ' '+ ISNULL(d.Description,'')
FROM
[SalesLT].[ProductCategory] c,
[SalesLT].[ProductModel] m,
[SalesLT].[Product] p
LEFT OUTER JOIN
[SalesLT].[vProductAndDescription] d
on p.ProductID = d.ProductID
and d.Culture = 'en'
where p.ProductCategoryID = c.ProductCategoryID
and p.ProductModelID = m.ProductModelID
and p.ProductID = @ProductID);
exec dbo.create_embeddings @text, @Vector output;
update [SalesLT].[Product] set [embeddings] = @Vector, [chunk] = @text where ProductID = @ProductID;
DELETE FROM #MYTEMP WHERE ProductID = @ProductID
SELECT TOP(1) @ProductID = ProductID FROM #MYTEMP
END

A quick check confirms nothing was missed:

sqlselect count(*) from SalesLT.Product where embeddings is null;

That should come back 0. And if you peek at the data now:

sqlselect top 10 chunk, embeddings from SalesLT.Product

...you'll see each row's chunk — the combined text description — sitting right next to its embeddings vector. Every product in the catalog now has a semantic fingerprint stored natively in the table.

Task 5: Vector similarity search — where it gets fun

This is the payoff. VECTOR_DISTANCE is the function that makes similarity search possible directly in T-SQL — no external vector store, no separate index to maintain, just a function call between two vectors.

Test 1 — "I am looking for a red bike and I dont want to spend a lot"

sqldeclare @search_text nvarchar(max) = 'I am looking for a red bike and I dont want to spend a lot'
declare @search_vector vector(1536)
exec dbo.create_embeddings @search_text, @search_vector output;
SELECT TOP(4)
p.ProductID, p.Name , p.chunk,
vector_distance('cosine', @search_vector, p.embeddings) AS distance
FROM [SalesLT].[Product] p
ORDER BY distance

The search text never mentions a price or a model number — it's phrased the way an actual person would ask. And the result is exactly what you'd expect: an affordable red bike near the top, with distance (cosine distance, lower is closer) ranking the results by how well they match the intent, not just the words.

Test 2 — "I am looking for a safe helmet that does not weigh much"

Same pattern, same procedure, different search text. The results come back as lightweight helmets, ranked correctly. One non-helmet item (a vest) sneaks into the results too, but notice its distance score is noticeably higher than the three actual helmets — the model is still doing its job, just being honest about a weaker match.

Test 3 — "Do you sell any padded seats that are good on trails?"

This is the one that actually shows off what embeddings are for. There's no product literally called "trail seat" in the catalog. But the search surfaces products described with terms like absorb shocks and bumps and foam-padded — the model made the semantic leap from "good on trails" to "shock-absorbing" and "padded" on its own. That's the difference between a keyword search and a similarity search: one matches strings, the other matches meaning.

Wrapping it in a stored procedure

Once the pattern is proven, it's worth packaging it properly. First, a general-purpose product finder with a configurable similarity threshold:

sqlcreate or alter procedure [dbo].[find_products]
@text nvarchar(max),
@Top int = 10,
@MIN_similarity decimal(19,16) = 0.80
as
if (@text is null) return;
declare @retval int, @qv vector(1536);
exec @retval = dbo.create_embeddings @text, @qv output;
if (@retval != 0) return;
with vector_results as (
SELECT
p.Name as product_name,
ISNULL(p.Color,'No Color') as product_color,
c.Name as category_name,
m.Name as model_name,
d.Description as product_description,
p.ListPrice as list_price,
p.weight as product_weight,
vector_distance('cosine', @qv, p.embeddings) AS distance
FROM
[SalesLT].[Product] p,
[SalesLT].[ProductCategory] c,
[SalesLT].[ProductModel] m,
[SalesLT].[vProductAndDescription] d
where p.ProductID = d.ProductID
and p.ProductCategoryID = c.ProductCategoryID
and p.ProductModelID = m.ProductModelID
and p.ProductID = d.ProductID
and d.Culture = 'en')
select TOP(@top) product_name, product_color, category_name, model_name, product_description, list_price, product_weight, distance
from vector_results
where (1-distance) > @MIN_similarity
order by
distance asc;
GO

And then a thin wrapper around it, using WITH RESULT SETS to make the output shape predictable — which matters if you're planning to expose this behind a GraphQL endpoint, since GraphQL needs to know exactly what columns and types to expect:

sqlcreate or alter procedure [find_products_api]
@text nvarchar(max)
as
exec find_products @text
with RESULT SETS
(
(
product_name NVARCHAR(200),
product_color NVARCHAR(50),
category_name NVARCHAR(50),
model_name NVARCHAR(50),
product_description NVARCHAR(max),
list_price INT,
product_weight INT,
distance float
)
)
GO

Test it the same way an API consumer eventually would:

sqlexec find_products_api 'I am looking for a red bike'

Clean input, clean output, ready to sit behind an app.

Honest take

Going in, I expected this to feel like duct-taping an AI feature onto a relational database — technically possible, awkward in practice. It didn't feel that way.

What stood out is how little ceremony there is. No separate vector database to provision, no index to sync, no pipeline gluing two systems together. The VECTOR data type and VECTOR_DISTANCE function live right next to the tables you already query every day, so the embeddings are just... columns. You back them up the same way, secure them the same way, join them the same way.

The trail-seat search is the one that sold me. That's not string matching dressed up — that's the model actually reasoning about meaning, and it's running from inside a SELECT statement.

If you're building anything that needs "search by intent, not by keyword" — product catalogs, support ticket lookups, document retrieval — and your data already lives in Azure SQL, this is a genuinely low-friction way to get there. You don't need a new platform. You need a VECTOR column and a stored procedure.

That's Part 3. If you're following along with Part 1 and Part 2, you've now gone from a plain SQL Database in Fabric, through Copilot-assisted querying, to a working RAG pipeline — all inside the same service.

Happy reading!