Press "Enter" to skip to content

Day: April 28, 2025

Restoring Databases using Change Data Capture

Published 2025-04-28 by Kevin Feasel

Tim Radney restores a database:

I have recently had the privilege of working with multiple clients who have been taking advantage of Change Data Capture “CDC”. Change Data Capture is a feature that utilizes SQL Server Agent to log inserts, updates, and deletes occurring in a table. It makes these data changes available to be consumed in a relational format. These changes are captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables.

Change Data Capture is very often utilized for ETL projects or for data extracts into a data warehouse, data lake, etc.

Click through for the scenario and how you can keep CDC going after restoring a SQL Server database.

Leave a Comment

Digging into the Kusto Detective Agency

Published 2025-04-28 by Kevin Feasel

Tom Zika becomes a gumshoe:

We need to answer this question:

Who is the detective that earned the most money in 2022?

We can see that only one table (DetectiveCases) was added in the ingestion section. Let’s take a look at its data.

The Kusto Detective Agency is a great way to apply KQL skills. I’m not sure it’s a fantastic experience for somebody with zero KQL knowledge, but if you’ve messed around at least a little bit with the language, this is a fun way of applying those skills.

Leave a Comment

Incremental Backups in PostgreSQL 17

Published 2025-04-28 by Kevin Feasel

Dave Stokes takes a backup:

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use.

This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies.

Click through to see how it all works. But also please test your backups, because if I were to modify the adage, I’d say that a DBA is only as good as his last tested backup.

Leave a Comment

Performing a Quick Filter via APPLY

Published 2025-04-28 by Kevin Feasel

Shane O’Neill tries something out:

Now, SQL Server doesn’t have the filter option, but we can do some pretty weird things, like a SELECT...WHERE statement with no FROM clause.

SELECT

    a,b,c,

    [filter?] = (SELECTb WHEREb > 11)

FROMz;

GO

It turns out that this works, but when you try to aggregate the results, it doesn’t work the way Shane expected. Using the APPLY operator does help here, so click through to see how that works.

Leave a Comment

A Performance Comparison of Temp Tables and Table Variables

Published 2025-04-28 by Kevin Feasel

Mehdi Ghapanvari compares two ways of holding temporary data:

Do you ever wonder why your query is slow when you use a table variable in a join operation? If you are interested in why this happens and how to improve query performance in such a scenario, keep reading to learn more.

Click through for the full article. This focuses entirely on classic table variables and not memory-optimized table variables, the latter of which can significantly improve performance in specific circumstances.

Leave a Comment