Quassnoi,

At the risk of interrupting your flow here, are you aware that you are missing something very important from your 'group-wise min/max (with ties) discussion?

There is a QO transformation, available...

Adam Machanic (2/5/2010)

---

What I expected when you mentioned this was some kind of streaming example where the procedure and aggregate would each spit out a set of rows that had...

RBarryYoung (2/6/2010)

---

You know I actually wrote a lot of these same things last summer (right before I got sick), including the "packing everything into one big varbinary" trick, but it...

TheSQLGuru (2/5/2010)

---

DOH! More goof - you need an index on the value joined or it can never do anything but a table scan/hash. Back in a moment with...

Clearly a bug in the optimizer - take it up with the SQL Team :laugh: :laugh: :laugh: :laugh: :laugh:

No, seriously, I got my caveat post in first, so AHA! back...

I opened a Connect suggestion of my own, for CLR user-defined analytic functions (streaming multiple rows input and output thingies :-D).

https://connect.microsoft.com/SQLServer/feedback/details/530387/sqlclr-user-defined-analytic-functions

I would hope to encourage some casual readers of this...

Well yes, absolutely. I'm not arguing the...

Adam Machanic (2/5/2010)

---

Agreed, but don't you think the SQLCLR hosting environment should handle this itself and spill to tempdb just like everything else in SQL Server does? Pedro, are you...

Adam Machanic (2/5/2010)

---

This is certainly an interesting and promising idea and I'll have to play with it a bit. I'm not sure, however, whether it helps to solve windowing problems,...

Adam Machanic (2/5/2010)

---

Your example shows that an aggregate can aggregate faster than a stored procedure, but is that really surprising? The stored procedure needs to do data access over the...

Adam Machanic (2/5/2010)

---

Hi Paul,

Might want to re-read what Bob Dorr has to say about this technique:

😀

http://blogs.msdn.com/psssql/archive/2009/12/15/how-it-works-are-you-handling-cancels-correctly-in-your-sqlclr-code.aspx

In Bob's case he was only working with...

TheSQLGuru (2/5/2010)

---

The inability to have statistics on table var (except for PK if you have one) can be a killer even if there is just ONE row in the table...

Do you mind if I adopt that gem into...

Adam Machanic (2/3/2010)

---

I saw that you left a note in this item so I added some code to test. Would appreciate any insight you can add with regard to what's...

You could use SELECT DISTINCT * to overcome that (instead of using the group by).