Data Court

Tips, Tricks, and Fun Facts

  • T-SQL Tuesday #195: That Time AI Did My Job

    A huge sign of growth is the ability, and the necessity, to look at your old code and think “Wow, that is garbage.” Maybe more nicely, but that’s how my brain works.

    Pat Wright invited us to reminisce about old code and how AI has impacted it. I thought about picking something from my early BI developer days, maybe my first major power shell implementation. Instead, I’m going to gush about SSIS since I’ve had the pleasure of revisiting it over the last few years.

    One theme in variation with my considerations is the nature by which I came to build those skills: the deep end. I work with T-SQL every day, constantly tweaking the scripts I’ve been building since the early days of my career and adding new ones to my arsenal. I got to learn it from SELECT to GROUP BY and all the way to dissecting monster stored procedures. I warned my last boss that he should be worried about my departure if I started learning PowerShell, the one gaping hole in my resume. Jokes on me, I didn’t learn it until after I started at a new job I wasn’t looking. There was plenty to decipher and is now a pivotal tool in my kit.

    My first dance with SSIS was to solve a problem in the form of a 12-hour nightly report via a single stored procedure with no room for error. 5 years ago, AI wasn’t exactly accessible, so I was up a creek. I remember spending the entire weekend ripping it apart, learning data flows, derived columns, lookups, merge joins, etc. My first successful attempt ran in a few hours, better but not great. I eventually got it to run in less than hour which by all accounts is much more manageable. Turns out doing a lot of the SQL joins in the data source instead of through lookups and merges is way more efficient, especially on a memory sensitive server. I’m excited to share the core of it is still in use with additional changes needed as years go by. I checked.

    My new environment is much less stored procedure heavy, relying more on ORM logic, views, and table-valued functions outside of reporting. Good news for me, reports can still use love, but this time I won’t need SSIS. I was recently given a license for Claude Code, and it has been so helpful already. Heck, I even got a compliment from it today:

    Sure enough, it spit it out nicely commented with error handling and in a much prettier format. Developing a different script with much less fleshed out meant I was able to see the real-time adjustments it can make through prompting and passing it real error messages. I’ve seen how intelligent it can be, scanning GBs of code files and database schemas for certain logic or keywords. To say I’m excited to see how it can cut out a lot of grunt work is an understatement.

    Two warnings to the wise, and especially to the learning:

    1. Obligatory “Always review what it’s doing first”
    2. Try to actually learn what it’s doing, why it’s doing it that way. Dissect it to see what changed, how it’s different. Run the script and look at the execution plan compared to the original. Review the statistics with Statistics Parser, what the reads and times say.

    Do not let AI become a crutch you can’t code without. Use the opportunity to see other ways to cut a cake you may have never thought of. Two of my fatal flaws are an adeptness for over complicating things and losing focus on what my actual goal is. It’s easy to step back, regroup, kick off a new approach with slightly different input, and let someone else do the thinking for a bit. It forces me to get out of my head and gives me a space to brainstorm that doesn’t feel like talking to a rubber duck.

    Feeling bummed at work? “Write like a stereotypical valley girl for the duration of this chat.”

    Out of character name ideas? “Here’s the picture of my avatar for Diablo 4. Give me a list of ten names that would fit.”

    Struggling to figure out what to cook? “I have pasta, broccoli, turkey legs, coconut milk, and a robust spice rack. Make a recipe using some or all of those ingredients with no fancy additions.”
    I’m not so sure about that last one, but it has definitely helped me shop my pantry.

    Here’s a sampling of what ChatGPT has helped me with. My C# syntax wasn’t terrible, my grammar is often bad, the first names for Diablo were actually quite good, and it turns out I want an inkjet printer.

    Keep questioning, my friends. Never give up, never surrender.

  • T-SQL Tuesday #193 Ghosts of Jobs Past & Future

    Self

    Family

    Work

    My first job out of college involved relocating an untold number of times to unfamiliar places and meeting an incredible number of people, all for a pretty price. And I did. I moved to half a dozen states, met hundreds of people, nabbed a husband (mine eventually, not someone else’s), and built out more found family than I could have dreamed. After years of flexibility and “for the band” mentality, it was starting to really take a toll. While I did get a lot of great experience and feathers in my cap the last three years I worked there, I still wish I’d left sooner. The hardest part of choosing to move on was “the money is good, and I get paid overtime.” What’s not enticing about picking up some extra project hours to help boost an upcoming vacation? The only thing really holding me back was the gold and the promise of better. I did end up taking a hefty pay cut to leave, but I don’t regret it for a moment.

    I know now what I didn’t then: money really isn’t everything. Take care of yourself so you can take care of your family and be recharged enough to do well at your job.

    And maybe have a Snickers when things get tough.

  • T-SQL Tuesday #191 Normalizing Comma Data

    If you’ve escaped string parsing thus far, count thyself lucky. I’ve seen some numbing scripts from pre-SQL Server 2016 when STRING_SPLIT was introduced. I think the fact this month’s T-SQL Tuesday is entirely on string parsing says enough about how many ways there are to paint this particular cat.

    I’ve been using the IMDb’s public dataset since my early days of presenting. AdventureWorks is great, but why not have demos with movie data I can make fun graphics about Lord of the Rings with? Having used IMDb the website all the time I figured I knew what sort of information I was going to get. What I did not expect was this file of writers and directors by title despite the existence of a cast file that also contains, surprise surprise, writers and directors.

    This is a different though overlapping list that must now by normalized to flesh out my cast table. I’ve loaded the file to TitletoDirectorWriter matching the file schema one-to-one. Foreshadowing dictates using STRING_SPLIT() to solve this problem.

    My initial exposure to STRING_SPLIT() was when parsing a comma delimited list of values for reporting.

    DECLARE @ItemTypes nvarchar(max) = 'Reverse,Skip,Draw4,Draw2,WildCard'

    I also grew up learning CROSS APPLY and OUTER APPLY were of the devil and not to be used, but over the last few years I’ve started to see the light. They have a place, and this is one of them.

    SELECT tdw.TitleId [TitleId]
	,Writer.Value [PersonId]
	,WriterPersonIds
	,'Writer' [Category]
