Copilot in Azure SQL Database. The end of DBAs?
![Copilot in Azure SQL. The end of DBAs?](https://aleson-itc.com/wp-content/uploads/2024/07/Copilot_in_Azure_SQL._The_end_of_DBAs.png)
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.
![Microsoft Copilot in Azure SQL Database](https://aleson-itc.com/wp-content/uploads/2024/06/image-15.png)
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:
![Copilot en Azure SQL DataBase](https://aleson-itc.com/wp-content/uploads/2024/06/image-16.png)
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.
![Copilot en Azure SQL DataBase](https://aleson-itc.com/wp-content/uploads/2024/06/image-17.png)
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.
![Azure SQL Database - Query Editor](https://aleson-itc.com/wp-content/uploads/2024/06/image-18.png)
Once we have logged in and clicked on New Query, we will see the button with text Launch inline copilot
![Use Copilot in Azure SQL Database](https://aleson-itc.com/wp-content/uploads/2024/06/image-19.png)
When you click on it, new options appear on which we are going to expand the information a little:
![Chat with Copilot in Azure SQL Database](https://aleson-itc.com/wp-content/uploads/2024/06/image-21.png)
- 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.
- A message warning that the code may contain errors and that it currently only allows the creation of SELECT.
- If you want Copilot to search only in some tables, you can filter it here.
- 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:
![List of tables in Azure SQL DataBase](https://aleson-itc.com/wp-content/uploads/2024/06/image-22.png)
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.
![Chatbot of Microsoft Copilot in Azure Database](https://aleson-itc.com/wp-content/uploads/2024/06/image-24.png)
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.
![Query Result for Azure DB](https://aleson-itc.com/wp-content/uploads/2024/06/image-27.png)
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;
![Query Result for Azure DB](https://aleson-itc.com/wp-content/uploads/2024/06/image-25-1024x344.png)
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;
![Query Execution in Azure SQL Database](https://aleson-itc.com/wp-content/uploads/2024/06/image-26-1024x273.png)
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.
![](https://aleson-itc.com/wp-content/uploads/2024/04/DSC08759-2_Cuadrada_Mini.jpg)
Consultor Senior SQL Server & BI con 9 años de experiencia, MCSE Data Platform con conocimientos de toda la herramienta y enfocado principalmente a la detección y mejora de problemas de rendimiento en Base de Datos. En mi tiempo libre soy un gran aficionado a la fotografía de estilo urbano y de lugares abandonados.