Tag: Redgate

I used Claude to build an application that loaded data for me. However, there were a few others things I needed done to help me get the data ready for analysis. I could have used Claude (or another human), but this was a place I’d need to do a lot of directing.

I did, however, use Prompt AI to help me get this moving quicker and start some analysis. This post covers the way I got assistance inside SSMS from Prompt AI.

This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag. This is part of a series of experiments with AI systems.

Adding New Structures

As a part of my data analysis, I needed to tag sessions with topics. This wasn’t really done as a part of most SQL Saturday events. While there might be tracks, often the sessions aren’t categorized with tags. It’s a pain, most organizers and most speakers don’t want to bother.

Plus, search works well on a website. For data analysis, not so much.

I decided to use Prompt AI to help a bit at this point, since I was low on credits.
I asked it to add a structure. Possibly I could have written the code in not much more time than it took for this prompt, but here I get to let an assistant take a stab and then critique the work. Here’s my prompt to SQL Prompt AI:

Here are the results. This has the PKs, FKs, and indexes, some of which I might have forgotten. I don’t love names like “description”, but that’s fine. I could easily fix that.

-- Create a table to store tags
CREATE TABLE dbo.Tags
(
    TagID INT IDENTITY(1, 1) PRIMARY KEY,
    TagName NVARCHAR(100) NOT NULL,
    Description NVARCHAR(500) NULL,
    CreatedDate DATETIME
        DEFAULT GETDATE()
);

-- Create a junction table to link sessions with tags (many-to-many relationship)
CREATE TABLE dbo.SessionTags
(
    SessionID INT NOT NULL,
    TagID INT NOT NULL,
    PRIMARY KEY (
                    SessionID,
                    TagID
                ),
    CONSTRAINT FK_SessionTags_Sessions
        FOREIGN KEY (SessionID)
        REFERENCES dbo.Sessions (SessionID),
    CONSTRAINT FK_SessionTags_Tags
        FOREIGN KEY (TagID)
        REFERENCES dbo.Tags (TagID)
);

-- Create index to improve query performance
CREATE INDEX IX_SessionTags_TagID ON dbo.SessionTags (TagID);
CREATE INDEX IX_SessionTags_SessionID ON dbo.SessionTags (SessionID);

Now, I need to analyze the data.

Fortunately, Prompt AI helped me. Below this DDL, was a snippet to test my tags. I had this code listed:

SELECT 
     s.SessionID, 
     s.Title, 
     STRING_AGG(t.TagName, ', ') AS Tags
FROM 
     dbo.Sessions s
LEFT JOIN 
     dbo.SessionTags st ON s.SessionID = st.SessionID
LEFT JOIN 
     dbo.Tags t ON st.TagID = t.TagID
--WHERE Title LIKE '%n rds%'
GROUP BY 
     s.SessionID, s.Title;

I used this to check my tags, which were non existent at this point, so I had all NULL values in the Tags column. Fortunately, I know how to write some code, so in another window I wrote this code, which inserts data, but doesn’t create dups since I might have dups based on my LIKE clause catching the same session twice.

I also get the list of current tags, so I could change the number used in the insert as needed. There are more elegant ways to do this, but I wanted to get something done.

INSERT dbo.SessionTags
(
     SessionID,
     TagID
)
SELECT SessionID, 10
FROM sessions WHERE Title LIKE '%n rds%'
   AND sessionid NOT IN (SELECT sessionid 
        FROM dbo.SessionTags 
     WHERE TagID = 10)

SELECT @@rowcount
GO
SELECT top 30
*
FROM dbo.Tags

There was a sample INSERT statement for tags as well, so I modified it to use tags I cared about. Then I started running my test query to look for NULL values and start filling them in.

Here’s a look at a run. I’ve added some tags, but there are some nulls. There are also multiple tags for some sessions. I added the description field as well, but for most of the data, this doesn’t exist, so I don’t have it. Yet. That’s another project.

Line 63 is for Snowflake, which isn’t a tag. So I edit my commented out code to include Snowflake and then execute it. This is commented, so as I hit execute it doesn’t run automatically.

Now I get a list of tags and use that to edit my numbers in the SessionTags insert statement. In this case, Snowflake is number 17, so I change the insert to that, and edit the LIKE statement. This will add that tag to all sessions with Snowflake in the title.

I repeated this for a number of sessions. Below, I’ve re-run my tag query and now we see Snowflake is added.

Why I Didn’t Use an AI for This

I built an application that loaded this data with Claude Code. I could have asked Claude to add the tags as well, but I didn’t have any data to put in there. I wasn’t even sure what I would do, especially as a lot of these sessions aren’t really sessions. Notice the timings above, the breaks, the panels, etc. There are also lunch breaks and other items that aren’t really sessions.

