Introduction ChatGPT in SQL Server
In this article, we will learn some basic examples of ChatGPT in SQL Server. In a previous article, we had an interview with ChatGPT an AI chatbot developed by OpenAI. If you do not know how to use it yet, you can go to that article.
We will show some examples of T-SQL code generated by ChatGPT. We will use ChatGPT to generate SQL Server tables with data, and generate T-SQL code to translate the table, column names, and data to other languages. How to split data separated by spaces, create stored procedures based on instructions, and create SQL Logins based on a list separated by commas. Also, we will see some errors and limitations of ChatGPT. Finally, we will talk about pollution. Does ChatGPT produce a lot of pollution?
Let’s start with the questions.
Can you generate the T-SQL code to create a table with all the players' names, teams, and goals of the Spain Football (soccer) league? Also, include the code with real information.
ChatGPT: I can provide an example T-SQL code to create a table with player information for the Spanish football league, commonly known as La Liga. Please note that this is just an example, and the data used here is not necessarily up-to-date or accurate.
--sql CREATE TABLE LaLigaPlayers ( PlayerID INT PRIMARY KEY, PlayerName NVARCHAR(50), TeamName NVARCHAR(50), GoalsScored INT );
INSERT INTO LaLigaPlayers (PlayerID, PlayerName, TeamName, GoalsScored) VALUES (1, 'Lionel Messi', 'FC Barcelona', 20), (2, 'Karim Benzema', 'Real Madrid', 18), (3, 'Luis Suarez', 'Atletico Madrid', 16), (4, 'Gerard Moreno', 'Villarreal CF', 15), (5, 'Youssef En-Nesyri', 'Sevilla FC', 14), (6, 'Mikel Oyarzabal', 'Real Sociedad', 13), (7, 'Iago Aspas', 'RC Celta', 12), (8, 'Alex Berenguer', 'Athletic Bilbao', 11), (9, 'Joselu', 'Deportivo Alaves', 10), (10, 'Roger Marti', 'Levante UD', 9);
This code creates a table named LaLigaPlayers with columns for PlayerID, PlayerName, TeamName, and GoalsScored. It also inserts some example data into the table, with each row representing a player in the league and their corresponding team and goals scored. Again, please note that this is just an example, and the actual data for the league may be different.
Me: It is great because you can easily generate data. This is a sample, but you could get real data of leagues in 1905 or other information in just minutes and create thousands of databases in minutes. However, I was a little disappointed about the small sample of 10 rows when I wanted more players.
Can you add data to that table with the players of Barcelona in 2015?
ChatGPT: Sure! Here's an example T-SQL code to insert data for Barcelona players in 2015 into the LaLigaPlayers table:
--sql INSERT INTO LaLigaPlayers (PlayerID, PlayerName, TeamName, GoalsScored) VALUES (11, 'Lionel Messi', 'FC Barcelona', 43), (12, 'Luis Suarez', 'FC Barcelona', 16), (13, 'Neymar Jr', 'FC Barcelona', 22), (14, 'Ivan Rakitic', 'FC Barcelona', 7), (15, 'Javier Mascherano', 'FC Barcelona', 1), (16, 'Andres Iniesta', 'FC Barcelona', 3), (17, 'Gerard Pique', 'FC Barcelona', 4), (18, 'Jordi Alba', 'FC Barcelona', 2), (19, 'Sergio Busquets', 'FC Barcelona', 1), (20, 'Claudio Bravo', 'FC Barcelona', 0);
This code inserts 10 rows into the LaLigaPlayers table, each representing a player from FC Barcelona in the 2015 season, along with their corresponding PlayerID, PlayerName, TeamName, and GoalsScored. Again, this is just an example, and the actual data for the team in that season may be different.
Me: Again, just a sample of 10 rows. I will try to insist.
Q. Could you calculate the real ages and add more players instead of just an example?
ChatGPT: Sure, I can modify the code to calculate the real ages of the players and add more players as well. Here's an updated example that calculates the ages of the players based on their date of birth, and inserts additional players:
--sql -- Create LaLigaPlayers table CREATE TABLE LaLigaPlayers ( PlayerID INT PRIMARY KEY, PlayerName NVARCHAR(50) NOT NULL, TeamName NVARCHAR(50) NOT NULL, GoalsScored INT, DateOfBirth DATE );
-- Insert data for Barcelona players in 2015 INSERT INTO LaLigaPlayers (PlayerID, PlayerName, TeamName, GoalsScored, DateOfBirth) VALUES (11, 'Lionel Messi', 'FC Barcelona', 43, '1987-06-24'), (12, 'Luis Suarez', 'FC Barcelona', 16, '1987-01-24'), (13, 'Neymar Jr', 'FC Barcelona', 22, '1992-02-05'), (14, 'Ivan Rakitic', 'FC Barcelona', 7, '1988-03-10'), (15, 'Javier Mascherano', 'FC Barcelona', 1, '1984-06-08'), (16, 'Andres Iniesta', 'FC Barcelona', 3, '1984-05-11'), (17, 'Gerard Pique', 'FC Barcelona', 4, '1987-02-02'), (18, 'Jordi Alba', 'FC Barcelona', 2, '1989-03-21'), (19, 'Sergio Busquets', 'FC Barcelona', 1, '1988-07-16'), (20, 'Claudio Bravo', 'FC Barcelona', 0, '1983-04-13'), (21, 'Marc-Andre ter Stegen', 'FC Barcelona', 0, '1992-04-30'), (22, 'Dani Alves', 'FC Barcelona', 3, '1983-05-06'), (23, 'Jeremy Mathieu', 'FC Barcelona', 2, '1983-10-29'), (24, 'Thomas Vermaelen', 'FC Barcelona', 0, '1985-11-14'), (25, 'Douglas Pereira', 'FC Barcelona', 0, '1990-08-06'); -- Calculate the age of each player UPDATE LaLigaPlayers SET Age = DATEDIFF(YEAR, DateOfBirth, GETDATE()); -- Display the contents of the LaLigaPlayers table SELECT * FROM LaLigaPlayers;
This code creates the LaLigaPlayers table, inserts 15 players for FC Barcelona in the 2015 season, and calculates their ages based on their date of birth. The UPDATE statement uses the DATEDIFF function to calculate the difference between each player's date of birth and the current date in years and sets the Age column to that value. Finally, the SELECT statement is used to display the contents of the LaLigaPlayers table.
Me: This is better. It has only 14 players, but I like that it calculates the age and includes the actual birth dates. You can easily create your tables of basketball, baseball leagues, or movies. This ChatGPT has a lot of information. However, it is not displaying all the players.
Can you generate a T-SQL code for a table named countries with all the countries and populations in the world?
ChatGPT: Yes, here is the T-SQL code to create a table named countries with name and population columns:
--sql CREATE TABLE countries ( Country_name nvarchar(100) NOT NULL, population int NOT NULL )
This will create a table with two columns: name for the name of the country and population for the population of the country. You can then populate the table with data using INSERT statements:
INSERT INTO countries (country_name, population)
VALUES
('China', 1433783686),
('India', 1380004385),
('United States', 331002651),
('Indonesia', 273523615),
('Pakistan', 220892340),
('Brazil', 210867954),
('Nigeria', 206139589),
('Bangladesh', 165063758),
('Russia', 145934462),
('Mexico', 130759074),
('Japan', 126451398),
('Philippines', 109376023),
('Vietnam', 97338579),
('Ethiopia', 114963588),
('Egypt', 97665000),
('Iran', 83992949),
('Turkey', 84339067),
('Democratic Republic of the Congo', 87753908),
('Thailand', 69037513),
('Myanmar', 54409800),
('South Africa', 59308690),
('North Korea', 25778816),
('Colombia', 50882891),
('France', 67003611),
('Tanzania', 59734218),
('Kenya', 53771296),
('Argentina', 45376763),
('Uzbekistan', 33469203),
('Malaysia', 32169700),
('Yemen', 29825968),
('Syria', 18719025),
('Morocco', 36296400),
('Saudi Arabia', 34713871),
('Uganda', 45741007),
('Iraq', 40222493),
('Peru', 32971854),
('Venezuela', 28435940),
('Algeria', 43851044),
('Mozambique', 31255435),
('Angola', 32866272),
('Madagascar', 27691018),
('Ghana', 31072940),
('Cameroon', 26545863),
('Côte d''Ivoire', 26378274),
('Burkina Faso', 20967865),
('Mali', 20250833),
('Malawi', 19129952),
('Zambia', 18383956),
('Zimbabwe', 14862924),
('South Sudan', 11193725),
('Rwanda', 12952189),
('Chad', 16244513),
('Guinea', 13132795),
('South Africa', 59308690),
('Burundi', 11890784),
('Sudan', 43849258),
('Congo', 5244363),
('Senegal', 16743927),
('Tunisia', 11818619),
('Syria', 18719025),
('Equatorial Guinea', 1402985),
('Somalia', 15893222),
('Cape Verde', 546388),
('Central African Republic', 4829767),
('Sierra Leone', 7976983),
('Greece', 10727668)
Me: As we said before, it just generates samples of data. However, this can be easily improved.
Translate data with ChatGPT in SQL Server
The next example will show how ChatGPT can translate the table names, column names and content from English into Spanish.
I have the following table with data:
CREATE TABLE [dbo].[documents]( [Title] [nvarchar](50) NOT NULL, [FileName] [nvarchar](400) NOT NULL ) ON [PRIMARY] GO INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Documents', N'Documents') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Overview', N'Overview') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Introduction 1', N'Introduction 1.doc') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Repair and Service Guidelines', N'Repair and Service Guidelines.doc') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Maintenance', N'Maintenance') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Crank Arm and Tire Maintenance', N'Crank Arm and Tire Maintenance.doc') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Lubrication Maintenance', N'Lubrication Maintenance.doc') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Assembly', N'Assembly') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Front Reflector Bracket and Reflector Assembly 3', N'Front Reflector Bracket and Reflector Assembly 3.doc') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Front Reflector Bracket Installation', N'Front Reflector Bracket Installation.doc') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Installing Replacement Pedals', N'Installing Replacement Pedals.doc') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Seat Assembly', N'Seat Assembly.doc') INSERT [dbo].[documents] ([Title], [FileName]) VALUES (N'Training Wheels 2', N'Training Wheels 2.doc') GO
Q. Can you generate the T-SQL script to create the table name, column names, and data in Spanish?
ChatGPT: ¡Claro que sí! Aquí está el script T-SQL para crear la tabla, columnas y datos en español (Certainly, here it is the T-SQL script to create the table, columns and data in spanish):
CREATE TABLE [dbo].[documentos]( [Título] nvarchar NOT NULL, [NombreDeArchivo] nvarchar NOT NULL ) ON [PRIMARY] GO INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Documentos', N'Documentos') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Visión general', N'Visión general') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Introducción 1', N'Introducción 1.doc') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Guías de reparación y servicio', N'Guías de reparación y servicio.doc') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Mantenimiento', N'Mantenimiento') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Mantenimiento de brazo de manivela y neumático', N'Mantenimiento de brazo de manivela y neumático.doc') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Mantenimiento de lubricación', N'Mantenimiento de lubricación.doc') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Ensamblaje', N'Ensamblaje') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Bracket de Reflector Delantero y Ensamblaje de Reflector 3', N'Bracket de Reflector Delantero y Ensamblaje de Reflector 3.doc') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Instalación de soporte de reflector delantero', N'Instalación de soporte de reflector delantero.doc') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Instalación de pedales de reemplazo', N'Instalación de pedales de reemplazo.doc') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Ensamblaje de asiento', N'Ensamblaje de asiento.doc') INSERT [dbo].[documentos] ([Título], [NombreDeArchivo]) VALUES (N'Ruedas de entrenamiento 2', N'Ruedas de entrenamiento 2.doc') GO
ChatGPT: Espero que esto sea útil para ti (I hope this is useful for you).
Me: My native language is Spanish and I can say, that its Spanish translation skills are even better than Google Translate. I once tried to speak with ChatGPT in Quechua (a native language in Bolivia). Chat GPT could understand the question in Quechua but did not answer in Quechua.
When I tried to execute the code suggested I got this error:
Msg 8152, Level 16, State 30, Line 6 String or binary data would be truncated. The statement has been terminated.
The problem is that the table created just had one character limit for the nvarchar columns:
CREATE TABLE [dbo].[documentos]( [Título] nvarchar NOT NULL, [NombreDeArchivo] nvarchar NOT NULL ) ON [PRIMARY] GO
To fix the problem we will add a limit of 50 characters to the nvarchar columns. Drop the table first.
drop table dbo.documentos
Next, create the table with the 50-character limit.
CREATE TABLE [dbo].[documentos]( [Título] nvarchar(50) NOT NULL, [NombreDeArchivo] nvarchar(50) NOT NULL ) ON [PRIMARY] GO
Now you can insert the data again and it will work successfully:
Update data with ChatGPT in SQL Server
The following example shows some functions to fix data problems.
Question: I have a table named names and a column named full name. The full name column contains the first name, a space, and the last name. Can you generate the T-SQL code to create two columns named first name and last name? Add the firstname data from the fullname column into the firstname column and the last name data from the full name column into the last name column. Once this is done, please drop the fullname column.
ChatGPT: Sure, here's the T-SQL code to create two columns named "first name" and "last name", and then split the data from the "full name" column into these two columns, and then drop the "full name" column:
--sql
-- Add the two new columns
ALTER TABLE names ADD first_name varchar(50)
ALTER TABLE names ADD last_name varchar(50)
-- Update the new columns with data from the full_name column
UPDATE names SET
first_name = SUBSTRING(full_name, 1, CHARINDEX(' ', full_name)-1),
last_name = SUBSTRING(full_name, CHARINDEX(' ', full_name)+1, LEN(full_name)-CHARINDEX(' ', full_name))
-- Drop the full_name column
ALTER TABLE names DROP COLUMN full_nameThis code assumes that the "full name" column is already present in the "names" table and that the "first name" and "last name" columns do not already exist. The code splits the data in the "full name" column into the "first name" and "last name" columns using the space character as the delimiter. If any of the names contain multiple words, this code will only capture the first word as the first name, and the remaining words as the last name.
Me: In my table, the column name was fullname and not full_name, so I had to modify it a little bit the code to make it work:
Here you have the table if you want to try for your own:
create table names ( id int, fullname varchar(50) ) insert into names values (1,'John Smith'), (2,'James Dean')
Now, we will execute the update:
UPDATE names SET
first_name = SUBSTRING(fullname, 1, CHARINDEX(' ', fullname)-1),
last_name = SUBSTRING(fullname, CHARINDEX(' ', fullname)+1, LEN(fullname)-CHARINDEX(' ', fullname))As you can see, the code worked successfully:
Finally, you can drop the full name column with the code provided.
ALTER TABLE names DROP COLUMN fullname
Create a stored procedure with ChatGPT in SQL Server
The next example will show how to create a stored procedure with ChatGPT.
Question: I have a table named HumanResources.Employee with the columns [BusinessEntityID] int,LoginID nvarchar(256). Can you create a stored procedure named dbo.Employee that receives 2 parameters named MaxBusinessEntityID and MinBusinessEntityID and does a select [BusinessEntityID],LoginID where BusinessEntityID is between MaxBusinessEntityID and MinBusinessEntityID
ChatGPT: Sure, here's the T-SQL code to create a stored procedure named dbo.Employee that accepts two parameters, @MaxBusinessEntityID and @MinBusinessEntityID, and returns the BusinessEntityID and LoginID columns from the HumanResources.Employee table where BusinessEntityID is between @MaxBusinessEntityID and @MinBusinessEntityID:
CREATE PROCEDURE dbo.Employee @MaxBusinessEntityID int, @MinBusinessEntityID int AS BEGIN SET NOCOUNT ON; SELECT BusinessEntityID, LoginID FROM HumanResources.Employee WHERE BusinessEntityID BETWEEN @MaxBusinessEntityID AND @MinBusinessEntityID; END
This code assumes that the HumanResources.Employee table already exists and has the BusinessEntityID and LoginID columns. The stored procedure accepts two parameters @MaxBusinessEntityID and @MinBusinessEntityID, which are used to filter the results from the HumanResources.Employee table based on the range of BusinessEntityID values. The stored procedure returns the BusinessEntityID and LoginID columns from the filtered results.
Me: This code worked successfully on the first try:
Exec dbo.Employee 1,2
I used the Adventureworks database and the Humanresources.Employee for this example.
Create logins with ChatGPT in SQL Server
This new example shows how to work with a list separated by commas. This can provide an idea about routine tasks.
Question: Can you generate the T-SQL code to add these 3 SQL Server logins with serveradmin permissions? jsmith,sstalone,tcruise. The password will be the names+134#.
ChatGPT: Sure, here's the T-SQL code to create 3 SQL Server logins with serveradmin permissions and set their passwords to their names followed by "134#":
USE master; GO CREATE LOGIN jsmith WITH PASSWORD = 'jsmith134#', CHECK_POLICY = OFF; CREATE LOGIN sstalone WITH PASSWORD = 'sstalone134#', CHECK_POLICY = OFF; CREATE LOGIN tcruise WITH PASSWORD = 'tcruise134#', CHECK_POLICY = OFF; ALTER SERVER ROLE serveradmin ADD MEMBER jsmith; ALTER SERVER ROLE serveradmin ADD MEMBER sstalone; ALTER SERVER ROLE serveradmin ADD MEMBER tcruise; GO
ChatGPT: This code assumes that you are currently connected to the SQL Server instance and have the necessary permissions to create logins and add them to the serv
Me: This code did not require changes and worked fine.
About pollution
We discussed in the previous article about pollution. Some people mention that this technology consumed a lot of energy. Let’s ask ChatGPT about that.
Q. Is it true that your technology pollutes a lot and consumes a lot of energy?
ChatGPT: As an AI language model, I do not consume energy or produce pollution directly since I exist entirely as software running on computer servers. However, the energy consumption and pollution generated by the servers that run AI models like me can be significant, especially as the use of AI continues to grow.
It is worth noting that many companies that develop and use AI technology, including my creators at OpenAI, are aware of the environmental impact of their operations and are taking steps to reduce their carbon footprint. These steps can include using renewable energy sources to power data centers, optimizing algorithms to reduce computational needs, and implementing efficient cooling systems to reduce energy usage.
Overall, while the use of AI technology may contribute to energy consumption and pollution, many in the industry are working to mitigate these effects and develop more sustainable practices.
How many servers are required to make your work for the world?
ChatGPT: As an AI language model, I can work on a single server or cluster of servers, depending on the specific implementation. The amount of computational resources required to run an AI language model like me depends on various factors such as the size of the model, the amount of data it processes, and the complexity of the tasks it performs.
For example, the largest language models today, like GPT-3 from OpenAI, require massive amounts of computational resources to train and run. GPT-3 has 175 billion parameters and requires a cluster of hundreds or even thousands of servers to run.
On the other hand, smaller models with fewer parameters, like the ones used for some chatbots or customer service applications, can be run on a single server or even on edge devices like smartphones.
So, the number of servers required to make me work for the world would depend on the specific use case, the size of the model, and the amount of data being processed.
Me: According to ChatGPT thousands of servers are required.
Do you know the number of servers used for GPT-3?
The exact number of servers used for GPT-3 is not publicly disclosed by OpenAI, the research organization that developed the model. However, it is known that GPT-3 is currently one of the largest language models in existence, with 175 billion parameters, which makes it orders of magnitude larger than most other language models.
Given the size and complexity of GPT-3, it is likely that it requires a significant amount of computational resources to train and run. OpenAI has described the training process for GPT-3 as "a highly distributed and parallelized effort", which suggests that the model was trained on a large cluster of servers. However, the exact number of servers used and the specific hardware specifications are not publicly disclosed.
Conclusion about ChatGPT in SQL Server
In this article, we saw 2 examples to generate data using ChatGPT. You can easily generate random or real data. It is hard to get many rows because ChatGPT tries to attend to millions of users. We also saw how to translate the table columns and the data from English to Spanish. ChatGPT is pretty great to handle different languages. You may need a proofreader to check, but the translation is acceptable and the proofreader will not have too much work. ChatGPT makes some mistakes. If you are not familiar with T-SQL, you will not be able to solve the errors. Humans are still required, but unfortunately, the world now requires fewer DBAs and developers.
Also, learned how to split the firstname and lastname from a column named full name into 2 columns.
In addition, we created a stored procedure and then we created some logins based on a list.
Finally, we talked about pollution. I really love the things that ChatGPT can do. I think it can replace several employees. It makes some mistakes, but I think it can evolve fast and improve its skills really fast. It will be hard to replace experienced DBAs, but not so hard to replace newbies. Regarding pollution, I am pretty sure that humanity will find better ways to improve energy consumption and alternatives to produce energy in a more efficient way and pollute less.