Introduction

In this article, we will see how to work with GitHub Copilot. GitHub copilot is an interesting plug-in that generates code using AI. The technology is from Microsoft and OpenAI. We will show how to install Copilot, and how to generate SQL Server code (T-SQL) using Copilot.

First, we will answer some FAQs about Copilot and then, we will start playing with it.

What is GitHub Copilot?

The copilot is like an assistant in writing code. It will help you to enhance the code, generate code, answer questions, and translate code between different languages.

What programming languages does it support?

The most popular languages like Python, JS, Ruby, C++, Java, c#, PHP, Swift, SQL, and other languages. In this example, we will work with T-SQL.

Who is the owner of GitHub Copilot?

Microsoft and OpenAI. Microsoft got GitHub in 2018. Copilot is powered by OpenAI (the ChatGPT creator).

Which code editors can be integrated with GitHub copilot?

1. First, Visual Studio Code (we will use this one in this article).
2. Secondly, Visual Studio.
3. Thirdly Neovim.
4. Finally, JetBrains. It is possible that other editors will be available in the future.

Now, it is time to start. Less talk, more action.

Requirements

1. First, make sure to have Visual Studio Code installed.
2. Secondly, the SQL Server installed
3. Thirdly, I will have the Adventureworks2019 database.
4. Finally, a GitHub account is ready.

Get GitHub Copilot

First, go to the GitHub Copilot page. Secondly, press the Get Copilot button.

Thirdly, go to Billing and plans

Next set up your plan. I will use a trial option for 1 month to test it. After that, there is an option to pay 10 dollars per month. For more information about prices, go to the Copilot page. Even when you are on a free trial, you will need to set up your credit card or send PayPal information.

Install GitHub Copilot in Visual Studio Code

Once you have your subscription, we will install it in Visual Studio Code. First, go to Extensions and search for GitHub Copilot.

Secondly, Install the extension by pressing the Install button. This is next to the extension in the blade, as shown here.

Thirdly, go to File>New File.

Also, select SQL as the language to use for the file. This is a question that pops up, but you can skip this if you want. For our demo, we'll choose SQL.

You will be sending a message to authorize GitHub for VS Code. Press the Button and then press the Authorize Visual-Studio-Code button.

Getting Started

Using GitHub copilot is a straightforward process. You only need to write comments and it will show you the code. Let’s start with something simple:

--Create a SQL Server table with players.

Copilot will generate the code and with TAB and ENTER you can accept the suggestion:

CREATE TABLE Players2 (
PlayerID int IDENTITY(1,1) PRIMARY KEY,
PlayerName varchar(255) NOT NULL,
PlayerAge int NOT NULL,
PlayerPosition varchar(255) NOT NULL,
PlayerTeam varchar(255) NOT NULL
);

I will ask Copilot to generate data:

--Add real values to the table Players2
--Add 10 values to the table

It doesn’t seem to be smart enough to insert data. It was disappointing:

INSERT INTO Players2 ('John', PlayerAge, PlayerPosition, PlayerTeam)

Also, I asked to backup the adventureworks2019 database:

--backup the database adventurewoks2019

The result was disappointing as well.

 BACKUP DATABASE adventureworks2019

I will provide a path, maybe it needs more information:

--BACKUP the adventureworks database in the c:\backup folder

And the answer was the same…

BACKUP DATABASE adventureworks2019

Frustrated by the previous results, I decided to give it a last chance.

Show GitHub Copilot SQL Server information

First, I will ask some system database queries.

--show me all the databases
SELECT * FROM sys.databases

OK, it is not bad.

Secondly, I will ask about the SQL Server edition.

--Show me the SQL Server edition
SELECT @@VERSION

It is OK, but I only want the edition. The current result is too long:

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: ) (Hypervisor)

I want only the SQL Server edition. I will ask that.

--Show me only the edition and no other information
SELECT SERVERPROPERTY('Edition')

This is great, but I need a column name for the result.

--Include an alias in the edition
SELECT SERVERPROPERTY('Edition') AS 'SQL Server Edition'

Getting Database information

Now, we will get some information about the data files.

--Show me the MB used by the Adventureworks2019 database
SELECT (size * 8)/1024 AS 'Size in MB' FROM sys.database_files

This information is good.

Now I want to get the path of the data files and log files.

--Show me the path of the data files and log files of the adventureworks2019 database
SELECT name, physical_name AS 'Physical Name', size FROM sys.database_files

Also, I want the size of the files in MB.

--Show me the path of the data files and log files of the adventureworks2019 database and the size in MB
SELECT name, physical_name AS 'Physical Name', (size * 8)/1024 AS 'Size in MB' FROM sys.database_files

Shrink data and show fragmentation with GitHub Copilot

In the next example, we will shrink the log file.

--Generate the code to shrink the C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_log.ldf
USE AdventureWorks2019
GO
DBCC SHRINKFILE (AdventureWorks2017_log, 1)
GO

Also, we will try to see the information of the Sales.SalesOrderDetail table.

--Show me the fragmentation of the table [Sales].[SalesOrderDetail]
USE AdventureWorks2019
GO
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2019'), OBJECT_ID(N'Sales.SalesOrderDetail'), NULL, NULL , 'DETAILED')
GO

Working with Stored Procedures with GitHub Copilot

We will now create a stored procedure with parameters.

--Create a stored procedure to get the information of the [Production].[ProductCostHistory] table with the startdate and enddate as input
CREATE PROCEDURE GetProductCostHistory
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SELECT * FROM [Production].[ProductCostHistory] WHERE StartDate >= @StartDate AND EndDate <= @EndDate
END

In addition, we will execute the stored procedure.

--Execute the stored procedure with the startdate 2012-01-01 and enddate 2014-01-01
EXEC GetProductCostHistory '2012-01-01', '2014-01-01'

Finally, we will run a query to find a column.

--Find the table in Adventurewoks2019 that contains the word 'Product' in a column
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'roduct'

Conclusion

In this article, we learn how to generate T-SQL code using GitHub Copilot. It still makes some mistakes, but in general, it is useful for some tasks. I think it simplifies a lot of work.