{"id":107690,"date":"2025-10-22T12:25:31","date_gmt":"2025-10-22T12:25:31","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107690"},"modified":"2025-12-17T11:15:59","modified_gmt":"2025-12-17T11:15:59","slug":"an-ai-powered-t-sql-assistant-built-with-python-and-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/an-ai-powered-t-sql-assistant-built-with-python-and-sql-server\/","title":{"rendered":"How to Build an AI-Powered T-SQL Assistant with Python & SQL Server"},"content":{"rendered":"\n

If you\u2019re a SQL Server DBA<\/a> or developer looking to harness AI for your everyday scripting workflows, this article will walk you through building an AI-powered T-SQL assistant<\/strong> using Python<\/a> and SQL Server<\/a>.<\/p>\n\n\n\n

We\u2019ll cover how to index your scripts with embeddings, store and search them semantically in SQL, and then build a chat-style interface that delivers context-aware script recommendations.<\/p>\n\n\n\n

Whether you\u2019re already familiar with AI or just starting out, this walkthrough shows how to apply cutting-edge features in a practical, real-world DBA scenario.<\/p>\n\n\n\n

To Start: A Bit of Background<\/h2>\n\n\n\n

A very strong memory I have is of my father and his toolbox, with all kinds of tools for the varied repairs that were needed at home. Sometimes, he would spend several minutes looking for a specific screw for something he needed to fix. Over the years, he collected more and more tools – exactly as I have done after many years of being a SQL Server DBA<\/a>.<\/p>\n\n\n\n

Over time I’ve gathered numerous SQL scripts that have helped me in various situations, but quickly discovered that finding the right script for the right situation often took far too long, just as my father experienced with his toolbox.<\/p>\n\n\n\n

Thanks to AI, that pain can finally be eased: I’ve made a tool to easily and quickly find the best script for any given need, using new AI features from SQL Server 2025<\/a> (which are already available in Azure SQL Database<\/a>) and Python<\/a> code that loads an AI model on a GPU to generate and search through data. <\/p>\n\n\n\n

In this article I\u2019ll give you a detailed look at how it all works. If you’re a DBA who has never touched AI beyond ChatGPT<\/a>, this is a great opportunity to explore a real-world example in a context you already know: finding the right T-SQL<\/a> script for your needs. And, if you’re not a DBA, you’ll still get an understanding of how to integrate AI into your own solutions and see how it can be used for much more than just text generation.<\/p>\n\n\n\n

Before continuing, I recommend reading this article about embeddings<\/a>.<\/em><\/p>\n\n\n\n

Introducing my Git Repository: SQL Server Lib<\/h1>\n\n\n\n

So before we get started, here\u2019s a quick introduction to my Git repository, SQL Server Lib<\/a>. The names and comments are in Portuguese but should be pretty obvious for the most part, plus the search function works in any language.<\/em><\/p>\n\n\n\n

This repository is the result of a personal project I started this year to review the many useful scripts I’ve saved in my time as a DBA and share them – for free – with the whole world:<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

<\/a><\/p>\n\n\n\n

So far, I’ve saved more than 490 scripts to my local folder (started with a free Dropbox<\/a>, later migrated to OneDrive), ranging from simple scripts to identify queries causing CPU usage, to utilities like finding a string in every column that contains text. This number keeps increasing as I keep adding new scripts or enhancing existing ones. <\/p>\n\n\n\n

At the time of writing, 102 scripts have been added to the repo, as seen by the progress bar on the repo so everyone can keep track of how many are left to add:<\/p>\n\n\n\n

\"The<\/figure>\n\n\n\n

<\/a><\/p>\n\n\n\n

Another advantage of doing this is that anyone can help me enhance these scripts by coming up with new ideas, or just by fixing something. Simply submit a PR via Git and then I (or future maintainers) can review it. In doing so, we can build a free SQL Server script library reviewed by the community itself.<\/em><\/p>\n\n\n\n

AI to the Rescue: Ask For a Script Naturally<\/h2>\n\n\n\n

As mentioned in the introduction, even after publishing the scripts I was still spending a significant amount of time trying to find the right script (or the right \u201ctool\u201d, just like my father with his toolbox). Depending on the script, it was sometimes faster to recreate it than to search for it – but the main issue with that was that some trick or detail I had learned while writing the script could be forgotten.<\/p>\n\n\n\n

For years, I found the script I needed just by memory (knowing which folder it was in), or with PowerShell<\/a> code like this (to find it by part of code that I vaguely remembered):<\/p>\n\n\n\n

gci -rec *.sql | sls \u2018dm_exec_query_stats\u2019<\/pre><\/div>\n\n\n\n
Then I did a visual check on the results to find the script. But it wasn’t always simple – and I didn\u2019t always find the correct one.<\/p>\n\n\n\n
But now we live in an AI-powered world, and if there’s one thing language models are great at, it’s text processing. And SQL scripts are just text – so this is a perfect use case. That’s why I created a web tool that lets you search for scripts from the Git repo. Just type what you need, and the tool will handle the magic. You can access it here<\/a>.<\/p>\n\n\n\n
Following is a video that shows how you can use the tool. The first four examples are in Portuguese, but later I demonstrate how the tool can handle other languages including Japanese, French, and even Hindi.<\/p>\n\n\n\n
\"The<\/figure>\n\n\n\n
<\/a><\/p>\n\n\n\n
In the rest of this post, I\u2019ll explain the code and services I used to build the tool, so by the end, you\u2019ll have a better understanding of how SQL Server can be used for AI – and how some AI models work beyond just generating text. Don\u2019t worry if you\u2019re not familiar with AI terms yet; I\u2019ll only assume you have some basic experience with programming logic and a bit of experience with SQL.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
The fast and easy way to write, format and debug SQL<\/h3>\n                
\n                                            SQL Prompt, now with AI capabilities, works alongside you to make your SQL coding fast, accurate and effortless.                                    <\/div>\n            <\/div>\n                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Divide and Conquer<\/h1>\n\n\n\n
Using AI to assist in search involves two main phases: indexing<\/a> the content and the search itself.<\/p>\n\n\n\n
To start smoothly, let me show you a diagram with all the technologies I used to build this project and how they relate to these two phases:<\/p>\n\n\n\n
\"The<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This diagram might look a bit intimidating at first glance but don’t worry, we\u2019ll break it down throughout the post. After reading, you can come back and see how many new things you\u2019ve learned about AI. You\u2019ll notice that the architecture is actually very simple and includes common elements from a traditional ETL (Extract, Transform, Load)<\/a> project – although I\u2019ve seen plenty of ETL pipelines out there that are far more complex and don\u2019t use any AI at all.<\/p>\n\n\n\n
The left side of the image, where you see the stick figure labeled Rodrigo<\/em>, represents the part that inserts data into SQL Server – in other words, the indexing phase. The yellow area on the right is the phase where we interact with the chatbot, starting from the stick figure labeled User,<\/em> which represents anyone in the world. A small part of this yellow area is also used during the indexing phase.<\/p>\n\n\n\n
What we\u2019re doing here is known as RAG: Retrieval-Augmented Generation<\/a>. That\u2019s just a fancy name for something simple: take data related to the user\u2019s question and provide it to the AI chatbot to improve the answer. But here\u2019s the real challenge: how do we find the relevant data for the question?<\/p>\n\n\n\n
To do that, we need to index the content so we can efficiently retrieve what’s relevant to the user’s text. In other words, we need to search by meaning, or do a semantic search<\/a>. For example, if the user searches for “performance problem,” we want to return scripts that check CPU usage, validate what\u2019s currently running, etc. We don\u2019t want to return a script that recreates constraints as it\u2019s not relevant in this case. <\/p>\n\n\n\n
So, once all the content is stored in a database, and a user sends a query, we go to that database, retrieve the most relevant entries, and use a Large Language Model (LLM)<\/a> to present and explain those results as if an expert were responding.<\/p>\n\n\n\n
Note: LLM stands for Large Language Model, the type of AI that generates text. It’s the core behind tools like ChatGPT, DeepSeek<\/a> or Google Gemini<\/a>. Maybe your experience with it so far has been through a nice web interface, but there are also APIs and services we can call programmatically. You\u2019ll see an example of that in this project: we\u2019ll use Google Gemini to generate the final response text.<\/p>\n\n\n\n
And thanks to the LLM, we can guide it to read the relevant scripts we\u2019ve found and present them in any language we want – for example, matching the user\u2019s original input language. Even if I wrote or commented my scripts in Brazilian Portuguese or English, AI helps make my experience accessible to the entire world, in their own native language.<\/p>\n\n\n\n
It\u2019s important to remember that in this project, we\u2019re not only using the type of AI that generates text (the LLM), but also two other types of AI models that don\u2019t generate text – they generate numbers (that can be used to do some other operations) based on text.<\/p>\n\n\n\n
Throughout the examples, you\u2019ll understand why the new vector support in SQL is so important and truly game-changing in the moment we\u2019re living in.<\/p>\n\n\n\n
In this article, I\u2019ll point out whenever the code is interacting or loading an AI model. So, let\u2019s dive into each of these phases separately to see how the code works to make all the magic happen!<\/p>\n\n\n\n
Phase 1: Indexing the Data<\/h2>\n\n\n\n
The first thing needed for a project like this is to index my scripts. But it’s not just taking the content of the file and throwing it into a database to do a LIKE or a Full-Text search. Remember that we need to support searching by meaning. If I search for the text performance problem<\/em>, then the search should find all the scripts that talk about requests, locks, waits, identifying CPU usage, etc.<\/p>\n\n\n\n
In the world of AI, there is a concept called embeddings. They are a numerical representation of text. The post I recommended earlier<\/a> provides a more complete introduction, but here\u2019s a quick summary:<\/p>\n\n\n
\n
    \n
  1. You need an AI model that can generate that embeddings.<\/li>\n\n\n\n
  2. You input a text into that AI model.<\/li>\n\n\n\n
  3. It outputs an array of numbers that represent the text and its meaning.<\/li>\n\n\n\n
  4. You can repeat that process for another text and compare this array with another using a simple math operation called Cosine Distance<\/em>. A result closer to 0 means that the two texts have similar meanings.<\/li>\n<\/ol>\n<\/div>\n\n\n
    Basically, I need to generate the embeddings of my scripts and store them in a database table. Since I\u2019m using GitHub, I chose to use GitHub Actions<\/a>, so whenever I update the repository, it automatically triggers a script that generates the embeddings.<\/a><\/p>\n\n\n\n
    GitHub Actions<\/strong><\/p>\n\n\n\n
    Think of GitHub Actions as a \u201ctrigger\u201d that runs when something happens in my repository. A YAML file called embed.yml<\/a> defines all of that for me. This is called workflow. To better control when this process starts, I configured an action that runs a PowerShell script whenever I push a tag with the prefix embed-*.<\/p>\n\n\n\n
    This gives me control over when I want to start the process and even lets me do a bit of version control for the indexing phase. For example, I can choose not to trigger the process if I\u2019ve only changed files that I don\u2019t consider important to index right now.<\/p>\n\n\n\n
    But if you don\u2019t know what a Git tag is, no problem! Just imagine this: whenever I make a change in my repo, something automatically runs a PowerShell script for me on GitHub\u2019s servers.<\/p>\n\n\n\n
    If you\u2019d like to learn more about GitHub Actions, check out this great series on Simple Talk by Kathi Kellenberger: Introduction to GitHub Actions Part 1: Getting Started – Simple Talk<\/a><\/p>\n\n\n\n
    The script responsible for this is called embed.ps1<\/a>. Yes, it\u2019s a PowerShell script. Let\u2019s now rebuild our initial diagram to show the components we\u2019ve covered so far:<\/p>\n\n\n\n
    \"A<\/figure>\n\n\n\n
    First AI Use: Generating Script Embeddings<\/h3>\n\n\n\n
    If you thought we would only use AI to generate text, here is the first surprise. AI goes far beyond generating text, and in our project, the first use is to generate the numerical representation of our scripts content, the famous embeddings. This will allow us to search for meaning later in SQL Server (thanks to the new AI support).<\/p>\n\n\n\n
    For each repository file, I will generate its embeddings using a Hugging Face Space that I created.<\/p>\n\n\n\n
    About Hugging Face <\/strong>\ud83e\udd17<\/strong> and Gradio<\/strong><\/p>\n\n\n\n
    Hugging Face<\/a> (yes, the same name as the emoji) is an online service where anyone can upload AI models and build demo applications that use those AI models. Hugging Face provides the infrastructure to host them, including hardware resources like GPUs to showcase projects like the one I\u2019m presenting to you. It\u2019s known as the \u201cGitHub of AI\u201d<\/em> and there you\u2019ll find lots of resources related to the AI world! So, it will be common to hear about Hugging Face (or just \ud83e\udd17) when talking about AI.<\/p>\n\n\n\n
    The Hugging Face Space<\/em> is where we create the demo application. Think of it like this: anyone can create a space<\/em> which simply contains Python code that runs a web interface, load AI models, invoke APIs, etc. Each space has its own unique URL for access.<\/p>\n\n\n\n
    There\u2019s a cool Python library developed specifically for AI\/Machine Learning demos: Gradio<\/a>. Thanks to that library, it\u2019s super easy to develop complex UI interfaces like chats, tabs, etc. And because it\u2019s made for Machine Learning, it includes lots of components from the AI world. So, it\u2019s common to see many spaces on Hugging Face using Gradio, which handles all the HTML, JavaScript, and APIs. I don\u2019t need to worry about that – I can just focus on the code that does solves my problem!<\/p>\n\n\n\n
    The tool you see in the video at the start of this article is the Space I created to showcase this project, and it uses that library, Gradio, to provide the UI interface and API. We\u2019ll look at the code soon.<\/p>\n\n\n\n
    If you look at the embed.ps1 file, you will see that I use a function called GetEmbeddings. This function is defined in the file util.ps1<\/a>. It uses a PowerShell module the I created called PowershAI<\/a>, which has one of the features I like the most: I can invoke the Gradio API as if it were a PowerShell function. Thanks to that, I can integrate the AI services I exposed via Python directly into PowerShell, and, in this case, the embed function is available to me from that script:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    This part of the code is straightforward:<\/p>\n\n\n
    \n