GitHub Copilot and SQL
Tags: sql, csharp, productivity, codenewbie
Published: 2024-07-04 12:21:33

## Introduction

One of the best ways to up a developer’s skills is to get a subscription for [GitHub Copilot](https://visualstudio.microsoft.com/github-copilot/) which can assist with writing better code and to ask difficult questions.

Copilot [pricing](https://github.com/features/copilot/plans) is affordable at $10 USD per month is a low cost tool for assisting in many facets of coding which will pay for itself usually in less than a day of coding.  

:heavy_check_mark: GitHub Copilot can also assist with writing SQL.
:heavy_check_mark: Best done with known databases like Microsoft NorthWind



## Example 1

A novice developer learn to work with SQL is using Microsoft NorthWind database and wants to create a stored procedure for getting all products for a specific order.

Consider using my version of [NorthWind database](https://gist.githubusercontent.com/karenpayneoregon/e090b43be6834c02ad23cb30029a4097/raw/2db54649f38557c4f4090a3fe64dc8a688e31201/NorthWind2024.sql).

In Visual Studio, create a new file named test.sql, press <kbd>ALT</kbd> + <kbd>/</kbd> to bring up Copilot window and enter.

_using microsoft NorthWind database create  a select statement to get all products for an order as a stored procedure_

Click the paper airplane and Copilot returns the following, click Accept.


![Shows Copilot results](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tubmug5283847h7n6g5k.png)

Well that did not work as there is no OrderId in the Product table. Let's try again by indicating that we are getting information from OrderDetails table.

_using microsoft NorthWind database create  a select statement to get all products for an order in the OrderDetails table as a stored procedure_

```sql
CREATE PROCEDURE GetProductsForOrder
    @OrderID INT
AS
BEGIN
    -- Select all products for the given order
    SELECT p.ProductName, od.Quantity, od.UnitPrice
    FROM OrderDetail od
    INNER JOIN Products p ON od.ProductID = p.ProductID
    WHERE od.OrderID = @OrderID
END
```

Open SSMS (SQL-Server Management Studio) or Azure Data Studio to the NorthWind database, create a new query, drop the SQL from Copilot in and execute.

Or better, copy the SQL between the BEGIN and END into a new query window as shown below to test prior to creating the stored procedure to ensure it works.

- I added a parameter which would in the stored procedure would be passed in.
- Adjust columns needed

```sql
DECLARE @OrderID AS INT = 10248;
SELECT      p.ProductName,
            od.Quantity,
            od.UnitPrice
  FROM      dbo.OrderDetails od
 INNER JOIN dbo.Products p
    ON od.ProductID = p.ProductID
 WHERE      od.OrderID = @OrderID;
```
Execute and this data is returned.


![Using Azure Data Studio the results of the above statement](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2rmrccyrb53vsul22ic1.png)

Since in this case the results are good, go ahead and run the create SQL statement above and if not adjust as needed.

> **Note**
> When performing the above steps Copilot retrieved information from the web and if asking about a unknown database you created more likely than not will get the question wrong. Remember this is for learning so use known databases such as NorthWind and Chinook for instance.

## Example 2

:stop_sign: This example was a random pick from Stackoverflow

A great method to learn is traversing Stackoverflow but a developer when finding a question with an answer the developer must take time to create and populate a database which takes time.

Using the following question 

[How to concatenate text from multiple rows into a single text string in SQL Server](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv?rq=1)

And a developer wants to try the [accepted](https://stackoverflow.com/a/545672/5509738) solution.

In Visual Studio, create a new file named test.sql and press <kbd>ALT</kbd> + <kbd>/</kbd>.

Enter the following: 

Create a database for the following (add a space than paste in the following)

```sql
SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]
```

Click the paper airplane and Copilot produces the following.

```sql
CREATE DATABASE YourDatabaseName;
GO

USE YourDatabaseName;
GO

CREATE TABLE dbo.Students (
    StudentID INT PRIMARY KEY,
    StudentName NVARCHAR(50),
    SubjectID INT
);
GO

INSERT INTO dbo.Students (StudentID, StudentName, SubjectID)
VALUES (1, 'John', 1),
       (2, 'Jane', 1),
       (3, 'Mike', 2),
       (4, 'Emily', 2),
       (5, 'David', 3),
       (6, 'Sarah', 3);
GO

SELECT Main.SubjectID,
       LEFT(Main.Students, LEN(Main.Students)-1) AS "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main];
```

The above will work but let's clean it up, get rid of the create database, instead use an existing database. Note the SQL has been reformatted from the original and at the top a statement has been added to delete the table if it exists which allows for experimentation.

```sql
IF OBJECT_ID(N'dbo.Students', N'U') IS NOT NULL  
   DROP TABLE [dbo].Students;  
GO

CREATE TABLE dbo.Students (StudentID INT PRIMARY KEY,
                           StudentName NVARCHAR(50) NOT NULL,
                           SubjectID INT NOT NULL);
GO

INSERT INTO dbo.Students (StudentID,
                          StudentName,
                          SubjectID)
VALUES (1, 'John', 1),
       (2, 'Jane', 1),
       (3, 'Mike', 2),
       (4, 'Emily', 2),
       (5, 'David', 3),
       (6, 'Sarah', 3);
GO

SELECT Main.SubjectID,
       LEFT(Main.Students, LEN(Main.Students) - 1) AS "Students"
  FROM (   SELECT DISTINCT ST2.SubjectID,
                           (   SELECT ST1.StudentName + ',' AS [text()]
                                 FROM dbo.Students ST1
                                WHERE ST1.SubjectID = ST2.SubjectID
                                ORDER BY ST1.SubjectID
                               FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)') [Students]
             FROM dbo.Students ST2) [Main];
```

Results done in Azure Data Studio


![Results for above statements](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5dyf04zrh7plvo6rwnd7.png)

The above is now part of your box of tricks with minimum effort using Copilot to assist.

## Example 3

A developer has the following SQL statement and wants to understand the statement.

```sql
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    p.Rows
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.name NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.object_id > 255 
	AND p.rows > 0
GROUP BY 
    t.name, s.name, p.rows
ORDER BY 
     t.name
```

Sure they can run it but they want details.

In Visual Studio, create a new file named test.sql and paste the above statement in. Next press <kbd>ALT</kbd> + <kbd>/</kbd> then <kbd>/</kbd>, options appear, select explain and click the paper airplane and Copilot provides details of the statement.


![partial view of Copilot results](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6wx2yr98tg49swofcx0r.png)

Next, press <kbd>ALT</kbd> + <kbd>/</kbd> then <kbd>/</kbd>, options appear, select doc to have Copilot create documentation and we get the following.

```sql
/*
    This SQL script retrieves information about tables in a database.
    It selects the schema name, table name, and number of rows for each table.
    The script joins multiple system tables to gather the required information.
    It also applies filters to exclude system tables and tables with no rows.
    The result is grouped by table name, schema name, and number of rows.
    The final result is ordered by table name.
*/
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    p.Rows
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.name NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.object_id > 255 
	AND p.rows > 0
GROUP BY 
    t.name, s.name, p.rows
ORDER BY 
     t.name
```

## Tips

- When learning in regard to SQL, use well known databases such as Adventure works, NorthWind and Chinook.
- Asking questions for Copilot, write the question, copy the question text to say notepad then ask the question. If the result is not what a developer wants, go back to notepad and rephrase the question again, repeat as needed.

## Summary 

Copilot can be used to assist with more than helping with writing code, Copilot also can be used to assist with writing SQL statements and explain and document SQL.