The Bit Bucket

Another powerful transformation in real-time processing is the union. A union allows us to take multiple input event streams and merge them into a single, consolidated stream. This is especially useful when we have similar data sources that we want to treat consistently in downstream processing.

A common scenario is application logs. You might have several applications, each producing its own stream of log events. Instead of maintaining separate pipelines for each one, we can use a union to combine them all into a single log stream. From there, we can apply filters, mappings, or aggregations in a single place, which makes management much easier.

2026-04-23

I fell for this one this week. If you execute the following code in SQLCMD mode, what would you expect the output to be?

:SETVAR PrincipalServer WINSERVERBASE
:SETVAR MirrorServer WINSERVERBASE\\SQLDEV02
:SETVAR WitnessServer WINSERVERBASE\\SQLDEV03

:CONNECT $(PrincipalServer)
SELECT @@SERVERNAME;

:CONNECT $(MirrorServer)
SELECT @@SERVERNAME;

:CONNECT $(WitnessServer)
SELECT @@SERVERNAME;

I’m guessing you might not have expected:

WINSERVERBASE\SQLDEV03

WINSERVERBASE\SQLDEV03

WINSERVERBASE\SQLDEV03

The problem is the lack of a batch separator. What I should have written was this:

:SETVAR PrincipalServer WINSERVERBASE
:SETVAR MirrorServer WINSERVERBASE\\SQLDEV02
:SETVAR WitnessServer WINSERVERBASE\\SQLDEV03

:CONNECT $(PrincipalServer)
SELECT @@SERVERNAME;
GO

:CONNECT $(MirrorServer)
SELECT @@SERVERNAME;
GO

:CONNECT $(WitnessServer)
SELECT @@SERVERNAME;
GO

While this may be strictly correct, I can’t imagine it’s the behaviour anyone would wish for. Do you think that a :CONNECT statement in a SQLCMD batch should also be treated as a batch separator? Does it ever make sense for it not to?

2026-04-22

A snapshot transformation is about capturing the current state of something from a continuous stream of events. Instead of storing every single historical record, we keep just the most recent update for each entity.

Think of IoT devices as an example. Each device may send a temperature reading every few seconds. Over time, that becomes a huge stream of data. But often, what the business really wants to know is simple: what’s the latest temperature reading from each device right now? That’s where a snapshot comes in. It continuously updates a table of ‘current values’ as new events arrive, so at any given moment, you can query the most recent state without reprocessing the entire history.

2026-04-21

A while back, I managed to catch the tail end of the reptiles series that Sir David Attenborough created. If you have a spare 3 1/2 minutes, take a look at the end of his Life in Cold Blood series episode about crocodiles.

People seem to think crocodiles are cold, unintelligent eating machines. I have a major respect for them. They’ve been around since the time of the dinosaurs, and anything that can do that, is doing something right. In fact, until we arrived with guns, etc. they really didn’t have much to worry about. They were a top-level predator.

2026-04-20

A hopping window is a flexible windowing strategy that combines elements of both tumbling and sliding. Like tumbling, hopping windows have a fixed length — for example, 10 minutes. But unlike tumbling, they are allowed to overlap, because they advance by a smaller step size than their length.

For example, let’s say we configure a 10-minute window that hops forward every 5 minutes. That means between 12:00 and 12:10, you get one window, and between 12:05 and 12:15, you get another. Each window is 10 minutes long, but because they’re starting 5 minutes apart, they overlap. This means that every event can contribute to more than one window.

2026-04-19

I often work with reports either in SQL Server Reporting Services (SSRS or now Power BI Reporting Services) and Fabric Paginated Reports. At a site this week, the complexity of the reports we were working on reminded me that I really, really don’t like seeing T-SQL code (or really any complex business logic) embedded in reports.

Don’t make refactoring difficult or impossible

DBAs tend to be considered a conservative bunch. One thing they’re usually conservative about is refactoring their databases. In many cases, this is because they have little idea what they will break when they make database changes.

2026-04-18

A session window works very differently from tumbling or sliding windows. Instead of cutting streams into fixed blocks of time, a session window groups events based on periods of user or device activity. The window continues to collect events as long as there is activity, and it automatically closes after a defined period of inactivity.

For example, imagine a customer browsing an online store. They click through pages, add items to their cart, maybe watch a video. All of that activity within, say, a 10-minute span of clicks would be grouped into one session. If the customer stops interacting for more than 10 minutes, the window closes, and the next click starts a new session.

2026-04-17

One of my pet dislikes in this industry is the way we handle passwords. I’ve thought that, as a concept, they are completely broken and have been for a long time.

We tell users:

• Pick something really complex
• Don’t write it down
• Change it regularly
• Use a different password for each site, and often for each role that you hold in each site
• Deal with the fact that we apply different rules for passwords on each site

etc, etc.

2026-04-16

A sliding window is a type of temporal window where the windows are of fixed length, but they overlap because they slide forward by a smaller step than their total size. This makes them perfect for producing smooth, continuous metrics like rolling averages.

For example, suppose we define a sliding window of five minutes that advances every one minute. That means the system calculates results based on the last five minutes of events, then slides forward by one minute and recalculates. Each window overlaps the previous one, so every result shares a lot of the same data, but with just a bit of new information added.

2026-04-15

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Advanced

Question:

You have a SQL Server server attached to multiple storage volumes. One of the volumes has been lost. It contained the files for the tempdb database.

You know that tempdb is recreated on each restart of SQL Server, but will SQL Server still start when the files are missing? Will it just automatically create new files? If not, how could you work around it?

2026-04-14