I’ve used ChatGPT a little, but just recently I’ve tried to use it to assist with database development tasks. I have a friend that was very enthusiastic about using a similar tool to assist with coding, so I wanted to see what it could do for database work.
I was impressed with the range of tasks ChatGPT could handle, and how good of a job it did.
I’m working with SQL Server 2022 and ChatGPT May24 version.
Here are some of the items that I asked ChatGPT to assist with.

* Create Table
I asked ChatGPT to generate the SQL to create a table.

Prompt:
Please create a table for SQL Server called order in the dbo schema. It should have an auto-number primary key column. The other attributes are customer_id, order_date, product_name, amount.
The customer_id attribute refers to the customer_id attribute in a dbo.customer table.

Answer:

CREATE TABLE dbo.[order] (
    order_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    product_name VARCHAR(255),
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES dbo.customer(customer_id)
);

The script even delimits the table name, since order is a reserved word.
I went back a second time and added “All columns are not nullable.” to the prompt, and the updated script added NOT NULLABLE to each column.
The answer did a good job with assigning the data types. Interesting that amount used Decimal instead of Money for the data type. I never use the Money type, so that was fine with me.

* Script For Reference Data

Prompt
Please generate insert statements for a SQL Server table named country, where column country_name is the name of the country, and column country_code is the three character ISO 3166-1 alpha-3 code.

Answer:

INSERT INTO country (country_name, country_code) VALUES ('United States', 'USA');
INSERT INTO country (country_name, country_code) VALUES ('United Kingdom', 'GBR');
INSERT INTO country (country_name, country_code) VALUES ('Canada', 'CAN');
INSERT INTO country (country_name, country_code) VALUES ('Germany', 'DEU');
INSERT INTO country (country_name, country_code) VALUES ('France', 'FRA');

I couldn’t get the full list generated, I could only get a sample of the dataset.
I went back and added “Please include all current countries.” to the prompt. The second request got me 10 countries but that was it.
I can see where a list of thousands of records could be a problem, but 200+ countries doesn’t seem like it should be a big deal.

* Generate Stored Procedures

Prompt:
Could you generate CRUD procedures for SQL Server for this table? 

CREATE TABLE dbo.customer (
    customer_id int NOT NULL IDENTITY (1,1) PRIMARY KEY,
    first_name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    company_name varchar(100) NULL,
    email varchar(150) NOT NULL
);

Answer:

-- Insert
CREATE PROCEDURE dbo.CreateCustomer
    @first_name varchar(50),
    @last_name varchar(50),
    @company_name varchar(100) = NULL,
    @email varchar(150)
AS
BEGIN
    INSERT INTO dbo.customer (first_name, last_name, company_name, email)
    VALUES (@first_name, @last_name, @company_name, @email);
END

-- Select
CREATE PROCEDURE dbo.GetCustomer
    @customer_id int
AS
BEGIN
    SELECT * FROM dbo.customer WHERE customer_id = @customer_id;
END

-- Update
CREATE PROCEDURE dbo.UpdateCustomer
    @customer_id int,
    @first_name varchar(50),
    @last_name varchar(50),
    @company_name varchar(100) = NULL,
    @email varchar(150)
AS
BEGIN
    UPDATE dbo.customer
    SET first_name = @first_name,
        last_name = @last_name,
        company_name = @company_name,
        email = @email
    WHERE customer_id = @customer_id;
END

-- Delete
CREATE PROCEDURE dbo.DeleteCustomer
    @customer_id int
AS
BEGIN
    DELETE FROM dbo.customer WHERE customer_id = @customer_id;
END

I didn’t like the Select proc using SELECT *, so I added “For the Select procedure, could you have the proc return the specific column names instead of using SELECT *?” to the prompt, which returned SQL that specified the column names in the SELECT.

* Create Queries From Existing Algorithms

I’ve seen an algorithm to calculate on what day of a specified year that the Easter holiday falls on.

Prompt:
Could you create a SQL Server function to calculate the date of the Easter holiday for a specified year?

Answer:
Certainly! Here’s an example of a SQL Server function that calculates the date of the Easter holiday for a specified year using the Gauss algorithm:

