GitHub Copilot to Create Indexes and Tune Queries in SQL Server

Problem

In the previous article, we saw a few examples of what Copilot can do around the development of user-defined functions and stored procedures. Let's continue exploring the tool to see what else it can do.

Solution

In this article, I will test new use cases for Copilot related to the creation of indexes, query tuning, comparison of queries to determine which will perform better, and more.

Initial Considerations

This article will assume that your environment is set up with everything you need to follow along with what was covered in articles part1 and part2.
The content will be demonstrated using a Windows environment, but it's assumed you can follow along using a Linux or MacOS environment.

Quick Use Cases

Scenario 1

While trying to create a new table called dbo.posts from Copilot to establish a relationship between the users and the posts tables, I got the syntax wrong for the foreign key constraint. I asked Copilot to help me address this mistake.

Here's the comment that I used:

--From the above query, show me how to correctly create a foreign key constraint on the user_id column of the posts table to the id column of the users table.

That's quite handy. Next, I tried to get an alternative to that response, and this is what I got:

Here's the comment that I used:

--Show me how to correctly create a foreign key reference on the user_id column of the posts table to the id column of the users table.

This is also a valid approach and saved me a trip to Google!

*Even if that's a basic T-SQL syntax, I tend not to memorize absolutely everything, but that's just me.

Scenario 2

Now that I have the users and posts tables, I want Copilot to provide the code for the following query:

Note: You can use the multi-comment syntax for your requests.

/*
Help me build the following query:
Get the number of posts for each user along with their username, ordered by the number of posts in descending order.
*/

I know this is a somewhat trivial case because the users and posts thing is a classic example, but regardless of that, Copilot gave me the query I'm looking for, which is fantastic.

Scenario 3

With the above query, I would like to know if it could benefit from an index to boost its performance.

--From the above query, suggest me indexes to improve the performance of the query

II guess Copilot followed a "literal scan approach," meaning that it suggested an index for everything it found within the query without knowing that the first index isn't necessary because it is already the Primary Key of the users table.

Remember that you shouldn't take everything Copilot throws at you for granted. You can grab the input, analyze it, and determine if it suits your use case. It is assumed that these tools will keep improving with time, but our critical judgment is still key in our workflow.

Scenario 4

I want Copilot to compare two queries and tell me which one is better and why.

Here's the comment I used:

--Compare Query 1 and Query 2 and tell me which one is better and why?

I guess Copilot is still not ready to perform a comprehensive analysis like this. As you can see, it told me that each query was better than the other for its own particular reasons, but it couldn't tell me which one was the definitive winner.

Scenario 5

I want Copilot to look at a different query to see if it can make it more readable.

The following query is essentially the same as "Query 1" from the previous scenario, but I have put it in a single line. (It is long enough to not fit in the screenshot).

--Format my query to be more readable

Bingo! I got the desired formatted version. I didn't get it in one shot; instead, it suggested it line by line.

Scenario 6

I want Copilot to look at a sample query and see if it can make it SARGABLE.

For this query, I want to see the titles of the posts created in 2023.

--Make my query SARGABLE

That is good enough for me!

Scenario 7

Here I will ask Copilot for random things to see what it does:

--I need a query to know if my indexes are fragmented or not

Interesting. A solid round-trip to Google is saved for a DBA needing this information.

--Do temp tables perform better than table variables?

Interestingly, it gave the output in a "ChatGPT-like" fashion.

--How can I find poor performing queries in SQL Server?

This one I was not expecting. It simply provided a couple of links pointing to stackoverflow.com. However, after opening them, I noticed that they have nothing to do with the topic:

The links don't seem to exist, and stackoverflow forces a redirect.

Let's think about this: Opening links randomly provided by Copilot might not be a good idea. If the data Copilot uses is fed from public/open data sources, what could prevent it from being a fake URL for malicious purposes? I'm not implying that this will be 100% of the cases, but it's something to keep in mind and to always stay sharp.

With that, I'd like to wrap up this series. I hope you were able to take a thing or two from it.

Next Steps

Judging by what we saw during all the demos, there's still a long way to go for these kinds of tools. Don't get me wrong; in their current state, they have already accomplished a lot that was unthinkable 20 years ago.
I believe these AI-powered tools are here to stay for all the cool/positive reasons. It will be a matter of when we will jump on the bandwagon. After all, technology will never stop advancing, and it's up to us to catch up as best as we can.

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

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

View all my tips

Article Last Updated: 2023-08-18

GitHub Copilot Examples to Create SQL Server Indexes and Tune Queries