The two technologies, Complex Event Processing and In-memory Relational Storage, are apples and oranges. StreamInsight solves a different problem than does the relational engine.

Could you bend the relational engine to simulate the streaming of events by capturing 5 minute windows of data and then running processes on them to glen information and persist the data elsewhere? Sure, but you'd be building the plumbing to get new data into an in-memory table and age the old data out to a permanent table while StreamInsight was designed with that plumbing from the beginning. StreamInsight programs are built in .NET, not T-SQL, so that should tell you something about how different the two options might be.

The advantage of StreamInsight is raw speed due to the lack of storage requirements. For SQL, in-memory tables might be faster than on-disk storage in some scenarios but it is still storage and comes with a lot of the bulk that the relational storage engine introduces in terms of getting data into and out of the table.

Think of it like this, if you have to process a flow of data (like ticker buy/sell data) that is never ending (think compiled feed of all international exchange data) and can have very high volumes at times but all you care about are discrete data points within that flow, or more useful, data points early in the flow that are related to other data points that occur later in the flow but in relatively close proximity then StreamInsight can help with that. Using a stock theme, an example might be trying to find arbitrage opportunities within a stream of international exchange data.