CREATE FUNCTION CalculateEasterDate (@Year INT)
RETURNS DATE
AS
BEGIN
    DECLARE @A INT, @B INT, @C INT, @D INT, @E INT, @F INT, @G INT, @H INT, @I INT, @K INT, @L INT, @M INT, @Month INT, @Day INT

    SET @A = @Year % 19
    SET @B = @Year / 100
    SET @C = @Year % 100
    SET @D = @B / 4
    SET @E = @B % 4
    SET @F = (@B + 8) / 25
    SET @G = (@B - @F + 1) / 3
    SET @H = (19 * @A + @B - @D - @G + 15) % 30
    SET @I = @C / 4
    SET @K = @C % 4
    SET @L = (32 + 2 * @E + 2 * @I - @H - @K) % 7
    SET @M = (@A + 11 * @H + 22 * @L) / 451

    SET @Month = (@H + @L - 7 * @M + 114) / 31
    SET @Day = (@H + @L - 7 * @M + 114) % 31 + 1

    RETURN CAST(CAST(@Year AS VARCHAR(4)) + RIGHT('00' + CAST(@Month AS VARCHAR(2)), 2) + RIGHT('00' + CAST(@Day AS VARCHAR(2)), 2) AS DATE)
END

You could simplify the last line a bit by using the DATEFROMPARTS function, using the integers for year, month and day. But still impressive to put this together.

* Create A Query Joining Two Tables

Prompt:

CREATE TABLE dbo.customer (
customer_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
company_name varchar(150) NOT NULL
);

CREATE TABLE dbo.[order] (
order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
customer_id int NOT NULL REFERENCES dbo.customer (customer_id),
order_date date NOT NULL,
product_name varchar(100) NOT NULL,
amount decimal(9,2) NOT NULL
);

Please write a SQL Server stored procedure to return all orders from the dbo.order table along with the customer data from the dbo.customer table, using order_date as the input parameter.

Answer:

CREATE PROCEDURE GetOrdersByDate
    @orderDate date
AS
BEGIN
    SET NOCOUNT ON;

    SELECT o.order_id, c.customer_id, c.first_name, c.last_name, c.company_name, o.order_date, o.product_name, o.amount
    FROM dbo.[order] o
    INNER JOIN dbo.customer c ON o.customer_id = c.customer_id
    WHERE o.order_date = @orderDate;
END

I assumed that ChatGPT knew which column to use for the join from the foreign key. I tried the same request again, but without the FK. It still knew to join on customer_id, I’m assuming since the same column was in both tables and was the customer primary key.

* Find Errors With Queries

Prompt:

CREATE TABLE dbo.customer (
customer_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
company_name varchar(150) NOT NULL
);

CREATE TABLE dbo.[order] (
order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
customer_id int NOT NULL REFERENCES dbo.customer (customer_id),
order_date date NOT NULL,
product_name varchar(100) NOT NULL,
amount decimal(9,2) NOT NULL
);

Using these tables, please find the error with this query:

SELECT o.order_id, c.customer_id, c.first_name, c.last_name, c.companyname, o.order_date, o.product_name, o.amount
FROM dbo.[order] o
INNER JOIN dbo.customer c ON o.customer_id = c.customer_id
WHERE o.order_date = '2023-06-20';

Answer:

SELECT o.order_id, c.customer_id, c.first_name, c.last_name, c.company_name, o.order_date, o.product_name, o.amount
FROM dbo.[order] o
INNER JOIN dbo.customer c ON o.customer_id = c.customer_id
WHERE o.order_date = '2023-06-17';

ChatGPT was able to detect that the query used a column name of companyname instead of company_name.

* Analyze Executions Plans

Using the same query from above, I created an execution plan in SQL Server. We can save the plan as XML, and give that plan to ChatGPT.
It was able to suggest adding indexes, on the non-indexed join column, as well as order_date, since we are specifying that value in the query. It also gives more generic advice, like making sure statistics are up to date.

* Create Table Structure

As a last request, I asked ChatGPT to put together tables for me for customer and order information.

Prompt:
Please create tables for a SQL Server database to store customer and order information for a generic line of products.

Answer:

-- Table: Customers
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Phone VARCHAR(20),
    Address VARCHAR(100)
);

-- Table: Orders
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Table: OrderItems
CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    ProductName VARCHAR(100),
    Quantity INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

The answer was similar to what I was using for a test, but it went the extra step to account for orders with multiple items. The result wasn’t anything that you would throw straight into production, but was a good starting point.

* Wrap Up

Overall, I was pretty impressed with what ChatGPT could do. In particular, I was surprised it was able to analyze an execution plan. There were a few things were I wasn’t totally satisfied with the output, but adding additional information to the prompts helped resolve that.