SQL Prompt has an EAP with some AI capabilities. I was asked to do some testing, and while I’ve done relatively little, I did find some time to play with this recently and decided to document what happened.
I completed this and then clicked the < back arrow to return through previous versions of the code, which also shows what the prompt was that produced this code.
This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.
My Experiment
I wanted to write a windowing function to test something. Instead, I decided to ask SQL Prompt to do this for me. I connected to a database, hit ALT+Z, and then entered this prompt in the popup. You can see the code produced.
I only entered the prompt in the dialog. SQL Prompt wrote the highlighted code.
This wasn’t what I wanted, so I changed the prompt. You can see what I typed and what changed below.
Not bad, but not what I wanted. Next I asked to move this to the window clause. I got a window clause, but it wasn’t used in the OVER() clause.
Hmm, I tried again. Once I hit the play button, the AI does something and clears the prompt. That happened above, and this is the prompt I tried next:
It still didn’t work. Actually, it really didn’t work. I’m glad I have a back button.
Let me move on. I can fix that myself. Now I’ll change the column list.
That worked:
Can I add a second aggregate? I’ll try that.
That didn’t work. It changed my SUM to the COUNT I wanted. Interesting.
Thoughts
This is interesting in that it can help me structure queries, but knowing how to prompt them matters. I do like the back and forth, as I hit back above and then added to my prompt, which worked.
This doesn’t feel faster to me, but I’ve written a lot of SQL code. I could structure this stuff quickly and easily. In fact, I’d likely use a * in the column list and try to build the window clauses I need first, then go back and add aggregates. Normal SQL Prompt would help me, and I think it would be faster than the AI.
However, I also know the AI is new, and there are possibilities here, which might be easier than me playing with syntax and looking through suggestions, even with SQL Prompt.
I’ll keep playing and hopefully I’ll find this useful. Or at least useful enough that I can give good feedback to the team experimenting here.
Voice of the DBA 
Steve – Because of your post I have signed up for this EAP. I had no idea it was going on then again to be honest I rarely read anything from Red-gate if it look’s like solicitation.
LikeLike
Lol, lots of them are some sort of solicitation. Some are useful to you, some aren’t. I still think email marketing works well, though the volume means I do a lot of what you do, ignore most.
Be interested what you think of it. I am not sure it’s super helpful for me, but I’m experimenting a bit
LikeLike
Interesting. Were you able to try github copilot for this? I have access so could try for you. I have no experience with EAP but ghcp works really well. tbh, these AI tools aren’t really meant for YOU, a seasoned SQL person, but tend to work well for n00bs. As you mentioned, anything with an LLM behind it really requires you to be able to put together a “chain of thought” in the prompting so it can do the things in a stepwise manner.
I use ghcp for esoteric Spark code that I can never remember how to do, don’t feel like looking up the snippets, and tend to be poorly documented. The exemplar is using SparkSQL in a streaming use case to do a MERGE statement. Spark MERGE is a PITA, just like every other language. Another example is KQL. While that is an AWESOME language, I’m too old to learn it, but ghcp makes it a breeze.
LikeLike
I’ve tried some GHCP, but it can’t see my schema, so it’s less useful. At least, I haven’t seen a good way to get it to see schema other than including it in my prompt, which isn’t really helpful.
Do you prompt in a different way? Be neat to share what you prompt and how it’s worked well. I find too many people don’t share their prompts, so they may have tricks I’m missing.
LikeLike
Steve – In this context what exactly is a “Prompt”?
LikeLike
It’s the question or guidance you give the AI.
So, above, it’s the “use the over clause” part in the little drop down. In ChatGPT, or other items, it’s what you write to get a response. Your “prompts” guide the AI in producing a result.
LikeLiked by 1 person
Since the product we are discussion is SQL Prompt I assumed a “Prompt” was something specific to that app
LikeLike
Maybe a better explanation: https://zapier.com/blog/ai-art-prompts/
LikeLike
Yet Another Overloaded Acronym (YAOA), like DAC. That’s quite a few things.
LikeLike
I’ve tried several basic but non beginner tasks with AI. To summarize my findings, “AI” stands for “Arrogantly Incorrect” in every case that I’ve tried except for converting a small list of values to a test table.
LikeLike