GitHub Copilot Examples to Create SQL Server Indexes and Tune Queries
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.
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.
- 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
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:
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:
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.
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.
With the above query, I would like to know if it could benefit from an index to boost its performance.
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.
I want Copilot to compare two queries and tell me which one is better and why.
Here's the comment I used:
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.
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).
Bingo! I got the desired formatted version. I didn't get it in one shot; instead, it suggested it line by line.
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.
That is good enough for me!
Here I will ask Copilot for random things to see what it does:
Interesting. A solid round-trip to Google is saved for a DBA needing this information.
Interestingly, it gave the output in a "ChatGPT-like" fashion.
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.
- 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
View all my tips
Article Last Updated: 2023-08-18