Category: Copilot

by Fran Lens 2024-07-04

Copilot in Azure SQL Database. The end of DBAs?

Welcome to a new post in the Aleson ITC blog. If you are here is because you know that AI has become a key player in the future of all companies, and if yours uses SQL Server will also be able to enjoy all the benefits it brings. Today we will talk about Microsoft Copilot in Azure SQL Database.

What is Microsoft Copilot for Azure SQL Database?

Microsoft Copilot for Azure SQL Database is a tool that integrates AI capabilities with Azure SQL Database to improve the management and operation of applications using SQL.

It is aimed at database administrators (DBAs) and developers, helping them in database management, troubleshooting and writing more efficient SQL queries.

What is included in the preview version

It should be noted that at the time of writing this post, Copilot for Azure SQL DB is in preview version, so the set of features available is small compared to those expected in the future. It currently includes two main experiences:

• Microsoft Copilot integration in Azure: Adds support, database management and troubleshooting capabilities.
• Natural language to SQL query: Translating natural language queries to SQL in the Azure Portal query editor.

Let’s dig a little deeper into each of these experiences, for this I have created an Azure SQL Database with the example data from AdventureWorksLT.

Microsoft Copilot integration in Azure

Access to Copilot in Azure is done from the portal, on the button available on the top bar.

It is recommended that before opening Copilot, you have gone to the resource of your Azure SQL Database where you want to test it, otherwise, and if you have several, the first thing it will do when you write a prompt, is to ask you on which Azure SQL DB you want to act.

The integration is prepared to answer multiple areas within SQL Server, such as configuration, connections, slow queries, indexes, query store, statistics, performance, backups… In this link you can find many sample prompts. Some of them are:

• Help me find fragmented indexes.
• Why is my database slow?
• Check top blocking sessions.
• When was the most recent backup of my database created?

For example, I am going to ask about index fragmentation problems:

We see that at the moment it gives us information about the indexes that should be defragmented. Now we are going to ask for the script to solve it.

You can see how it gives the information perfectly to solve the problem.

Natural language to SQL query

Now that we have seen how the integration with Azure Copilot works, let’s test the natural language translation to TSQL that is available in the query editor of the Azure portal.

The first thing to do is to go to our Azure SQL Database and then to the Query Editor section.

Once we have logged in and clicked on New Query, we will see the button with text Launch inline copilot

When you click on it, new options appear on which we are going to expand the information a little:

1. The text box where we will enter in natural language the query we need. At the time of writing this post only English text is allowed.
2. A message warning that the code may contain errors and that it currently only allows the creation of SELECT.
3. If you want Copilot to search only in some tables, you can filter it here.
4. It shows that it is a Public Preview feature.

Now let’s test how the translation works. This is the list of tables and views:

Prompt Test

The first test prompt is:

Tell me the name and surname of all the customers whose company contains the word Bike, in alphabetical order

The response shows the Prompt, an explanation and the generated query.

Analyzing the query, it seems correct, it points to the Customers table, the where filter is correct, the select is correct and the order by is correct.

SELECT FirstName, LastName
FROM [SalesLT].[Customer]
WHERE CompanyName LIKE '%Bike%'
ORDER BY LastName, FirstName;

And if we proceed to execute it, the data it returns is the correct one, sorted by LastName.

Let’s complicate it a bit by adding new information to the second prompt:

Tell me the name, surname, company name, the number of orders they have placed, and the total amount of the order  of all the customers whose company contains the word Bike, in alphabetical order. 

The generated query complies perfectly with everything requested:

SELECT c.FirstName AS [Name], 
       c.LastName AS [Surname], 
       c.CompanyName, 
       COUNT(soh.SalesOrderID) AS [Number of Orders], 
       SUM(sod.LineTotal) AS [Total Amount of Orders]
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
JOIN SalesLT.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
WHERE c.CompanyName LIKE '%Bike%'
GROUP BY c.FirstName, c.LastName, c.CompanyName
ORDER BY c.LastName, c.FirstName ASC;

Finally, let’s complicate this third prompt a bit more.

Tell me the name, surname, company name, the number of orders they have placed, and the total amount of the order  of all the customers whose company contains the word Bike, in alphabetical order. Also, add the number of characters the company name has, and whether that number is odd or even.

Again, the query returned is correct:

SELECT 
    c.FirstName AS [Name],
    c.LastName AS [Surname],
    c.CompanyName,
    COUNT(soh.SalesOrderID) AS [Number of Orders],
    SUM(sod.LineTotal) AS [Total Order Amount],
    LEN(c.CompanyName) AS [Company Name Length],
    CASE WHEN LEN(c.CompanyName) % 2 = 0 THEN 'Even' ELSE 'Odd' END AS [Odd/Even]
FROM 
    SalesLT.Customer AS c
    INNER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
    INNER JOIN SalesLT.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
WHERE 
    c.CompanyName LIKE '%Bike%'
GROUP BY 
    c.FirstName,
    c.LastName,
    c.CompanyName
ORDER BY 
    c.CompanyName ASC;

It can be said that Copilot has passed the test with very good marks, the trick is to be clear with the prompt and try to give as much detail as necessary.

Information of interest

Finally, I leave here some data that I have been collecting and that I think will be helpful.

• Copilot in Azure SQL Database will not use data from your databases to train Open AI models.
• Copilot is currently only available for Azure SQL Database, but is expected to be available soon for the rest of the SQL Server portfolio, starting with the Managed Instance and continuing with the On Premise versions.
• While in Preview, Copilot will be free of charge. There is no information on future pricing.

My opinion

Copilot for SQL is going to become an indispensable tool on a daily basis, and with this evolution that we are having, in a few months/years the job of programmer and DBA is going to undergo a great change reducing a lot the time needed to perform the tasks.

I hope you liked the post, I invite you to follow me on LinkedIn to keep abreast of all the news in the area of Data and Analytics.

Are you having problems with your database? Are you considering adopting Copilot in your company? We are experts in Data and we can help you.