This is just a quick test... But according to this, the Windowed SUM is going faster... see what you think...

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL

DROP TABLE #temp;

CREATE TABLE #temp...

There's always Hotmail & AOL. 😛

Yea... I'm right there with you. My latest test harness was based solely on my perception of what Jeff was talking about. I'm not certain that it's actually representative of...

-- removing duplicate post --

Sergiy (12/21/2015)

---

Minor change to deal with deadlocks:

WITH

cte_Blocks AS (

SELECT

b.spid,

b.typ_of_block,

b.capdt,

b.blocked_spid,

blocked_level = CAST(1 AS INT),

blocking_chain = CAST(b.spid AS VARCHAR(8000))

FROM

#Blocking b

WHERE b.blocked_spid = 0

UNION ALL

SELECT

b.spid,

b.typ_of_block,

b.capdt,

b.blocked_spid,

blocked_level = cb.blocked_level +...

Jeff Moden

---

Not so much as the hierarchical query goes. You pretty much topped that off quite nicely. The only thing that you might want to add is a check...

IGNORE_DUP_KEY = ON doesn't "ignore" duplicate keys per-se.

It simply discards new rows, that would cause a key to be duplicated if it were allowed into the table, without throwing...

The danger is real...

Jeff will no doubt follow up with something super cool... but in the mean time, this should give you the basic idea...

IF OBJECT_ID('tempdb..#Blocking', 'U') IS NOT NULL

DROP TABLE #Blocking;

...

I seem to recall reading something about beer popsicles... Ever see a drunk monkey with brain freeze? 😉

(I haven't... But I'd like to... and I don't care if that makes...

Now I feel cheated... Mine only came with the yellow ones... :unsure:

Extremely kind words sir... Thank you again!

As for the Monkey... They were all out of the non smoking ones when I placed my order...

LOL... NULLs eh? Are you referring to "Missing & Applicable" or "Missing & Not applicable"? I think that's up there with religion & politics... 😛

Jeff Moden (12/20/2015)

---

As for not actually being a result set, I agree to disagree. There's nothing preventing all of the subsets from being a part of a larger set....

Jeff Moden (12/20/2015)

---

Heh... yeah... so where would you do it? SSRS?...