Introduction

Previously, we wrote about Copilot and showed how to install and work with it. A GitHub copilot is an AI programmer who can help a lot of developers with daily tasks. In this article, we will learn the following things:

• First, we will use the code chat to send instructions to Copilot and generate T-SQL code.
• Secondly, we will use the code explanation to explain a stored procedure.
• Thirdly, we will fix code problems to find the problem in a query.
• Also, we will generate Docs of some queries.

Requirements

We assume you already have Visual Studio Code and GitHub Copilot installed. For more information, look for our previous Copilot article “Writing SQL Server code with AI using GitHub Copilot”.

How to use the code chat with GitHub Copilot

Copilot has a code chat where you can send orders. It is like ChatGPT, you send your requests and it generates the code. In this article, we will work exclusively with T-SQL, but Copilot can work with Python, JavaScript, TypeScript, Ruby, Go, C++, C#, and other languages. Let’s take a look at it.

First, we will work with an SQL file in Visual Code.

Secondly, right-click the code and select Copilot>Start Code Chat.

You will have a textbox to write your orders and requests. You can send your requests here.

Let’s write an order: "Please show me the list of user-stored procedures in a database using T-SQL."

The results are the following:

SELECT name
FROM sys.procedures
WHERE type = 'P' AND is_ms_shipped = 0
ORDER BY name;

The query uses the sys.procedures system view to list the procedures. The type is P (procedures). Also, we have the is_ms_shipped = 0. This column indicates if the procedure is shipped with Microsoft or not. In this example, it is not.

In addition, you can accept the code, Discard, Discard to Clipboard, or a new file. You can also like or dislike the solution using the like button.

How to use the code explanation in GitHub Copilot

The next example will show you how to explain code created in T-SQL. This is very useful if you have someone else code without documentation and you want to understand the code (my daily routine in other words).

First, we have the following code:

USE [AdventureWorks2019]
GO
/****** Object: StoredProcedure [dbo].[uspGetManagerEmployees] Script Date: 26/09/2023 18:52:56 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_TYPE = 'VIEW'
ALTER PROCEDURE [dbo].[uspGetManagerEmployees]
@BusinessEntityID [int]
AS
BEGIN
SET NOCOUNT ON;
-- Use recursive query to list out all Employees required for a particular Manager
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName',
[EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)
END;

But it is not our code. We want to understand it. Is it possible to understand the code with Copilot?

Secondly, we will select the code and right-click selecting the Copilot>Explain This option.

Thirdly at the left pane, you will have a nice explanation for the code:

Finally, the complete explanation is here:

GitHub Copilot: This SQL code defines a stored procedure named `uspGetManagerEmployees` that takes a single input parameter `@BusinessEntityID` of type `int`. The procedure returns a list of all employees that report to the manager with the specified `@BusinessEntityID`.
The procedure uses a recursive common table expression (CTE) to list out all employees required for a particular manager. The CTE is named `EMP_cte` and has columns for `BusinessEntityID`, `OrganizationNode`, `FirstName`, `LastName`, and `RecursionLevel`. The `OrganizationNode` column is used to represent the hierarchical relationship between employees and managers.
The CTE is defined using two SELECT statements that are combined using the UNION ALL operator. The first SELECT statement retrieves the initial list of employees for the specified manager. The second SELECT statement joins the recursive member to the anchor and retrieves the next level of employees in the hierarchy. The recursion continues until all employees have been retrieved.
The final SELECT statement joins the CTE back to the `Employee` table to return the manager name and other employee details. The results are ordered by `RecursionLevel` and `OrganizationNode`. The `OPTION (MAXRECURSION 25)` clause is used to limit the maximum recursion level to 25 to prevent infinite recursion.
To improve the readability of the code, the author could consider using more descriptive column names and adding comments to explain the purpose of each section of the code. Additionally, the author could consider using a more consistent indentation style to make the code easier to read. To improve the performance of the code, the author could consider adding indexes to the `Employee` and `Person` tables to speed up the joins.

I think it is a pretty nice explanation and also, at the end, it send some suggestions to improve and enhance the code.

How to fix code problems with GitHub Copilot

Now, we will see how to fix code problems. First, we have the following T-SQL code:

SELECT [Title]
FROM [AdventureWorks2019].[Person].[Person]
WHERE TITLE = NULL

We are trying to find the titles that are null, but this query does not show any result.

Secondly, we will select the code, right-click, and select the Copilot>Fix This option.

Fix code

Thirdly Copilot suggests using IS NULL instead of =.

Accept or discharge changes

The fixed code is the following:

SELECT [Title]
FROM [AdventureWorks2019].[Person].[Person]
WHERE TITLE IS NULL;

We cannot use = because NULL is the absence of a value. Then the IS NULL should be used.

As you can see, copilot can help to fix some errors.

How to generate comments with GitHub Copilot

Another nice thing about Copilot is that you can generate comments to explain the code. Let me show you an example in order to understand what I mean.

First, we have this code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_TYPE = 'VIEW'

Secondly, we want to generate comments for this code. To do it, select the code. Right-click and select the Copilot>Generate Docs.

Finally, Copilot will generate comments for the line selected.

/*
This SQL script selects the names of all views in the current database. It uses the INFORMATION_SCHEMA.VIEWS system view to retrieve the names of all views that have been defined in the database. The WHERE clause filters the results to only include views, as opposed to other types of objects that may be defined in the database.
*/

Conclusion

In this article, we learned how to use the Copilot chat and send orders to generate code. Also, we used the explain this feature to explain code. In addition, we used the feature to fix code. We sent some code and Copilot found the problem. Finally, we learned how to generate Copilot T-SQL comments on selected lines of code.

In general terms, I prefer ChatGPT. I think it makes fewer mistakes than the copilot. For me, GitHub Copilot looks like a ChatGPT version 2.0. Anyway, it is a nice technology. The AI is amazing, but it is not the best tool in the market right now.

Images

Some images were generated in Bing image creator.