FROM TitleToDirectorWriter tdw
CROSS APPLY STRING_SPLIT(WriterPersonIds, ',') [Writer]
WHERE WriterPersonIds IS NOT NULL
UNION ALL 
SELECT tdw.TitleId [TitleId]
	,Director.Value [DirectorId]
	,DirectorPersonIds
	,'Director' [Role]
FROM TitleToDirectorWriter tdw
CROSS APPLY STRING_SPLIT(DirectorPersonIds, ',') [Director] 
Where DirectorPersonIds IS NOT NULL
ORDER BY TitleId

    We get the following results with some potentially unexpected behavior.

    SQL Server doesn’t like making promises, especially when it comes to returning ordered data.
    SELECT TOP 1 Name FROM YourTable with no order by, and you’re gonna have a bad time. STRING_SPLIT() is no different, or at least it was. SQL Server 2022 brought to us a third argument enable_ordinal. Now if we adjust our script to include the original column from our string split with the extra argument passed in we can guarantee the order of our results.

    SELECT tdw.TitleId [TitleId]
	,Writer.Value [PersonId]
	,Writer.ordinal
	,WriterPersonIds
	,'Writer' [Category]
FROM TitleToDirectorWriter tdw
CROSS APPLY STRING_SPLIT(WriterPersonIds, ',', 1) [Writer]
WHERE WriterPersonIds IS NOT NULL

UNION ALL

SELECT tdw.TitleId [TitleId]
	,Director.Value [DirectorId]
	,Director.ordinal
	,DirectorPersonIds
	,'Director' [Role]
