I figured I might get ChatGPT to write T-SQL, test out the results, and see how it compares to T-SQL written by a human.

Prompt: Whoami Output: I am an artificial intelligence programmed to assist with tasks and answer questions. I do not have a physical identity or personal characteristics. ChatGPT write T-SQL

Warning: Do not run any of the following T-SQL in a production environment. There are already enough databases being tortured by developers on a daily basis.

Starting out with a simple REPLACE

I’m starting with the basic request of replacing all numbers in a column.

Prompt: Write T-SQL that uses nested REPLACE functions to replace numbers 0 through 9 in a column without using variables. Output: Here is an example of a T-SQL statement that uses nested REPLACE functions to replace numbers 0 through 9 in a column without using variables: UPDATE YourTable SET YourColumn = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(YourColumn, '0', 'X'), '1', 'X'), '2', 'X'), '3', 'X'), '4', 'X'), '5', 'X'), '6', 'X'), '7', 'X'), '8', 'X'), '9', 'X') This statement will update the values in the YourColumn column of the YourTable table, replacing all occurrences of the numbers 0 through 9 with the letter X. Note that this approach can become unwieldy if you need to replace a large number of different values. In that case, it might be more efficient to use a loop or a cursor to iterate through the values you want to replace.

The resulting T-SQL code is valid. It assumes that the column’s data type is VARCHAR and that the numbers should be replaced with X, but that’s because I didn’t provide enough details in my prompt.

Adding some complexity to it

I use my previous prompt as a template, and add a bit more complexity in the mix.
I provide information about the column’s data type, as well as what the numbers should be replaced with.

Prompt: Write T-SQL that uses nested REPLACE functions to replace numbers 0 through 9 in a NVARCHAR column with dynamically generated random three character strings without using variables. Output: UPDATE Table SET NVARCHAR_Column = REPLACE(NVARCHAR_Column, '0', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '1', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '2', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '3', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '4', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '5', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '6', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '7', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '8', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65) REPLACE(NVARCHAR_Column, '9', FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65 || FLOOR(RAND()*26)+65)

There’s a lot to unpack here:

  1. ChatGPT took the “NVARCHAR column” part literally and used it as the name of the column, instead of treating the strings as Unicode. But at least it had the decency to put an underscore between the words.
  2. While FLOOR(RAND()*26)+65 will generate a random value, it will always be a two-digit number.
  3. Since 3 two-digit numbers will be concatenated*, we’ll end up with a singe number being replaced with a random six-digit number.
  4. And now for the problems with the concatenation part. While I did explicitly specify that this should be T-SQL code, ChatGPT used ||, which is an Oracle-specific concatenation operator, and it doesn’t work in SQL Server.
  5. Just to get nit-picky – even if ChatGPT would have opted for a T-SQL appropriate concatenation using the + symbol, SQL Server would have just added up those 3 numbers.

The human version

I don’t want to paste a lengthy piece of T-SQL that doesn’t really do much aside from this niche case.
So, to keep it short, I would use the initial T-SQL generated by ChatGPT, but would replace every occurrence of ‘X’ with the following:

The above piece of code generates a random number between 65 and 90 and then applies the CHAR function to that number. Since 65-90 is the ASCII character set range for A-Z, the result will always be something in that range.

How about some DBA-specific tasks?

Database restores

Trying this time with a basic restore. The kind that would be done to create a temporary test version of the original database.

This was too long to get it all into one screenshot.

Prompt: Write T-SQL code to restore a full database backup file named AdventureWorks.bak as a new database named AdventureWorks_test. Path to backup file E:\VSQL\Backup\ Path where the data file should be written E:\VSQL\Data Path where the transaction log file should be written - E:\VSQL\TLog Restore progress should be shown in 10% increments. Once the restore is finished, the database should be set to use the simple recovery model.

And this is what ChatGPT suggested.

