GitHub Copilot for Azure Data Studio to Boost Database Development Productivity - Part 1

By:   |   Updated: 2023-07-24   |   Comments (1)   |   Related: 1 | 2 | 3 | More > Artificial Intelligence


Problem

At this point, you probably have been pulled into the artificial intelligence (AI) tornado that's conquering the headlines. Companies are throwing loads of money into the AI gamble, hoping for a brighter and prosperous future (whatever that means, right?). For us SQL Server database professionals, what does that mean? Will we be displaced by some fancy piece of automated software that can do everything we can do? I guess it's too early to carve that in stone, but one thing is certain: the popularity of AI-enabled tools is growing by the second. So why don't we approach this fact with an open mind to see if we can take advantage of these tools to enhance our workflow?

Solution

In this article, we will look at GitHub Copilot for Azure Data Studio to see if it is an AI-enabled tool that can boost our database development productivity.

Initial Considerations

  • This article assumes that Azure Data Studio 1.44 or later is installed.
  • The content will be demonstrated using a Windows environment, but it is assumed one can follow along using a Linux or MacOS environment.
  • The steps include setting up a GitHub Copilot account (which can be skipped if you are inclined).

GitHub Copilot

GitHub Copilot is not something exclusive to Azure Data Studio. As per GitHub's official website, it is an extension that can be integrated into several code editors, such as "Visual Studio, Neovim, VS Code and Jetbrains IDEs," to name a few. It uses the OpenAI Codex to make code suggestions on the fly.

Its main premise is to "spend less time creating repetitive code patterns, and more time on building software."

Is it free?

For individuals, there's a 30-day free trial (precisely what is used in this article), starting at $10 per month or $100 per year after the trial term. There's also a business tier, starting at $19 per user per month, which would be a free perk if your organization is enrolled within this tier.

Copilot screenshot

Let's move on to the setup of the free trial (feel free to skip this section if you already have an individual or business account enrolled).

Github Copilot free trial
Github Copilot free trial-amount

*Remember to cancel before the 30-day period expires to avoid surprises in your bank statement.

Github Copilot free trial-billing info

In this last section, choose your preference.

Github Copilot free trial-preferences

And with that, your account is ready. Let's start using Copilot!

Github Copilot free trial-ready

GitHub Copilot Extension for Azure Data Studio

As stated previously, ensure Azure Data Studio version 1.44 or newer is installed to continue.

When Data Studio loads, click the Extensions icon and type GitHub Copilot in the search bar, as seen below.

Install

Once the extension is installed, a message asks you to sign in to GitHub to use Copilot.

Installed and sign in
Azure Data Studio alert
Signed in using GitHub

Back in Data Studio, how can I confirm that everything is set up correctly and that the Copilot extension is running? Click the tiny Copilot icon at the bottom right corner of the window, and a popup will appear asking you if you'd like to disable Copilot, as seen below.

Disable GitHub

To start testing the extension, let's open a connection to a SQL Server instance (local or in a cloud provider) or an Azure SQL Database and open a new query tab.

As you can see, I currently don't have a single table within my test database, so the first natural thing is to start typing "CREATE TABLE". Immediately, a chunk of code appears as a suggestion that either disappears if you move the cursor with your keyboard or autocompletes if you hit Tab.

Create table

If you have a keen eye, you will immediately recognize that the suggested code is to create a MySQL table, not a SQL Server table, which doesn't seem quite right. If you hover your cursor over the "user" keyword, you will see a small tooltip with key combinations to perform actions. For this demonstration, I'll choose "Open Completions Panel".

Open Completions Panel option

A side tab will open, and you will see a bunch of code snippets with different ways to create your table. You will even see code snippets written in PHP.

Code snippet options
Code snippet options

This is why we see code for a MySQL table. The system probably feeds from open data sources and data it collects from user input (remember the screenshot I showed above where you could allow Copilot to improve the product by collecting your code snippets?). There's nothing wrong with the extension because I have the wrong target RDBMS; it may be how it currently works.

Let's try to trick Copilot into giving us suggestions related to the T-SQL code. For that, instead of writing "CREATE TABLE user," let's try "CREATE TABLE dbo.user":

CREATE TABLE dbo.user
CREATE TABLE dbo.user

Interestingly, it suggested T-SQL code this time, so the tiny trick worked.

Another Copilot feature I've seen is the suggestion of code based on instructions specified in the form of comments (yes, you read that one right, comments). To try this one out, let's write a simple comment that suggests creating a new table to store user data. And let's specify a few fields to see what we get.

Suggestion based on comments

As soon as you hit enter, you can see the suggested code. However, for this case, it still suggests non-T-SQL because SERIAL is not a valid type in SQL Server. It also suggests fields I didn't state in my comment (not a deal-breaker, I'm just pointing it out).

Let's try again to overcome this inconvenience with a trick. This time, let's be specific in the comment stating that we want a SQL Server table to see what Copilot suggests this time:

Specify a SQL Server table

Interesting, this time we're getting full T-SQL syntax, and precisely with the fields I want!

I will stop with the article right here and will continue expanding this interesting tool in subsequent ones, so stay tuned!

Next Steps
  • This was pretty much just the setup of the account and the tool within Azure Data Studio, so it would be unfair to draw definitive conclusions from the couple of basic samples we saw. Regardless of that, there are a couple of things we were able to observe:
    • Copilot doesn't seem to be fully aware that I'm using Azure Data Studio to connect to a SQL Server instance so that it can offer me accurate code based on my environment.
    • The data that Copilot uses to provide code suggestions seem to be fed from public data sources or by individuals who explicitly agree to share the data while using the tool.
  • It will be very interesting to see how a tool like this evolves and what features it will provide to the end users to boost productivity.
  • As with any new technology, I think it is too early to bet all your chips. Don't get me wrong, I think it has all the potential in the world, but I get the impression that it needs to go through a refinement process, and who knows, perhaps I change my mind along the way as I continue to explore the tool even deeper.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Alejandro Cobar 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-07-24

Comments For This Article




Wednesday, August 2, 2023 - 4:55:12 PM - Vishal Back To Top (91453)
Tried your example this seems useful already. reminds me of SQLprompt