The Bit Bucket

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

Let’s take a look at the windowing options available. The first of these is the tumbling window, and it’s the simplest type of temporal window. It slices the event stream into fixed-length, adjacent blocks of time, with no overlap and no gaps. Think of it like dividing a timeline into perfectly equal buckets — each window starts as soon as the previous one ends.

For example, let’s say we’re monitoring sales transactions. If we set up a tumbling window of five minutes, then every five minutes the system calculates totals or averages. At the end of each window, you get a new result, and then the process resets for the next five-minute slice.

2026-04-13

Most SQL Server developers are aware that the EXEC statement can be used to:

• Execute a stored procedure (system, user-defined, extended)
• Execute some dynamic SQL

And most understand that you can SELECT from a scalar user-defined function.

But the option that many people don’t seem to be aware of, is that you can also use EXEC to call a scalar function.

I remember noticing this in the documentation for the EXEC command some years back. Prior to that, it had never dawned on me that you could use EXEC to call a scalar UDF. It’s also in the oldest documentation that I was able to check, so I’d say it’s worked for a long time.

2026-04-12

One of the challenges with streaming data is that it never ends — it just keeps flowing. If we tried to calculate totals or averages across the entire stream, the numbers would just keep growing forever, and we’d never get a meaningful result. That’s why we use temporal windows.

A temporal window lets us break the continuous stream into slices of time, so we can apply aggregations within those boundaries. For example, instead of calculating total transactions forever, we might calculate total transactions every minute, or average sensor readings every five seconds. Each window produces a result that can be stored, visualized, or acted upon in real time.

2026-04-11

The WITH EXECUTE AS clause was a great addition for defining stored procedures and functions, to change the execution context, just for the duration of the stored procedure or function. For example:

CREATE PROC SomeSchema.SomeProc
WITH EXECUTE AS USER = 'Fred'
AS
... 

Mostly I use this with the OWNER option:

CREATE PROC SomeSchema.SomeProc
WITH EXECUTE AS OWNER
AS
... 

It’s also useful during testing, where I can temporarily change my execution context during testing. For example:

EXEC AS USER = 'Fred';

\-- Try some code here while running as Fred

REVERT;

But the option that most people don’t realize is possible, is that you can set the execution context for a single execution like this:

2026-04-10

When we talk about grouping in real-time data processing, we’re talking about organizing events according to attributes that are meaningful for analysis. Instead of treating the event stream as one giant firehose, grouping lets us carve it up into categories that align with how the business thinks about its data.

For example, in an IoT scenario, we might group telemetry events by device ID. This way, instead of calculating one global average temperature across thousands of sensors, we can calculate average per device, giving us much more useful insights. In a retail scenario, we could group transactions by customer ID to analyze individual purchasing patterns, or by region to monitor performance across different locations. Grouping is what enables those per-customer, per-device, or per-region dashboards that managers and operators rely on.

2026-04-09