Output: Here is the T-SQL code to restore the AdventureWorks database from a full database backup file as a new database named AdventureWorks_test: -- Set the database to use the simple recovery model ALTER DATABASE AdventureWorks_test SET RECOVERY SIMPLE; -- Restore the database from the full backup file RESTORE DATABASE AdventureWorks_test FROM DISK = 'E:\VSQL\Backup\AdventureWorks.bak' WITH MOVE 'AdventureWorks_Data' TO 'E:\VSQL\Data\AdventureWorks_test.mdf', MOVE 'AdventureWorks_Log' TO 'E:\VSQL\TLog\AdventureWorks_test.ldf', REPLACE, STATS = 10 GO This code will restore the AdventureWorks database from the full backup file located at 'E:\VSQL\Backup\AdventureWorks.bak' and create a new database named AdventureWorks_test. The data file will be written to 'E:\VSQL\Data' and the transaction log file will be written to 'E:\VSQL\TLog'. The restore progress will be shown in 10% increments, and once the restore is finished, the database will be set to use the simple recovery model. Note: Make sure to adjust the file paths to match your environment and to use the correct path for the full backup file.

This is actually good, the only small issue would be the order of the operations.
The recovery model should be set to simple after the restore, as specified in the prompt.

Killing sleeping sessions with active transactions

Sometimes, due to developers used to Oracle trying to get SQL Server to behave in a similar fashion, or just JDBC being horrible, you end up with a bunch of sleeping sessions still holding on to opened transactions.
Maybe ChatGPT can help with this scenario.

Prompt: Kill sleeping spids with opened transactions Write T-SQL code to kill all the sleeping sessions that still have opened transactions on an instance. Output: DECLARE @session_id INT SELECT @session_id = session_id FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND status = 'sleeping' AND open_transaction_count > 0 WHILE @session_id IS NOT NULL BEGIN EXECUTE ('KILL ' + @session_id) SELECT @session_id = session_id FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND status = 'sleeping' AND open_transaction_count > 0 END

Due to the poor logic of this piece of T-SQL, the KILL command just goes into a loop trying to terminate the same sesion over and over. If left running long enough it can cause SSMS to crash.

This can be easily reproduced by opening a couple of query editor tabs and just executing a BEGIN TRAN statement in each, and then running the above T-SQL in another query editor.

To see the errors before it’s too late, you’ll have to switch to the Messages tab of the results pane.

Continuous errors with the message: Msg 6106, Level 16, State 2, Line 1 Process ID 51 is not an active process ID. ChatGPT write T-SQL

You can find in this post a script that can help with killing multiple sessions and allows for more control and filtering. It’s also available on GitHub.

Or some fire-able offense?

But what if I want to do something bad on purpose? Like reversing the names of all the tables in a database.

Prompt: Write T-SQL to identify and rename every table in a database to the table's reversed name using dynamic T-SQL. Result: -- First, we need to get a list of all the tables in the database DECLARE @tables TABLE (table_name varchar(255)) INSERT INTO @tables SELECT name FROM sys.tables -- Now we can loop through each table and rename it to the reversed name DECLARE @table_name varchar(255), @reversed_name varchar(255) WHILE EXISTS (SELECT 1 FROM @tables) BEGIN SELECT TOP 1 @table_name = table_name FROM @tables SELECT @reversed_name = REVERSE(@table_name) -- Use dynamic T-SQL to rename the table EXECUTE sp_rename @table_name, @reversed_name -- Remove the table from the list so we don't rename it again DELETE FROM @tables WHERE table_name = @table_name END ChatGPT write T-SQL

The dreaded T-SQL:

Will it work?

Even if the AI ignored my request for a dynamic SQL solution, the result does work with one gotcha:

  • It errors out if the database has more than one schema, and will only rename the tables on the same schema as the current user’s default one.

Conclusion

While ChatGPT, like similar AIs, are fun and interesting to test ideas on or potentially use them as a learning tool, they’re not really at the stage where you can blindly use anything they suggest without first making at least a few tweaks of your own.