Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #200: When I Look at a Query …

    This month is a milestone for T-SQL Tuesday. It’s number 200, which doesn’t sound big, but this is a monthly party (started by Adam Machanic). We have 12 blog party events a year. 200 means this has been running for almost 17 years (16 years and 8 months).

    I don’t care who you are, that’s impressive. Think about where you were, what you were doing, and what was happening in 2009.

    I haven’t been running it that long, but I am glad I took it over from Adam and have kept it going. Thanks to everyone who writes and reads the posts and especially the hosts.

    I tried to get Adam to host, but he declined. Fortunately Brent Ozar stepped up with a great initiation. My response below.

    At First Glance

    There are two things that immediately stand out to me when I see a query and create concern.

    1. cross joins
    2. functions in the where/on clause

    While there are other things I might see, these two stand out and usually I can guess there will be issues.

    For cross joins, I don’t see this as much when people use SQL Prompt or some other helper because they tend to use inner/left outer/right outer explicitly, or cross join. If you explicitly use a cross join, I might ask why, but these clauses require an ON clause, which means you’re deciding to join tables.

    Where I see people using old style joins, like this:

    select *

from a, b

where a.id > 23 and b.saledate > current_date

or (a.id is null and b.saledate is null)

    I get worried. This happens in Oracle, and PostgreSQ, and it’s easy to forget to join a and b, especially when there are multiple tables. Usually cross joins happen with legacy join conditions.

    The other area is using functions in the WHERE clause. A common example is

    select *

from customer

where upper(customername) = ‘Steve’

    This function in the WHERE clause ruins the ability to see the data. The index is something like (‘Adam’, ‘bill’, ‘Steve’, ‘WILLIAM’). This can’t be used when the UPPER is applied. This often results in more reads, more scans than a system might otherwise take.

    There are plenty of other issues that can indicate performance issues, but these two are the ones I’ve often run into and the ones that would have helped 2004 Steve write and review better code.

  • Un-Migrating From the Cloud: T-SQL Tuesday #199

    This month we have a very interesting invitation from Koen Verbeeck. He has hosted once before, and agreed to help me out this month by tackling another topic. We’ve shared a few beers in the EU, though not in some time, so I hopefully will get the chance to buy him a pint and thank him for this month’s invite.

    He was inspired by another friend, Alexander Avidsson, who wrote about skills and the cloud, but with an interesting take. This month’s invite is about moving back on-premises from the cloud, which is something I’ve had a few customers do, or start doing. Nothing is quick when migrating systems, either to or from the cloud.

    Here’s my take.

    How Easy Is It To Un-Migrate?

    I decided on a fun title here, since so many people talk about migrating to the cloud. Is going back on-premises an un-migration? Or a re-migration? Just a migration? I won’t worry about the semantics.

    I work with a lot of different customers at Redgate Software. Whether they are discussing development topics (Prompt/Toolbelt), deployment issues (Flyway), production stuff (Monitor) or compliance (all of the above + TDM), I find that most of them are still rooted in an on-premises mindset. Even when they’ve migrated to the cloud, it’s often lift-and-shift, with VMs running in AWS/Azure/GCP. In that sense, they still tend to manage things in an on-premises way.

    They’re more comfortable with those skills and more confused by cloud auth systems, still. In 2026.

    I think that’s still the norm and it’s easy to think that most people work in the cloud all the time. Some do, and I think most people have some familiarity with one of the major cloud systems, but I would expect that lots of people would love to come back on-premises.

    Is it easy? No. It’s another migration, and while you might find it easy to re-provision hardware (whether purchased or rented from another provider like Rackspace) there are still major data movement challenges in the db world. How do I sync systems? Can I get log backups? What type of quiescing and cutover is there?

    The tech stuff, matching versions, etc. is likely easy. After all, most of us don’t use the latest and greatest functions from SQL Server, so moving back to on-premises likely just works. If you are in the IaaS world, this is simple, other than the latency of copying data down (and explaining the egress charges).

    I actually think customers might gain some efficiencies from moving systems with known workloads and costs back on-premises. Especially those that create lots of tickets for DBAs or developers. Any time spent moving back will come back in the skills that so many have with on-premises systems.

    The one downside I think might cause some issues is HA. It’s easy in the cloud, and hard on-premises. Those are skills that some people likely need to brush up on if they don’t have a significant HA footprint with VMs.

  • 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.