Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #198–Change Detection

    This month we have a new host, Meagan Longoria, who graciously agreed to help me this month. I’ve known Meagan for a number of years and she’s been a person whom I’ve asked questions about data visualization and analysis in the past. I was slightly surprised by the topic this month, but only slightly. I’ll write my answer below, but if you want to try blogging and host a future month, let me know.

    Change Detection

    The topic this month is change detection, which is important for efficient ETL work, but also for other areas, such as auditing. I haven’t tended to work in high volume systems where we did a lot of ETL and needed to very efficiently detect changes. Most of the time I’ve had ETL pipelines they were busy, but not excessively so.

    As a result, in the past, I’ve often used a roll-your-own approach in the SQL 6.5->2008 era. As I’ve worked on those systems, we’ve usually used a simple update or modified date in the table that tracked when something was altered. By knowing the last time a pipeline ran, we could gather all data from that point forward and extract it.

    We used a similar approach to send a lot of emails from SQL Server Central years ago. That can work well, and as long as you track the last execution of your pipeline process, whatever that is, you minimize the data being transferred.

    A Modern Approach

    I ran into dbt a few years ago and did a one day class on how it works. It was interesting to me, and I could see the appeal. Recently I had a conversation with John Miner, who’s used it in his Fabric Modern Data Platform series. I would be very tempted to use dbt, in conjunction with a modified date as described above, to ETL data around today.

    However.

    I have been seeing that Change Data Capture (CDC) is being used by lots of products these days. It’s behind the Fabric mirroring, Oracle has used it for years, when you look to move data into Databricks, CDC is common, it seems like it’s everywhere. 

    If I were going to be regularly moving data in 2026, CDC seems like something I’d experiment with and test, since it’s a known technology that works across many platforms and there is a lot of knowledge out there on how it works. AI can certainly help with experiments, and with understanding the overhead on your system, because there is some overhead.

    I haven’t looked at the new Change Event Streaming, though I’m always wary of anything that limits me to one cloud. The more generic CDC, with AI assisted configuration and maintenance, seems like a better approach.

  • Impactful Sessions I’ve Seen: T-SQL Tuesday #196

    This month I’m thrilled that Steve Hughes is hosting. I’ve read this Data on Wheels blog for a long time and I appreciate all that he does, especially given his struggles with ALS. I kicked myself a few times for not asking him to host earlier, but I’m glad I finally did.

    I also was honored to meet him a few weeks ago and spend a little time with him.

    20260329_184753

    With that, let’s get to Steve’s invitation.

    Lasting Memories from Events

    These days I don’t go to a lot of sessions at events. It’s not that I can’t learn from others, or be inspired, but I find myself often viewing the time spent with others as more valuable. I can (and often do) work through articles, tutorials, and other resources online. For me, the time talking to others if more valuable than sitting in sessions.

    However, I wasn’t always like that. Early on in my career, and even throughout, I went to a lot of sessions to learn things. Over the years, a few things have stood out to me. There is some recency bias here, and I’m sure I’m forgetting about some great sessions along the way. I don’t always remember titles, so forgive if I’ve made a mistake.

    Tempdb in Early SQL Server – This was at the very first PASS Summit in Chicago, where Kalen Delaney have a talk on tempdb. I remember this because we had lots of contention in tempdb on SQL Server 6.5 and I was struggling. Finding solutions was one of the main reasons I got my boss to pay for the conference. I know I learned a few things and worked to restructure some queries after this. I even got to ask Kalen a question, which was a thrill for me.

    Reporting Services CI – I think this was a Nigel Sammy session, but it might have been Roy Ernest or another friend. I was working to get DevOps style checks for SQL code at the time and I wondered how someone would do this for SSRS. It was an interesting session as it reaffirmed a lot of my guesses for how to approach this, but taught me an interesting way to try and verify parts of reports while ignoring others. The session focused on ensuring your clients were happy with report data more than visual layout.

    The BI Power Hour – I went to a few of these, but the first one was with Donald Farmer, Euan Garden, and others at TechEd in 2003 or 2004. It was wildly entertaining and I appreciated the humorous approach to showing ways to use the BI platform. While I am not quite as entertaining, I try to add a few minor funny things in sessions.

    Basic Backup for SQL Server – Sean McCown gave this at a few SQL Saturdays. I am not sure if I saw him in Dallas or elsewhere, but I think I was sitting in the room and decided not to go anywhere. Instead, I was curious what Sean might cover that I didn’t know. While I knew most of the material, there were a few tweaks in settings that made a different to performance. This session reminded me there is always more to learn, even when you think you know a topic well.

    Clustering in 60 Minutes – Early in the SQL Server Central days, Brian was doing a lot of HA for his company. He presented this session at a PASS Summit where he built a SQL Server 7 or 2000 cluster live onstage in 60 minutes. It was cool to see and it reminded me that I should practice my demos and make them smooth.

    Live Encryption Changes – Simon Sabin gave an interesting talk at SQL Bits where he showed a live app running against his database and how he could implement encryption without breaking the app. The app just ran a workload against his database in a loop. He made changes to the db in the background without causing errors, which was cool. I enjoyed learning the encryption techniques, but more this inspired my Architecting Zero Downtime Deployments, where I wrote my own app to do this.

    There are plenty more, but I’ll stop here.

    There’s always a lot to learn, and going to sessions is a great way to get inspired and help you build your own solutions.

  • T-SQL Tuesday #196: Taking Risks

    This month we have a new host, James Serra. I’ve been trying to find new hosts over the last few years to keep this party going and to expand the ways in which people look at database work.

    There are definitely less bloggers, but in the age of AI, I think the more you can stand out and show that you add value, that you think about things, that you can spot AI mistakes, the better off you are, so keep blogging an encourage others to do so.

    This is a great invite, and I’ll give you my response below.

    A Big Leap

    I had changed jobs a few times after I moved to Colorado. I worked at an established small company, which was still a bit of a startup, and after sleeping in my office 8-10 times in a year, I looked for another job. Another startup, which failed, left me looking again. I got a job at JD Edwards, which turned into PeopleSoft after an acquisition. I was promoted, and in a good place.

    At the time, I’d been running SQL Server Central on the side with Andy and Brian for a couple of years. We were making it work, but it was stressful in that we spent time at night, on weekends, and during breaks keeping it growing. We were feeling the stress and decided someone needed to work at it fulltime. We’d made a big sale, so we had some cash to give us stability, but if things went poorly, this was about a half a year’s salary for any of us.

    We debated it a bit and I decided to make the leap to working for the company fulltime. I took a small pay cut to do so, but we expected a monthly dividends to make up for most of the decrease in pay. Not all, but most. The main reason I decided to make the leap was that my wife was working fulltime and provided our health benefits. That was a cost Andy or Brian would have needed to shoulder themselves.

    This was a risk. At the time, I worried about my skillset. Would they atrophy? If this didn’t work, could I go back to work as a consultant or fulltime DBA? Would I miss out on the learnings and growth that come from being involved fulltime in projects for an organization?

    Looking back, it doesn’t seem like a big risk, but at the time, in the 2003 timeframe, it felt like a major leap away from the security and stability of corporate work. Sure, I’d changed jobs in the past, but I always had a strong track record of delivering results in a position. I was giving some of that up and the longer I was away, the more I was worried about coming back.

    Over time I realized that a lot of what I do here with testing scenarios, mocking situations, and working through the challenges people face is the same type of work that I would do in a corporate environment. I don’t have the pressure from a manager, but I often put that on myself, so it’s very similar.

    This was a calculated risk, but still a work. Fortunately, it was a one that worked out well.

  • Aging Code for T-SQL Tuesday #195

    It’s that time of the month again, with T-SQL Tuesday coming along. I managed to not forget about this and checked with the host. He had an issue, but fortunately I got a friend to step up.

    This month Pat Wright has an interesting question, asking how your code has aged. He and I have had a few conversations lately about getting older and when I asked him to host, this was a perfect choice.

    I’m definitely getting older, but what about my code?

    25 Years Later

    Actually a little more, but I wrote a series called “Tame Those Strings” for Swynk a long time ago. That became Database Journal, but during the switch, they stopped paying us authors. A few of us started SQL Server Central and we went live 25 years ago.

    In that piece, I referenced the oldest article, which is Tame Those Strings Part 4 – Numeric Conversions. There’s also a part 3, but in those pieces, is that code still useful?

    A bit.

    These are basic articles looking at string functions that are still heavily in use today. The idea of cleaning phone numbers using REPLACE is still something we might do today. If we are using SQL Server 2025, there are additional functions, but I still see a lot of code that still use multiple CHARINDEX+SUBSTRING or REPLACE functions.

    I asked the Prompt AI if it could do better.

    2026-02_0091

    It gave me two pieces of code. The first is nested REPLACE() statements. This works, but I find this hard to read. I’d rather have separate statements, for ease of maintenance.

    2026-02_0095

    The second is a single statement, using a CASE and STUFF and XML to clean things. I like this, thought it’s a semi-complex way of doing things. However, it works.

    2026-02_0096

    Has the code aged well? I think it’s OK. There are better ways, as shown with the STUFF/XML version, which wouldn’t have worked in SQL Server 2000. Still, the use of REPLACE is a common technique still used today.

    For part 4, with the use of LTRIM and STR(), today we have FORMAT, which is cleaner. However, it’s likely less performant. In a simple test of 500,000 values, the FORMAT takes over 600ms to return the results while my LTRIM/STR combination consistently runs in the 460ms or quicker range.

    I think my code aged well.