Voice of the DBA

Tag: AI

Getting References for GenAI Results

I wrote an editorial on the view of GenAI tech from execs and someone commented that they wanted references for results. Most of us might take a result from a co-worker and use it without asking for any proof of where it comes from. If we trust our co-workers, this can work well. If we don’t, then we might want to know where they learned this and how they know it works.

For GenAI, we might want some references to help us learn more or check something. Or, more likely, learn how to modify something that isn’t perfect.

In this sense, GenAI can be better as this gives us the references we need to learn how the answer was generated.

This post looks at how I got references in a few cases using Claude, Perplexity, and Copilot.

This is part of a series of experiments with AI systems.

Asking a Question

I decided to ask a question that I was asked by someone and I used Copilot to find the answer. Here is my prompt:

I need to schedule a Powershell script to access a SQL Server and want to use a managed service account to run the script. How can I do this in powershell

Let’s look at how the various tools I’ve used respond.

Claude

Claude.ai is from Anthropic and it’s a tool I’ve enjoyed using. When I pasted in the prompt, I saw this response:

I got to the bottom and no references, but when I asked for them (see my prompt in the image), I got a few to look at.

That’s one of the tricks with AI. You can ask it to explain itself.

Perplexity

My second test was perplexity, which I started to use a bit after Grant mentioned it.

Right at the top, I get some references that I can click. When I get to the bottom, I have a list of possible future prompts, which aren’t references, but they get me thinking about things I might need to consider.

That worked well and I liked having the references, though I’d prefer them at the end.

Copilot

I decided to try Copilot inside of VS Code, which is where I’d likely use it. I entered the prompt as a comment in a blank .ps1 file. After my first line, it suggested a few more comments to give context. I accepted a few and then added the last #include references line.

I didn’t get references and the generation stopped at the #End of script line.

I then added the comment below asking for URL references.

Each of the items above appeared as a line in italics, like other suggestions. I hit Tab to accept and I’d get another reference. When I saw the last one repeat, I stopped the responses.

Summary

I’m not evaluating the code here, just trying to see if I could get some idea of where the code came from. In this case, I don’t deal with MSAs a lot, so it’s good that I can get some URLs to check docs or get more options other than the code I found.

If I asked this question on Stack Overflow, I might get the same code (or similar), but getting URLs or references isn’t always easy there. Some people provide them, some don’t, and some might just close my question and say I should have searched better.

I think the GenAI does a good job here of giving me a starting point, and a place to go, which is helpful.

June 2, 2025
Copilot Outside of SSMS Seems to Work

I read Brent’s first look at SSMS and Copilot in there. He didn’t have a great opinion of the tool, especially comparing it to Gemini or Claude. I haven’t tested this yet, mostly because I’ve got other stuff going on, but I decided to take his prompt and the code and drop it into the dedicated Copilot App on my Windows 11 laptop.

This is part of a series of experiments with AI systems.

I have a button that starts Copilot, so I pressed it and put in this prompt:

This was copied from Brent’s post, and then his code copied and dropped in.

I was curious to see if this worked.

My Results

I thought my results were better. The first part of the response is shown below. Copilot sees the scalar function as a problem and rewrites things. I don’t love that this is just the query and not the CREATE OR ALTER PROC part, but still.

Below this, it notes that I should test indexes and gives me the ones it would recommend. Then it goes into the fact that order by desc can be problematic. It also has a TOP 1 recommendation for the first query.

The rest of the response is OK, and gets into some general things that might help me go past the recommendations.

The prompting matters, as if I click on the “what are more perf opt techniques” I get a lot of generic stuff.

Is AI going to make you a better DBA or developer? Maybe. It will give you things to think about, but you need to test them. You might even ask a GenAI tool for help on that. I did and got this:

And below, some more steps.

Not bad, and likely what a lot of people do, though perhaps not with #5, in which HammerDB is suggested to run this proc.

Learn to use GenAI tools, learn where they help (And save time) and where they can’t be trusted. That’s likely an ongoing battle for most of us, but these tools can help us be more productive.

May 30, 2025
Changing the Paradigm of Work

I saw an article on AI usage that is based on an upcoming book that suggests redesigning the world around new tech, not adding it to existing things. The first example is how electricity was introduced to existing factories, but it only provided some incremental gains until new factories were redesigned around electric motors. There’s also an example given about reworking hotels to remove the front desk since that feature isn’t needed. Instead, people could walk in, and an employee with a tablet could find them to check them in.

I’m all for rethinking and redesigning processes. I do think we have a huge glut of software in many organizations that exists because processes have evolved across time, but not everything and we keep our old software. When we have a new need or want a new capability, we add new software (or add features), but we don’t necessarily throw out all the old software, processes, or habits. That wouldn’t be practical, often because when we implement something new, it might not meet all our needs. Or at least we don’t know it meets our needs at first.

I don’t love the examples, especially as I see organizations where Teams/Slack are heavily in use (not just in tech companies), and these tools have changed how people work. Not everyone has moved, and there is a lot of document sharing and legacy systems, but that’s also because not everyone wants to or can change at the same pace. However, lots of people have changed how they create, share, collaborate, communicate, and more because of these tools.

