Karen Payne

Posted on Jul 4 • Edited on Jul 5

GitHub Copilot and SQL

#sql #csharp #productivity #codenewbie

Introduction

One of the best ways to up a developer’s skills is to get a subscription for GitHub Copilot which can assist with writing better code and to ask difficult questions.

Copilot pricing 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.

✔️ GitHub Copilot can also assist with writing SQL.
✔️ 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.

In Visual Studio, create a new file named test.sql, press ALT + / 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.

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



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



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.

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

🛑 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

And a developer wants to try the accepted solution.

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

Enter the following:

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



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.



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.



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

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.



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 ALT + / then /, options appear, select explain and click the paper airplane and Copilot provides details of the statement.

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



/*

    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.