If I read this correctly, you just implemented the LAG(...) Over(...) analytic function.

However, I'm interested... if you do this on a large dataset, what does the execution plan look...

Jeff Moden (9/4/2010)

---

True enough. And search for WITH won't do it either....

Almost impossible. My advise is upgrade to SQL Server 2005, then start looking at Oracle which has top() over(), lag, lead, and a number of other really cool analytic functions....

I know where this is going. If it's a 32-bit...

A cartesian product is set-based... it's a product of sets... of course on a database agreed - you really want to avoid it 🙂 But it's part of a foundational...

Jeff, I'm also interested, but before I even start looking I'm getting up to speed on set theory. In particular, understanding cartesian products, relations, functions, function composition, function limitations, etc.

Whoa! What's that full outer join doing there?

If I'm understanding what you are trying to achieve correctly, you are trying to output:

1. A column for the learner's name, then

2....

Fair point... also I'm thinking it won't scale because each time it does the sum it has to sum every row before it, which of course increases every time.

I...

Oh, and before anyone asks why I didn't run an execution plan to see what it looks like... I did!

I got an odd error from SQL Server (SSMS?), which I've...

Wouldn't a recursive CTE do this better?

SQL Server is trying to allocate memory but for reason it can't. Now I'd say that given that it says that it's reserved 1.8 GB or so of VAS, and...

I'm curious what your bootup switches are. Do you use /3GB as well as /PAE ?

Andrew Gothard-467944 (11/26/2009)

---

Use for performance testing on dev servers, checkpoint the db before running. Don't run against live.

What it does is clear down your procedure cache -...

If you have multiple instances then I'd say that you are using the SQL Server Browser service and different databases are listening on different ports.

But as it's sorted by...

Neat!