Using ChatGPT for T-SQL Code Reviews
For this month’s T-SQL Tuesday, Pinal Dave asked us if AI has helped us with our SQL Server jobs. For me, there’s been one instant, clear win: code reviews.
I usually keep a browser tab open with ChatGPT 4, and I paste this in as a starting point:
You are an experienced, diligent database developer who specializes in Microsoft SQL Server. Review the below stored procedure and decide whether it has any security or performance risks.
Then I paste in the code right below that. Here’s an example output using bad dynamic T-SQL. ChatGPT 4 cut straight to the point, identified the risk of SQL injection, and even rewrote the query for me in a less-vulnerable way, plus pointed out the performance risks of using LIKE for string searches.
In milliseconds.
This particular example is a really short piece of code, and even here, the advice isn’t perfect. ChatGPT renamed my @StringToExecute variable, and I’ve experienced tons of similar issues with client code. It’ll introduce subtle changes that are unrelated to the problem it’s trying to solve, but… for a blazing-fast code review, I’ll take it.
The more complex your code gets, the more vague ChatGPT’s advice becomes. For example, I took a query from my Mastering Query Tuning class’s deadlock module and asked ChatGPT why this query is getting deadlock errors. ChatGPT’s answer was really long, but mostly devoid of value. There’s one tiny good nugget in there, but I feel really bad for someone tasked with reading ChatGPT’s wall of text and extracting the tiny nugget that will actually solve the problem. I even tried a very directed, suggestive prompt for ChatGPT to lead the horse to water, and it still didn’t drink. It’s not bad advice, per se – it’s just really wordy and misdirected.
Still, though, I love using this as a very, very, very fast sanity check on code. I also like doing it together while sharing screens with client staff too because:
- I can get code recommendations faster than they can explain what the code is doing
- The client’s tech staff learns a new technique to get peer review on their own code
- The client’s management staff sees firsthand that 2024-level AI is a helpful tool, but not an end-all solution, because a lot of the advice requires human interpretation (and a lot of it is garbage)
Is it a perfect tool? No, but it’s very productive. Like Mitch Ratcliffe said, “A computer lets you make more mistakes faster than any other invention with the possible exceptions of handguns and tequila.”
23 Comments. Leave new
Mitch Ratcliffe’s comment is so true, but ChatGPT code review is still a very interesting perspective! I’m going to test it out. Thanks, Brent.
I’m befuddled – other than changing the name and size of the variable, the code looks the same to me. Can someone explain how they are different?
Hmm, no, they’re completely and wildly different. One uses parameterization. You may need to copy them out into SSMS side by side to go through ’em, or you can also watch the Dynamic SQL module in my Mastering Query Tuning class to see the differences.
Steve and I must both be blind. It looked (and still looks) the same to me too, side by side?
Original:
EXEC sp_executesql @StringToExecute,
N’@SearchDisplayName NVARCHAR(100), @SearchLocation NVARCHAR(100), @SearchReputation INT’,
@SearchDisplayName, @SearchLocation, @SearchReputation;
ChatGPT:
EXEC sp_executesql @SQL,
N’@SearchDisplayName NVARCHAR(100), @SearchLocation NVARCHAR(100), @SearchReputation INT’,
@SearchDisplayName, @SearchLocation, @SearchReputation;
No worries! I updated the link with a new conversation to make the differences even easier to spot: https://chat.openai.com/share/61975219-b4b8-4ab6-97f9-e9b3f1cce260
There it is. In the first link, the original example wasn’t concatenating the parameters into the sql string like I was expecting 😀
Have any clients yet said “No please don’t paste our code into the ai, it’s proprietary.”? Or are they usually pretty amenable to the concept?
Yes, but they’ve had ChatGPT Enterprise, which you can configure to not use your data for training purposes.
Ah okay cool!
Brent wrote “I can get code recommendations faster than they can explain what the code is doing” is true but counterintuitive. “Not sure why this bill is being updated with interest but lets do it faster – perhaps more often”
Knee jerk reactions of “Add an index” or “Loop through parts of the table to reduce the candidate set”
For our PoCs, we have so far used OpenAI GPTs, which we have equipped accordingly with knowledge about the “special characteristics” of T-SQL. However, a dedicated small UI was particularly promising, which we used directly via the Chat API and made a range of tools available to it via the API. These were, for example: “Is this valid T-SQL?”, “Look at the database schema.”, “What indexes are there for this table?”, “Find a pattern to optimize the where clause.” (List does not correspond to the exact tool descriptions 😉 ). This allowed us to build agents that “optimized” and evaluated T-SQL code over and over again.
Yeah, add-ons and plug-ins will be a huge market for it over time.
Oh yes, great things are in store for us all. That’s going to be exciting.
Have you also compared chatgpt’s results with results generated by other ai tools like Google Gemini or Ms Copilot or any other uprising ai splash?
No, there are only so many hours in the day, and unfortunately I haven’t had the time to play around with other tools.
I have been using copilot with uncertainty over what straight chat gpt would learn from, but have been noticing the results have been getting worse over time – lots of responses like, ‘SQL is a complex language that I can’t be certain of blah blah’ or it makes a response that claims to identify a problem that doesn’t exist – sometimes even with the alleged error formatted for emphasis.
I read that ChaGPT has an option to disable learning on free accounts that I may have to look into
I should add – if someone did side-by-side testing and proved that another LLM was better for T-SQL analysis though, I’d switch in a heartbeat for that particular task!
[…] Using ChatGPT for T-SQL Code Reviews (Brent Ozar) […]
I like using a prompt to document the code. I have to pull out a lot of useless comments but its a good start for a sometimes boring job.
Awesome quote at the end!! I’m going to have to remember that one.
[…] Using ChatGPT for T-SQL Code Reviews: Brent Ozar explores the use of ChatGPT for T-SQL code reviews, highlighting its efficiency in identifying security and performance risks, like SQL injection and the misuse of LIKE for string searches. Despite its rapid and helpful insights, Ozar notes ChatGPT’s limitations with more complex code, where advice can become vague and less useful. Yet, he values ChatGPT as a fast, initial check and an educational tool for clients and their tech teams, acknowledging its productivity boost while reminding us of the necessity for human oversight. (Don’t forget to read comments to this blog post. […]
I would like to find out more about the performance risks of using LIKE, but can’t find a statement about that….
Did this perhaps get lost when recreating the OpenAI link or am I just not looking at the right part?
Sure, start here: https://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/
And we dig deeper into it in my Fundamentals of Query Tuning and Mastering Query Tuning classes.