SQL Server 2008 R2: StreamInsight changes at RTM: Count Windows

Another interesting change in the RTM version of StreamInsight is the addition of a new window type. Count Windows aren't time based but are based on counting a number of events. The window type provided in this release is called CountByStartTimeWindow. Based on that name, you'd have to presume we might get other types of count windows in the future.

This new window type takes two parameters. The first is the number of events. The second is an output policy, similar to the policies now required for the previous window types. The CountWindowOutputPolicy currently only offers one policy type as PointAlignToWindowEnd.

Here's an example of the window in use:

var queryOutput = from w in input.CountByStartTimeWindow



                  select new { AverageMilliseconds = w.IntAvg(e =>

                              e.MillisecondsToPassSpeedCheckPoint) };

There are a few important things to note here. The first is that the count window doesn't work with the built-in aggregates such as Sum, Min, Max, Avg, etc. This surprised me and I'm sure that will be an option some time in the future. At present, it works with user-defined aggregates. (I'll describe building this user-defined aggregate in another post).

The second note is that the count isn't strictly a count of events, it's a count of distinct event start times. So if you have only one event per start time, the number of events in the window will match your count parameter. But, if you have multiple events starting at the same time, you can have more events in your window than your parameter has requested.


Leave a Reply

Your email address will not be published. Required fields are marked *