I also think the hotel example is silly (read the article to see more). I don’t need a desk, and often I check in and get my key without ever visiting a desk. However, I do like the desk because when I need something, I want to be able to find someone, not wander around looking for an employee or waiting for them to respond to some page. When there’s a desk I can see someone is there. I certainly don’t want a kiosk with an AI there to queue at to request a person help me.

GenAI LLMs are going to change work for a lot of people, especially those in tech. I don’t know that we can redesign the way we work now, as the technology is still advancing, and I’m not sure how we will want to integrate agents with GenAI to do work. We have to experiment and find ways to use this tech before we build too much. We also have to see it mature.

When we do, I bet many more people will start to use AI to generate code, change settings, deploy things, and more. I’m also sure that they will make many mistakes, at scale, and we’ll be using a GenAI agent to undo things, or perhaps we’ll have humans scrambling to fix systems. I wouldn’t be surprised to see the humans commonly fixing things as we might not trust the AI Agent (or our prompting) to fix our mistake.

If you could redesign work, what would you want out of an AI? Assume that it is at least as competent as someone you work with. Is there a way that a GenAI system would make your work smoother?

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

May 28, 2025
Using AI to Test a SQL Query
Someone sent me this code.
```
WITH p AS
 ( SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
 FROM wp_posts
 WHERE post_parent = 94341; )
 UPDATE p SET menu_order = RN
```
That’s not great code. In fact, it won’t run. I know that, but does an AI? Let’s see how Copilot does.

This is part of a series of experiments with AI systems.

The First Test

I entered this prompt, but accidentally hit Enter. When I then pasted in the code and hit enter, I got this:

That is just about what I would have sent the author to let them know this code doesn’t work. It’s good to see a GenAI LLM can recognize this.

I then pasted in the entire prompt which was: “give me a table with the schema to match this code: WITH p AS ( SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN FROM wp_posts WHERE post_parent = 94341; ) UPDATE p SET menu_order = RN”

The Gen AI response was polite, with this answer:

Certainly! Based on your SQL code, it looks like you need a table schema that matches the structure of wp_posts, particularly including ID, post_parent, and menu_order. Here’s a possible schema:

I then had this code, with a copy button that I could copy/paste into SSMS.
```
CREATE TABLE wp_posts (
     ID INT PRIMARY KEY,      -- Unique identifier for each post
     post_parent INT,         -- Parent post ID (for hierarchical structures)
     menu_order INT           -- Order value for arranging posts
);
```
That’s what I would have done to mock this up for testing. You might guess the next step: data.

Here’s my prompt: create some test data for the table with Ids that increment but are not necessarily sequential and post_parent values that match the ID values

That got my this response.

That’s not bad, but it’s a limited set of data. Let’s ask for more.

Below this the GenAI LLM gave me a summary that explains what it did.

Next, I wanted a test. I used this prompt: write a tsqlt test to verify that the update statement works to reset the menu_order to sequential values for a particular post_parent

I got a nice response and the code I’m showing below the image.

Here is the code:
```
-- Step 1: Create a test class
EXEC tSQLt.NewTestClass 'TestMenuOrderUpdate';
```
GO

— Step 2: Define the test
CREATE PROCEDURE TestMenuOrderUpdate.[test_UpdateMenuOrder_Sequential]
AS
BEGIN
— Arrange: Fake the wp_posts table
EXEC tSQLt.FakeTable ‘dbo.wp_posts’;

INSERT INTO wp_posts (ID, post_parent, menu_order)
VALUES
(101, 94341, 5), (102, 94341, 3), (103, 94341, 8),
(104, 94341, 1), (105, 94341, 7);

— Act: Run the update logic
WITH p AS (
SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
FROM wp_posts
WHERE post_parent = 94341
)
UPDATE wp_posts
SET menu_order = p.RN
FROM p
WHERE wp_posts.ID = p.ID;

— Assert: Check if menu_order is sequential
EXEC tSQLt.AssertEqualsTable ‘ExpectedResults’, ‘wp_posts’;

END;
GO

— Step 3: Create expected results table for assertion
CREATE TABLE ExpectedResults (
ID INT PRIMARY KEY,
post_parent INT,
menu_order INT
);

INSERT INTO ExpectedResults (ID, post_parent, menu_order)
VALUES
(101, 94341, 1), (102, 94341, 2), (103, 94341, 3),
(104, 94341, 4), (105, 94341, 5);

— Run the test
EXEC tSQLt.Run ‘TestMenuOrderUpdate’;
This is where I stopped. This code runs, though it’s brittle in that the Expected Results table is a real table and not a temp inside the test. I can easily edit that and likely quicker than the GenAI can. It’s copy/pasting some code for me from one spot to another. I also need to add a CREATE OR ALTER to the proc.

Summary

A quick use of an AI that easily saved me 5-10 minutes to mock of a scenario and show someone what they were doing wrong and also testing their code. I mock up things regularly to test them, and often do something like this to answer questions. This is a good place where AI can save some time, and might be worth using. I’m also glad to see it recognize and write some decent SQL code for me, especially the insert statement for test data.
May 28, 2025