When 2012 ships (or in the current public CTP 3) you could solve the problem without any looping:

CREATE TABLE #TranRecord

(

TransactionID INTEGER NOT NULL,

...

A normal (non-recursive) CTE is just a logical definition, like an (unindexed) view. The results from a CTE are not materialized; the optimizer considers the query as a whole...

Hugo Kornelis (11/9/2011)

---

Advance warning as a special service for those who take the time to read the discussion - the next question in this series (scheduled to go live in...

Good question. The explanation could have been improved very slightly:

"SQL Server will use the same amount of storage for a column whether its value is NULL or a "real"...

My preference would be...

These types of issues tend to come down to mapping between Unicode and ASCII code points. That collation is a Unicode one, so it makes sense to use a...

That seems inconsistent to me. I prefer something closer to Chris'...

You just need one final ORDER BY at the bottom.

ORDER BY applies to the *result* of the whole...

L' Eomot Inversé (11/8/2011)

---

Using a projection of master.dbo.spt_values onto the number column as a smallish [0 to 2047] tally table is a nice idea, if a little adventurous as this...

What Hugo said. Again.

http://connect.microsoft.com/SQLServer/feedback/details/630087/intersect-with-order-by-abends-when-data-types-are-implicitly-converted-on-right-hand-side

goofbauer (11/8/2011)

---

I now have a new favorite. :blush: The Itzik-style cross join. Begs the question, why does MS place the emphasis they do on cursors and rCTEs in certification. A...

To be fair, I had less to type. :laugh:

See Hugo's reply for a detailed explanation of why your explanation is wrong. I'm not getting further involved.

You might like to read http://www.sqlservercentral.com/articles/T-SQL/74118/