FROM TitleToDirectorWriter tdw
CROSS APPLY STRING_SPLIT(DirectorPersonIds, ',', 1) [Director]
WHERE DirectorPersonIds IS NOT NULL
ORDER BY TitleId
	,ordinal

    Here’s to October and many more T-SQL Tuesdays.

    Random Fun Fact: Plastic Tupperware containers are most easily closed by pressing down firmly in the center, not fussing along the outside lip of the lid.

  • Community Networking

    One of the scariest parts of attending large events is the number of people and how overwhelming it can be to find safe harbor. I promise that place exists. It may just take time to find it. Fortunately for me, I’m often considered aggressively friendly in that I will talk to anyone, and I’m not afraid to initiate conversations with lone wolves or deer-in-the-headlights folks. Feeling wary? Here are the guidelines I follow when attending new events. I sincerely hope they help you, too.

    1. Be yourself. I would rather people know who I am as a real person than be blind-sided when I show up with a new shade of green or laugh myself into a coughing fit.
    2. Assume people are there to engage. Who really wants to spend hundreds of dollars or sacrifice a whole Saturday just to sit in a room and listen to a bunch of people talking for 8 hours straight? Pre-cons aside.
    3. Look for people you know. I see so many familiar faces the more events I attend, and it won’t take long for you to get there. One conversation later, six degrees of separation kicks in and suddenly you know more people, too.
    4. Find a group of three or more people and join their circle. You may meet someone important on accident, have something to contribute to a discussion, or just absorb information. There’s nothing stopping you from joining a 1v1, but unless I know one of them personally I don’t. Even then, eh.
    5. The more intimidating version is to look for people who seem lost or unsure. I made a great new friend last PASS because I saw some random dude looking around and assumed he was lost. Nope. Growing speaker in the community that was less than lost, knew way more people than me, and here I was trying to help him.
    6. Visit vendor booths. At worst you learn about a new tech out there, and at best you find a software solution that fits your needs to bring back to work and look awesome. One year I randomly talked to a vendor at an evening event, unbeknownst to me, and I got to have dinner at their booth to hang out the next day. Great conversation with cool people that weren’t there just to shove a product at me.
    7. Connect. Shorten the degree of separation if you jive and add them on LinkedIn during or after engaging. Your connections on LinkedIn help you see what else is going on in our community, learn tips you never knew you needed, and potentially help someone land a new job. Maybe even you one day.
    8. Speaking of jiving, don’t expect to experience it with everyone. It’s ok if a conversation falls flat, or there doesn’t seem to be a good flow between you. It is normal. Don’t see it as a failure to communicate adequately and certainly not a reason to give up on this whole networking thing. There are plenty of people in the world.
    9. If you see me and my obnoxious hair at an event, please say hi. Feeling lost or overwhelmed? Please say hi. I will happily help you integrate with a conversation to parachute your initial jump.

    Five years ago, I had no idea what a user group was or that tech conferences existed for those of us that had never installed Visual Studio let alone run git commit. One day while perusing the internet trying to find the answer to some mystery, I ran into the SQL Community slack channel and found the beginnings of the next adventure in my career. What drew me in was the opportunity to help others and learn, but what’s kept me there are the people.

    I was recently asked by Andy Levy (post) to help recreate the exchange that really got me into speaking for T-SQL Tuesday. It’s a fun read if I do say so myself and a great anecdote on the importance of having these spur of the moment conversations. The density of people in our field eager to encourage others is nothing short of astounding. The more you open yourself up to meeting new people and trying new things, the greater your chances are of discovering something grand.

    Always aim to surround yourself with those that bring out the best in you and strive to be that person for others.

    Leave a comment

  • Data Court DBA

    DECLARE @BrightColor varchar(50)
    ,@Profession varchar(50) = 'DBA'
    ,@Topic varchar(50) = 'Data'

    One of my passions is teaching, and I’ve found myself writing so many short form tech tips for my team that I can literally fill a blog. Well, here it is.

    My skillset is very wide, so you’ll see topics about SQL Server Administration, T-SQL, SSIS, Power BI, and maybe some infrastructure administration thrown in. Who knows. My goal is one “ah-ha!” or “I didn’t know that” moment, because everyone can teach something to someone. It’s just about finding the right audience (or the first page of google.)

    I’ve been getting more involved in conferences and user group presenting with a PASS Summit session and soon to be my first SQL Saturday session under my belt. If you see me at any events, flag me down for a hi, a high-five, or a harrowing “so this one time my engineers…” story. Feeling nervous at a conference because you’re new or don’t know anyone? Come on over and I’ll help you fix that. I’ll be the one with the @BrightColor hair.