The usual problem is trying to follow part 1 of rule #2 (follow users' orders) while only bending rule #1 (i.e. a slight performance impact) when you'd really like to...

I would never rely on a trick based on a particular feature of the data which wasn't relevant to the selection. In my opinion, this is extremely bad practice.

As written,...

Peso (2)

create table #t (id int not null, st char(1) not null, date datetime not null, seq int null)

--

--.... main fill omitted

--

DECLARE@Seq INT,

@Yak INT,

@st CHAR(1)

SET@Seq = -1

UPDATE#t

SET@Seq = @Seq +...

Both Jeff's and Peso's queries do not eliminate pairs where the status stays the same. Additionally, as Peso pointed out, Jeff didn't remove the 'pairs' where the ids don't match....

Hi Gsquared,

Thanks for the hint.

I tried implementing your use of dense_rank instead of row_number and located an omission in my code. In my version 2, CTE t4 should have group...

GSquared (4/11/2008)

---

I used your temp table and test data on 9000 rows, 1000 IDs in #t.

Here's the query I wrote, based on the description you gave in the original post:

;with...

Glen (4/11/2008)

---

Can you please add the parameters and settings on your tempdb? Could it be linked to the fact that you are simply hitting an "auto grow" of your tempdb...

Did you read the script? I thought I put everything...

I got this right and have, in the past, upgraded a trial Exchange Server to full version, so I know it works for some MS software and assumed it would...

Matt Miller (4/9/2008)

---

Consistency is cute, but the optimizer needs to be able to keep up and know what the right way to handle that variable. Jeff stumbled onto one...

GSquared (4/8/2008)

---

On the consistency point, I have enough problems with T-SQL having weird consistency issues already. My (least) favorite example is Replace vs Charindex. One has the parent...

Is this just wishful thinking or do you know something?

The joke was...

I haven't used Vista yet, except for a brief trial, but am quite happy with XP, so won't be changing soon.

On the matter of Consistency. Since Microsoft control the specification...

srienstr (4/4/2008)

---

Shaun McGuile (4/4/2008)

---

Aleksandr Furman:

How is that funny?

Is that not the truth? 😉

Like the one told to me by an accountant;

"There are three types of accountant in this world,...