Exploring AI Tools for Writing SQL Queries - Are they reliable?

By: Rajendra Gupta   |   Updated: 2024-03-22   |   Comments (2)   |   Related: More > Artificial Intelligence

Problem

Organizations are using AI technologies to improve efficiency, automation, and saving time. AI is making its place in every domain, such as generating content, visuals (audio/video), music, and analysis tools. How can you use these AI technologies for writing SQL code? Is it beneficial to be dependent on these tools for your development work? Let's explore thoughts in this tip.

Solution

Artificial Intelligence (AI) is multiplying, revolutionizing the world with its implementation and usage. According to Forbes, the AI market is projected to reach $1811.8 billion by 2030 compared to $136.55 billion in 2022.

Image Source Forbes

Developers spend time manually writing queries using query editor tools such as SQL Server Management Studio and Azure Data Studio. These tools have functionalities to help developers write queries. A few useful features include:

• SQL Code editor with IntelliSense features such as keyword completion, code navigation, and code snippets.
• Smart Code snippets can help you give syntax for creating databases, tables, stored procedures, functions, views, users, and logins.

Role of ChatGPT and Bard in Writing Queries for Developers

AI has moved a step ahead. It can write the code on our behalf. We will cover two popular AI tools - OpenAI's ChatGPT and Google's Bard. A short background of these AI tools:

• ChatGPT receives around 1.7 billion website hits monthly. It is expected to have a revenue of $1 billion in 2024. Source: Demandsage.com
• Bard received 142 million monthly visitors at the beginning of 2024.

ChatGPT, Bard, or any other AI tool cannot replace developers since it does not know the database structure, schema, indexing, and workload requirements. Suppose you want to use ChatGPT and provide it with the following instructions:

ChatGPT worked on the requirement and gave me the following query.

However, if you try to run this query on the WideWorldImporters database, you get the following errors:

Google Bard gives a different query for the same requirement but still has an error:

Why do we get these errors? Well! This is due to the limitation of AI tools. However, before we look into the limitations or disadvantages, let's look at a few use cases or advantages of using artificial intelligence tools.

Advantages of Artificial Intelligence Tools

Learning Tools

AI tools are great for beginners to learn a new topic or technology. It can give concise information with example codes, syntax, and query structures. It is a very interactive tool that allows you to ask questions and provides real-time instructions in plain English.

Learning Different Ways of Writing Code

AI tools can help you write SQL queries in different ways using other solutions. It can also help to debug the SQL queries with possible solutions or errors.

Speed Up Coding Process

Understanding the code snippets can help developers write code faster. These tools also explain the code line by line based on the context.

Works for Multiple Programming Languages

As a developer, you might be good at a specific language. For example, suppose you are good at T-SQL, i.e., Microsoft SQL Server query language. However, you might need to learn or write code in different languages, such as MySQL, per organization requirements. The AI tools can help you understand the query differences between these multiple programming languages and make switching or working with various languages easy. You can start a session like the one below.

I have the following code for creating a table in SQL Server:

create table testable
 ( id int, 
   name varchar(20),
   Hiringdata datetime 
)

Can you help me write this in PostgreSQL?

Help with Query Optimization

AI tools can suggest the required indexes and explain why. It can offer best practices or alternatives for writing the query for optimized behavior. For example, an AI tool can suggest an index for a specific query.

Which indexes should I create to optimize the query below?

SELECT 
  c.CustomerName AS CustomerName, 
  cc.CustomerCategoryName AS CustomerCategoryName, 
  p.PhoneNumber AS PhoneNumber, 
  ci.CityName AS CityName, 
  dm.DeliveryLocation AS DeliveryLocation, 
  p.FullName AS FullName 
FROM 
  Sales.Customers AS c 
  JOIN Sales.CustomerCategories AS cc ON c.CustomerCategoryID = cc.CustomerCategoryID 
  JOIN Application.People AS p ON c.PrimaryContactPersonID = p.PersonID 
  JOIN Sales.BuyingGroups AS bg ON c.BuyingGroupID = bg.BuyingGroupID 
  JOIN [Application].DeliveryMethods AS dm ON c.DeliveryMethodID = dm.DeliveryMethodID 
  JOIN [Application].Cities AS ci ON c.DeliveryCityID = ci.CityID;

Why We Should Not Rely on AI Tools for Development

AI tools are suitable for help, but these limitations and challenges should make you think before using these tools.

Absence of Comprehensive Understanding

AI tools can help you understand coding languages. However, they cannot write the code on the developer's behalf because it lacks context information about the project, application, business requirements, constraints, or domain-specific knowledge. Developers understand the organization's requirements, which is critical for effective code development.

Experience

Experience makes a developer effective. Code can work differently in different environments. For example, a stored procedure might work well in a small-scale application, but needs to be tuned differently for a large-scale setup processing large chunks of data. Developers with good experience in their technology can understand the workload and write or adjust their queries to meet scaling demands.

Limited Knowledge

Every AI model has a cut-off date for its knowledge model. For example, if you ask ChatGPT (free version) this question: When was ChatGPT last trained for its knowledge?

It gives the following answer:

This article was written in January 2024. As you can see, ChatGPT's knowledge was updated in January 2022. Therefore, it will not have any information after the changes since January 2022.

For example, if I ask: What is the latest in SQL Server 2022?

As per the ChatGPT AI tool, SQL Server 2022 has not yet been released.

Security Concerns

Developers must avoid sharing the organization's data, financial details, PII information, private or confidential data, or intellectual property with AI tools to avoid data breaches or unauthorized access to sensitive information. Data breaches or privacy leaks are severe concerns for any organization in the digital world.

Misinterpretation of Information

AI tools work as per user inputs. Sometimes, you might give input that is correct in your understanding. AI generates the code without syntax or errors, but it might not be a desired outcome as AI does not have human intelligence.

Experience Can Never Be Replaced

If you look at websites like MSSQLTips.com, they provide content from experienced professionals who have real-world experience working with big clients and environments. These articles are not just rudimentary; they give end-to-end solutions with possible errors. Therefore, exploring these blogs for reliable, accurate, and trustworthy resources is required.

Next Steps

• Explore MSSQLTips for database-related solutions.
• Be careful while using AI-based tools. Use them for learning, and then write your code with your application requirements. Do not mindlessly follow the AI tools without verifying their contents.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips