Jeff Moden (5/28/2015)

---

Jason A. Long (5/28/2015)

---

I would suspect that the answer to which one is faster "quirky method" or "windowed function", is going to be heavily dependent of the available indexes.

The "quirky method" relies exclusively clustered index (assuming one exists) to establish the order of the running total. There's no real control beyond that.

The windowed function actually requires that you have, what Itzik Ben-Gan calls, a POC index (Partition, Order & Covering) in order to get the best possible performance.

Based on that, I would expect that the windowed function method to have the edge over the quirky method, only if the correct POC index is in place, otherwise, I'd expect the quirky method to blow the windowed function out of the water.

In addition... The reason I would expect the POC'd windowed function to edge out the quirky, is simply because I'd expect that the POC index would NOT contain all of the columns on the table... Which would allow more rows per page... Which means fewer pages. If the POC index covers the full table, I'd expect the two approaches to have similar performance.

That said, I have not yet had a chance to properly test this hypothesis... I'll add it to my "weekend chores" list, unless someone wants to beat me to the punch.

Jason. Do you have a link to Itzik's article on the subject? I'm especially interested in the POC index but would love to read about insitu.