In this post now, we will combine some of these functionalities to implement a simple RAG based shopping assistant with SQL Server 2025 (Preview).<\/p>\n\n\n\n
What is RAG?<\/h2>\n\n\n
But first of all I will introduce you in the concept of RAG. As you may already noticed in recent years, AI chat models are great. They are able to understand your prompts, they can perform reasoning to \u201cthink\u201d about what you ask them and they can provide you an individual answer. But the model alone is limited to the data on which it was trained on.<\/p>\n\n
So when I ask a chat model like GPT-5 a very general question like \u201cWhat is the capital of Australia?\u201d the model is able to answer this question without any problem because it\u2019s general knowledge and the model certainly came into touch with this knowledge through its training data. But when I ask the model something which is only known through access on specific ( usually private) data sources\u00a0 the model is not able to answer such a question at least not alone. For example\u00a0 when I ask the model how many vacation days\u00a0 I have left for this year\u00a0 the model\u00a0 is obviously not able to answer such a question because such information\u2019s are stored in\u00a0 company internal tools:<\/p>\n\n\n This\u00a0 is the point where Retrieval\u00a0 Augmented Generation (RAG) comes into play. RAG is an approach that searches external data sources for information relevant to the user’s prompt. The retrieved data is then used to augment the generation process of the AI model, allowing it to produce a context-aware and more accurate response.<\/p>\n\n And this is what we will implement within this blog post. We will build a simple chat service\u00a0 which will help the customers of the ShopAI Online Shop to find the product of their desire.<\/p>\n\n\n Let\u2019s take a look at the RAG\u00a0 architecture for our purpose.\u00a0 Therefore take a look at the\u00a0 schema below\u00a0 and on the numbers which are representing steps in the RAG-process:<\/p>\n\n\n We will be able to handle the whole data logic inside T-SQL with the latest features of SQL-Server 2025 (Preview).<\/p>\n\n\n So far so good. Let\u2019s start now with the practical implementation of our vision.<\/p>\n\n The AI models I\u2019m using are the gpt-4o-mini model for the chat completion tasks and the text-embedding-3-small model to create embeddings for the users prompt. Embeddings for product data are already persisted in our database (refer to the earlier blog for details). When generating embeddings for user queries, use the exact same embedding model and settings used for the stored data.<\/p>\n\n To learn how to deploy an AI model in Azure you should also check out my previous blog posts where I\u2019m guiding you through the process. For this article my models are already deployed in Azure AI Foundry:<\/p>\n\n\n To use now the models inside T-SQL we have to create first a database scoped credential for the chat completion model. You can do that with the following statement. You\u2019ll find the API key inside Azure AI Foundry (Check out the previous Blog posts for detailed instructions):<\/p>\n\n Then we will do the same for the embedding model:<\/p>\n\n And then we will register our embedding model to be able to generate data embeddings with the T-SQL built in functions:<\/p>\n\n As we have now an AI model for chat completion and one for generating data embeddings, we are now ready to write our code for processing the data logic. We will handle all the data logic and interaction with the AI models within a T-SQL stored procedure. I will explain the stored procedure I wrote for that in this chapter.<\/p>\n\n We have two Input Parameters which is the @SearchText<\/strong> parameter and the @ChatHistory<\/strong> Parameter. The @SearchText<\/strong> parameter represents the input text which the user is giving to our shopping assistant and the @ChatHistory<\/strong> is representing the history of the conversation and is per default set to NULL. We will handle the history of the conversation outside of the stored procedure.<\/p>\n\n\n Then the stored procedure will generate an embedding of the user query which is stored in the @SearchText<\/strong> variable and it will perform an exact vector distance search based on the product catalog data and the user query. Then it stores the top 5 findings as a list of JSON objects with the attributes product name, product description and product price:<\/p>\n\n\n In the next step the Stored procedure generates a prompt for the AI chat completion model without a previous chat history (in case the @ChatHistory<\/strong> parameter is NULL):<\/p>\n\n\n If there is a previous chat history (the @ChatHistory<\/strong> parameter is not NULL) the stored procedure is generating a prompt which is also including the provided chat history data:<\/p>\n\n\n And finally the stored procedure calls the API of the chat completion model with the generated prompt and returns\/selects the response from the model:<\/p>\n\n\n The process of RAG isn\u2019t that complicated, right? To recap:<\/p>\n\n\n Of course, in practice things can get more complex but at a high level, that\u2019s the essence of how RAG works.<\/p>\n\n Let\u2019s test the stored procedure with a sample user request and let\u2019s see if we get a nice answer back with some product recommendations:<\/p>\n\n\n Not bad for some few lines of code. This is the text I got back for my request:<\/p>\n\n\n So we have now a stored procedure which is able to generate context aware product recommendations. Now let\u2019s wrap that up in a better-looking front end.<\/p>\n\n I\u2019m building that it in Python with Streamlit, a framework for quickly spinning up web apps, perfect for rapid prototyping. Make therefore sure that you have streamlit installed in your environment. So let\u2019s take a look at the python code.<\/p>\n\n First I\u2019m importing the modules which we need for our ShopAI Assistant. This is \u201cpyodbc\u201d for interacting with our database, \u201cos\u201d to read environment variables from operating system, \u201cstreamlit\u201d itself, the \u201ctime\u201d module to work efficient with time data, the \u201cload_dotenv\u201d method to load our environment variables into the operating system and \u201cjson\u201d to work with json data:<\/p>\n\n\n Then I\u2019m creating a .env file in my project directory to store our environment variables, which are the Server and Instance Name where our ShopAI database is running on, a SQL user with a password to connect to the database (make sure the user exists and has the appropriate rights):<\/p>\n\n\n In my python code I then wrote a function as a wrapper around the SQL stored procedure:<\/p>\n\n\n Then I\u2019m loading the environment variables from our file into the operating system and I\u2019m building our pyodbc connection string:<\/p>\n\n\n Then we continue with Streamlit. We start by initializing the first assistant message in the st.session_state variable, a built-in Streamlit object that stores session data such as the conversation history. After that, we loop through all messages in the session state and display them to the user. Finally, we wait for new user input through the chat input field, so the conversation can continue interactively.<\/p>\n\n\n Next, we handle the user input. When the user enters a message, it\u2019s captured by st.chat_input()<\/strong>.<\/p>\n\n We then check if there are already previous messages stored in st.session_state<\/strong>. If yes, we convert the chat history (except for the very first assistant message) into JSON format and store it in the variable chat_history<\/strong>. Otherwise, we set chat_history<\/strong> to None. We do this for handling the conversation history inside python and pass it to the SQL stored procedure.<\/p>\n\n We will then construct the input parameters for our get_product_recommendation<\/strong> function and we store the parameters inside a tuple which we call “p”<\/strong>.<\/p>\n\n Then we append and display the user prompt to the chat front end.<\/p>\n\n\n Finally we invoke the SQL stored procedure through our python function and we will display the response from the AI model in a typing like manner:<\/p>\n\n\n We have now wrote a stored procedure for processing the data logic and interacting with the gpt embedding and chat completion model and we have a simple Streamlit app to handle the conversation history and for the front end.<\/p>\n\n Let\u2019s test what we did. To start the Streamlit web app I simple run the command “streamlit run app.py2:<\/p>\n\n\n A browser window should open and you will be able to see the front end of the shopping assistant:<\/p>\n\n\n Fine! let\u2019s ask him who he is and how he can helps us \u2026<\/p>\n\n\n Looks like he knows his purpose\ud83d\ude09 let\u2019s see if he is able to find the products of our desire. I\u2019m looking for some sport shoes \u2026<\/p>\n\n\n Awesome! We are getting a suitable response! The only weakness I see is, that we are getting twice the product \u201cNew Look Sneakers (Black)\u201d recommended but with different prices.<\/p>\n\n But when looking at the data below it\u2019s actually not the fault of our assistant because we have this product two times in our product catalogue. Good point to see how data quality matters \ud83d\ude09:<\/p>\n\n\n I\u2019m interested in the “Skechers Sneakers (Black)” but I\u2019m not fully convinced yet. Let\u2019s see if the assistant is doing a great job convincing me:<\/p>\n\n\n Sounds good for my! I\u2019m totally convinced that these sneakers would perfectly match to my desire. Assistant you made a good job!<\/p>\n\n\n As you\u2019ve seen in this post, we were able to easily build an AI shopping assistant, with all the data processing and RAG logic running directly inside SQL Server 2025 (Preview)! We only used python for handling the conversation history and for a nice looking front end with Streamlit.<\/p>\n\n If you are looking for more inspiration regarding this topic I can highly recommend you to visit the following GitHub page from Microsoft: https:\/\/github.com\/Azure-Samples\/azure-sql-db-chatbot\/<\/a><\/p>\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild1.png\")
<\/figure>\n\n\nRAG Architecture:<\/h2>\n\n\n
\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild2.png\")
<\/figure>\n\n\nPreparing the AI Models:<\/h2>\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild3.png\")
<\/figure>\n\n\n\nUse ShopAI\nGo\n-- Create database scoped credential:\nCREATE DATABASE SCOPED CREDENTIAL [https:\/\/shopai-blog.openai.azure.com\/openai\/deployments\/gpt-4o-mini\/] \/* always youse an url which is more generic*\/\n WITH IDENTITY = 'HTTPEndpointHeaders', secret = '{"api-key":"YOUR-API-KEY"}';\nGO\n\n<\/pre><\/div>\n\n\nUse ShopAI\nGo\n-- Create database scoped credential:\nCREATE DATABASE SCOPED CREDENTIAL [ https:\/\/shopai-blog.openai.azure.com\/openai\/deployments\/text-embedding-3-small\/] \/* always youse an url which is more generic*\/\n WITH IDENTITY = 'HTTPEndpointHeaders', secret = '{"api-key":"YOUR-API-KEY"}';\nGO\n\n<\/pre><\/div>\n\n\nUse ShopAI\n-- Create EXTERNAL MODEL\nCREATE EXTERNAL MODEL OpenAITextEmbedding3Small\nAUTHORIZATION dbo\nWITH (\n LOCATION = 'https:\/\/shopai-blog.openai.azure.com\/openai\/deployments\/text-embedding-3-small\/embeddings?api-version=2023-05-15',\n API_FORMAT = 'Azure OpenAI',\n MODEL_TYPE = EMBEDDINGS,\n MODEL = 'text-embedding-3-small',\n CREDENTIAL = [https:\/\/shopai-blog.openai.azure.com\/openai\/deployments\/text-embedding-3-small\/],\n PARAMETERS = '{"Dimensions":1536}'\n);\n\n<\/pre><\/div>\n\n\nRAG Stored Procedure:<\/h2>\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild4.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild5.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild6.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild7.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild8.png\")
<\/figure>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild9.png\")
<\/figure>\n\n\nGreat choice! \ud83d\udc99 Here are some stylish blue t-shirts that you might love:\u00a0 1. **Nike V-Neck Tees (Blue)** – **21.20 CHF**\u00a0\u00a0\u00a0 (Thoughts: This tee is perfect for sunny days with its breathable fabric and flattering V-neck cut, making it a great choice for both comfort and style!)\u00a0 2. **Boohoo Graphic Tees (Blue)** – **20.86 CHF**\u00a0\u00a0\u00a0 (Thoughts: If you want to stand out, this tee features bold graphics and a vibrant color, ideal for expressing your unique personality during casual outings!)\u00a0 3. **Fast Retailing V-Neck Tees (Blue)** – **23.93 CHF**\u00a0\u00a0\u00a0 (Thoughts: This lightweight tee is designed for women who crave comfort and style, making it perfect for pairing with jeans or skirts for a chic summer look!)\u00a0 4. **Madewell V-Neck Tees (Blue)** – **58.35 CHF**\u00a0\u00a0\u00a0 (Thoughts: For a more premium option, this tee offers a refreshing hue and modern design that instantly elevates your casual attire while keeping you cool!)\u00a0 5. **Armani Exchange V-Neck Tees (Blue)** – **55.08 CHF**\u00a0\u00a0\u00a0 (Thoughts: If you’re looking for a touch of designer elegance, this tee is crafted for comfort and style, ensuring a flattering fit for warm days!)\u00a0 Let me know if you need more information or help with your purchase! \ud83d\ude0a<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Shop Assistant Web app<\/h2>\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild10.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild11.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild12.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild13.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild14.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild15.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild23.png\")
<\/figure>\n\n\n\nTesting the ShopAI assistant<\/h2>\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild24.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild18.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild19.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild20.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild21.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/10\/Bild22.png\")
<\/figure>\n\n\nKey Takeaways<\/h2>\n\n\n