Congratulations on starting your journey as a database developer in a new organization! As you embark on this exciting chapter of your career, you'll encounter various challenges and opportunities to enhance your SQL development skills. One tool that can revolutionize your workflow is GitHub Copilot. This is the first blog of our series where we will explore how GitHub Copilot can assist you in your SQL development tasks, providing intelligent code suggestions and speeding up your learning curve.
What is GitHub Copilot?
GitHub Copilot is an AI-powered code completion tool developed by GitHub and powered by OpenAI. It leverages machine learning models trained on vast amounts of code to generate context-aware code suggestions directly within your integrated development environment (IDE). You can use GitHub Copilot as an extension in Azure Data Studio, SQL Server Data Tools (SSDT) and Visual Studio Code.
In our previous blog on Copilot, we talked about a plethora of uses, here we would take you through a specific journey where the same prompt given to Copilot would generate different output based on the context that is given to Copilot.
Copilot can take comments in natural language, the same we are used to writing before our code as a best practice, to give a context about our code. This comment, which serves as a token to Copilot goes through checks to identify PII (Personally Identifiable Information) and Toxicity (any hate speech or unwanted content) before allowed content is fed into the AI model. The output of the AI model then goes through Duplicate detection and the top result is presented as a few lines of code. Once this output is generated, and the user accepts or rejects it, Copilot forgets the token and the information via which it has reached that output.
In the example below, Copilot generates the code based on a comment that is posted.
Once you press the tab key, the code is drafted in the editor.
This process works perfectly fine, and Copilot can generate the right queries if they are basic and simple. When the prompt for a more complex question is given, Copilot generates a query that is referencing columns that don’t exist on the database.
The reason why Copilot cannot return this query right is because it lacks the context of the tables and columns present in the database. It tries to give the best guess but that is not enough. To make the same prompt works, we need to provide context of the objects that it is expected to know about, before returning the output.
The “Brain Dump” Approach
In this approach, you give context to Copilot to be able to return better results for the prompts that are given. Expanding on the previous example, you would want to provide context on the table object Sales.OrderLines and Sales.Orders. To do that you can right-click on the tables to get the Script for Create. You then copy the objects and paste them at the beginning of the query. This is done for all tables that you might be using in the query.
Once all the objects are there in the query, if you run the same prompt, is what you are expected to get:
If you have reached this far and have enjoyed your experience with GitHub Copilot, you can take it one step further by reading the second blog in the series. In Blog 2, we talk about some more complex queries and different scenarios. We deep dive into query optimization techniques using GitHub Copilot.
Get started with Copilot
You can get started today with GitHub Copilot in Azure Data Studio by installing the latest Azure Data Studio release and installing GitHub Copilot from the extension marketplace. More information on GitHub Copilot is available in the documentation for Azure Data Studio and GitHub Copilot.