Karl Kieninger (11/12/2011)

---

The row_order in the subquery ranks each row in ItemStatusHistory by the datetime of the Status change partitioned by the record in table Item and the Status. This...

I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points 🙂

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

---

OK, going to 1000 yours has 67% of the logical (row) reads that the naive one has and only half the CPU usage. Neither has any...

It all does come...

I think he was encouraging the OP not to use the...

It rather depends on whether you write the dumbest possible recursive solution or not:

WITH rCTE AS

(

SELECT 1 AS N

UNION ALL

...

Peter Brinkhaus (11/12/2011)

---

No problem, you forced me to investigate my sandbox DB a little further. For reasons I don't know sys.databases.is_read_committed_snapshot_on=1. Turned it off with ALTER DATABASE Test SET ALLOW_SNAPSHOT_ISOLATION...

Peter Brinkhaus (11/12/2011)

---

I always thought "page splits=fragmentation", but on SQL 2005 the fragmentation is less then on SQL 2008, if only 0.38% vs. 0.43%. But you're right, the ALTER INDEX...

Yes, string comparisons using all the complex rules of a 'regular' collation can be very expensive. ...

Hi Peter,

To speed the LIKE and CHARINDEX solutions further, consider comparing using a binary collation:

PRINT '=== LIKE ==='

SELECT

*

FROM

dbo.Metatags

WHERE

'|' +...

chandan_jha18 (11/11/2011)

---

Then I got rid of all the CTEs and simply created temp tables on the fly and joined them with same business logics as CTEs and now the execution...

No-one - the phrase was "pretty small risk".

My comment was ironic / tongue-in-cheek.

bitbucket-25253 (11/10/2011)

---

Since it caused you to take time to answer, in my humble opinion it was a GREAT question .. particulary since I have followed many of your forum...

Antony & Brazumich,

Toughest one point ever earned!