Claude could have cleaned this data. However, I want to be sure of what I’m removing. Having Claude write the delete (or run a select first) and then ask me what to do doesn’t seem to be a good use of its cost or my time.

I still need to be the human in the loop. There were some times I ran a select for certain words and then check the list before adding the tags. For example, here was what I did for Snowflake.

All those are good, but when I ran a search for Agent, I found mostly AI based results, but a few were SQL Agent sessions. Tagging those as AI wouldn’t make sense, so I had to find a better way to update the data I needed updating.

Summary

SQL Prompt didn’t do a lot here, but it did quickly get me moving on the task I was focused on rather than the details to support it. I could have written the DDL, but it would have taken focus away from me in thinking about tags. This did as good a job as I could do, or more importantly, as good a job as I needed.

It also gave me a query and insert statement to get moving, again, reducing my mental load. I could have written that query, but I would have spent a few minutes doing it rather than thinking about how to assign tags.

Ultimately I think this was a good use of AI, saving my time and energy, allowing me to focus on the task I was trying to accomplish without distraction.

I was looking back at my year and decided to see if SQL Prompt could help me with some analysis. I was pleasantly surprised by how this went. This post looks at my experience using this to help me write a few queries.

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

New York City

This year was a big one for me and New York City. By my count, I went there five times. I wanted to see if that was right.

I started by loading up a bunch of travel data I keep into a database. I do this to keep an eye on where/when I’m going places, so that I have a few of how busy I’ll be. In this case, I loaded data into a table. Here’s a short sample of data.

I started by asking Prompt AI to write me a query. Here’s the prompt:

Simple enough. I could have written what it gave me, so I asked for more. Since I’m not tracking trips, but where I am on days, I needed something better. My prompt is what I might express to someone else, non-continuous trips.

I got a 4 back from more complex code, which is looking for entries with the city being NYC or a variant. It then adds a LAG(), which is what I was thinking before I decided to let Prompt do this. Notice above I was already asking for dates.

I got my dates, but still 4 trips. I know I flew to the NYC area more times. In looking at the dates, I realized that one of the trips, Mar 31-Apr 2, was only to Jersey City. So I had asked for that above.

You can see more more complex code, which adds in New Jersey. The results were interesting. This has 6 trips, because on one another trip, I went to New Jersey for a day. I had forgotten that one.

Checking Countries

I wanted to do some country analysis. I knew there was a bunch of country data from 2025 in there, so I wrote a simple query. That gets me some data, but it’s a bit of a mess. I really want to know when I visited countries.

I used PromptAI with a simple request.

I got some results back, which were good, but included the USA. I then asked to remove this and you see the result.

This was a good query, overall, so I clicked the “Optimize SQL” button. I got these items added as comments.

There isn’t a lot to do here, but I created the index. The “optimize” had also added a FORCESEEK, which I didn’t realize at first, so I removed it.

Eventually I added back the USA, partially as this improves performance, but also, with the results, I got a good look at travel patterns. In this case, I can see when I’m in the USA and when I’m not. I liked that I had asked for the trip duration, but the GenAI also added days traveling on the trips, which was fascinating.

The final query is here:

;WITH TravelCTE
AS (SELECT Country,
            TravelDate,
            DaySpentTraveling,
            ROW_NUMBER() OVER (PARTITION BY Country ORDER BY TravelDate) AS RowNum
     FROM dbo.Travel WITH (INDEX = IX_Travel_YearCountry) -- Use existing index to improve performance
     WHERE TravelDate >= '20250101' -- Using efficient date literal format
)
SELECT t.Country,
        t.TripStartDate,
        t.TripEndDate,
        t.TripDuration,
        t.TotalDaysSpentTraveling
FROM
(
     SELECT Country,
            MIN(TravelDate) AS TripStartDate,
            MAX(TravelDate) AS TripEndDate,
            DATEDIFF(DAY, MIN(TravelDate), MAX(TravelDate)) + 1 AS TripDuration,
            SUM(DaySpentTraveling) AS TotalDaysSpentTraveling,
            DATEADD(DAY, -RowNum, TravelDate) AS GroupingKey
     FROM TravelCTE
     GROUP BY Country,
              DATEADD(DAY, -RowNum, TravelDate)
) AS t
ORDER BY t.TripStartDate;

Summary

This was an interesting experiment in having an AI help me do some data analysis. I could have written these queries, but it would definitely have taken me as long as it took to ask the AI and write this post to do so. I’d be messing with data, double checking myself, and trying to decide what I wanted.

I also found it interesting to have the AI write the code and I could think more about the data being returned. I found a few data anomalies that I corrected along the way.

Prompt AI is proving useful, though when I was going back and forth a lot, I got this message.

There are limits to how much and how often you can query.

Video Walkthough

I’ve tried to duplicate this in video below.