SQLServerCentral Article

Leveraging AI Tools for SQL Queries

,

Some of the many benefits that AI offers include task automation, increased productivity, and increased efficiency. As an SQL Server developer, you may be writing your queries manually or using Query Editor tools available in SQL Server Management Studio. Writing queries manually can potentially lead to human errors, may require more time to come up with the right queries, and can result in tedious and cumbersome efforts. All this is assuming that you already know SQL well. But what if there were tools that could help even a novice SQL Server developer craft intricate queries, with increased efficiency and productivity? This is where AI comes in.

As of today, AI and Generative AI are taking over nearly every domain, from content writing to music, image, and video generation to medicine and finance. It's not hard then to imagine the use of AI and Generative AI in the technology world itself. Today, you have AI tools that can build a fully functional Website in ten minutes, write Java code or Python code, and so on. This article makes use of a publicly available schema from Microsoft (free to download) and does not include within its scope working with custom schemas.

We start with one of OpenAI's biggest accomplishments, the ChatGPT chatbot. ChatGPT offers both free and paid subscriptions. There are 3 pricing plans - free, plus, and enterprise. Based on your requirements and budget, you can choose one of these.

Once you've created your account, you specify a "prompt" in ChatGPT to draft a query. The clearer your prompt, the better the outcome will be. Here's a helpful link on How to Prompt AI Chatbots

Sample Prompt: You are an SQL Server database developer. Assume you are working with the AdventureWorks2022 sample database. Write the SQL code to create a view named vwPersonDetails with columns Title FirstName, MiddleName, LastName, and JobTitle from the Person and Employee tables of HumanResources schema.

Outcome:

USE AdventureWorks2022;
CREATE VIEW HumanResources.vwPersonDetails AS
SELECT
    p.Title,
    p.FirstName,
    p.MiddleName,
    p.LastName,
    e.JobTitle
FROM
    HumanResources.Person AS p
INNER JOIN
    HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID;

The below Figure shows this:

Look at how easy this was. You did not have to specify the aliases, the JOIN condition or even the common column to link the two tables. ChatGPT inferred all these based on its training data and produced the above results.

Not just that, ChatGPT also gives you a detailed explanation of the SQL code as you can see here.

So far so good, but how do you know if this generated SQL code is valid or not?

To verify whether this code is actually correct or not, you could paste the same prompt in another chatbot, namely, Bard by Google, and see what it gives you. As you can observe, it has churned out an SQL query, that is identical to the one given by ChatGPT.

The added benefit of Google Bard is that it also gives you alternative solutions if you are not happy with the first. Click View other drafts dropdown to see more draft results for the same prompt.

Here, you can see three different draft outcomes for the same prompt.

Similar to ChatGPT, Google Bard also gives you a detailed explanation of the SQL code, however its explanation is far more elaborate as compared to that of ChatGPT's.

 

At the bottom of the results, there are icons denoting what you can do with the outcome.

You can click Like or Dislike, modify the response, share and export it to Gmail or Google Docs, double check the results with Google search engine, and so on.

Clicking double check response (G icon) will provide further resources in the form of links that you can visit the cross-check the response.

What about pricing? Google Bard is entirely free, with no limit on the number of questions users can ask. This is because Google earns revenue from its ads. Both Bard and ChatGPT give you options to edit the original prompt and generate a fresh outcome:

ChatGPT:

If OpenAI and Google have launched ChatGPT and Bard, Microsoft's answer to these is Bing Chat. Bing Chat is the AI chatbot by Microsoft.

Launch Bing.com on any browser such as Chrome, Firefox or Microsoft Edge. Once the page is loaded, click Chat on the top left corner. Bing Chat appears on the screen.

Click inside the New topic text box and you're ready to go!

Let's try creating a stored procedure using Bing Chat.

Prompt: You are an SQL Server database developer. Assume you are working with the AdventureWorks2022 sample database. Write the SQL code to create a stored procedure named uspGetTotalSales with input parameter @territory to accept the name of a territory and output parameter @sum to display the sum of sales year to date in that territory.

Here you can see how Bing Chat works. It begins by searching for the same database, searching for SQL Server stored procedure, and then, generates the outcome.

Bing Chat also includes explanations for the result, just like ChatGPT and Bard do. It also provides reference links at the bottom of the chat window in case you need to check more. Look out for the Learn more bolded heading beside which there may be two or more links.

Bing Chat has two pricing plans – free and enterprise. The free plan typically offers 20 chats per session and 200 total chats per day *(subject to change).

Bing Chat Enterprise currently is in preview and is included at no additional cost in Microsoft 365 E3, E5, Business Standard, and Business Premium. In the future, it will be available as a standalone offering for $5 per user, per month and also, Bing Chat Enterprise will be accessible from Windows Copilot.

Besides these three major chatbots, there are also other AI tools that can help generate AI queries. Let's explore a few.

Vanna

Vanna.ai is an open-source Python-based AI SQL agent that helps you draft complex SQL queries quickly. Familiarity with Python and Jupyter Notebooks are expected. To use Vanna, you first install it and then, import it into a Jupyter Notebook (see here for a demo).

Then, you "train" the AI agent which means you feed it your schema information. This can be done using either a manual approach or by using an automatic one. Both approaches are elaborated here at their official site. Training the AI agent ensures high accuracy for complex datasets. The tool supports a wide range of databases and you just have to provide a vn.run_sql function that takes in a SQL query and returns a Pandas DataFrame. This article can help you get started with Vanna.

It has a freemium pricing plan.

Link: https://vanna.ai/

Text2SQL.AI

text2sql.ai is another AI tool that generates SQL queries for you based on your  plain text instructions. It offers a free trial. Naturally, one may wonder how it stacks up against ChatGPT. Thankfully, someone else has already done the work and produced a comparison - ChatGPT Prompt Generator vs Text2sql

Link: https://www.text2sql.ai/

AI Helper Bot

AI Helper Bot, also called SQL AI, is another AI tool that helps you create SQL queries instantly, without any prior SQL knowledge, using everyday plain English. Users can also save SQL snippets for later use and connect to databases for direct data insights. It has a Freemium plan.

Link: https://www.sqlai.ai/

AI2sql

AI2SQL is an AI tool available through the web as well as a Chrome extension and enables users to build error-free SQL queries without requiring prior SQL knowledge. With this tool, users can build queries easily and efficiently. It offers a free trial. The tool includes features like SQL query generator, SQL syntax checker, and more.

Link: https://www.ai2sql.io/

Summary

AI is here to stay and makes our everyday lives. As someone who works frequently with databases, you can improve your efficiency, make better use of your time and maybe even cut costs with AI tools. This article described a few popular AI tools for working with SQL queries. As the AI industry continually evolves, we can expect to see more and more such tools. For now, we familiarize ourselves with these available tools and make the most of them.

Rate

4.4 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4.4 (5)

You rated this post out of 5. Change rating