The Bit Bucket

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

It’s common advice that most SQL Server tables should have a clustered index. There are some exceptions to this but it’s a pretty general rule, and if in doubt, you should follow it. (Note that this is not the same as having a primary key).

I regularly come across tables without clustered indexes for all the wrong reasons. So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that don’t have a clustered index. No surprise, it’s called ListUserHeapTables because a table without a clustered index is a heap.

2026-04-08

The Join operation allows you to bring together data from multiple sources in real time — much like performing a SQL join on continuously arriving data.

Imagine you have one stream of telemetry from IoT devices and another stream of configuration updates.

By joining them on a common key, such as a device ID, you can enrich your telemetry with configuration or location context in real time.

Eventstream supports different types of joins, including both inner joins (matching only overlapping records), and outer joins (keeping all events from one stream, even when there’s no match).

2026-04-07

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. Version 27 adds the first set of views and functions for working with Nepali dates. These are useful in Nepal and in a number of Buddhist-related areas.

The first tool added is a view called NepaliMonths. It returns the Nepali names for months. You can see it in the main image above.

2026-04-06

When we talk about aggregation, we’re really talking about taking huge volumes of raw events and rolling them up into something that’s usable, measurable, and actionable. A raw stream might be thousands of individual events per second — each transaction, each sensor ping, each click. By themselves, they’re useful for tracing details, but they don’t tell the bigger story.

Aggregation lets us step back and say: Instead of looking at every single reading, let’s look at the total, the average, or the maximum over a period of time. Common aggregation functions are ones you already know — COUNT, SUM, AVG, MIN, MAX.

2026-04-05

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. The TokenSetSimilarity function calculates token set similarity for two strings.

It answers the question: Do these two strings contain mostly the same words, even if the order, spacing, or repetition differs?

It is useful where word order varies, or extra or missing words are common. It can also help where character-level typos are less important than the presence of words.

2026-04-04

When working with real-time data, it’s easy for your streams to become cluttered — especially as events come from multiple sources with different structures. That’s where the Manage Fields option becomes essential.

It gives you control over the shape of your data stream. You can choose which fields to keep and drop those you don’t need, which helps reduce noise and improves performance. For example, you might remove diagnostic fields or metadata columns that aren’t needed for your analytics.

2026-04-03