First, I added some additional rows to cover your stated logic:

INSERT #CustomerRef (CustomerKey,CustomerKeyRef,EffectiveDate) VALUES

(1,101,'11/1/2016'),

(1,101,'11/15/2016'),

(2,201,'11/26/2016'),

(2,201,'11/28/2016'),

(3,301,'11/15/2016'),

(4,401,'11/15/2016'),

--important to prove results

(1,101,'11/2/2016'),

(1,101,'11/5/2016'),

(1,101,'11/20/2016')

Now there are 3 rows > orderdate and 2 < order date for 101....

Indianrock (12/9/2016)

---

Forgot to mention, when you have huge variances in estimated/actual rows ( e.g. 600 estimated, 1.4 million actual) how likely is it that some of the work...

The MANY (although not nearly as many as Oracle has) knobs, OPTIONS, HINTS, trace flags, etc. that SQL Server exposes are there for a reason: the optimizer cannot POSSIBLY get...

I'm sorry, but what you have posted up goes WAY beyond free support on a forum, at least for me. That is a metric butt-ton of ugly operations that could...

Most of the time when dealing with single-quote string issues (such as dynamic SQL) I love setting quoted_identifier off and then using double-quotes around the string and single-quotes inside. Never...

Look at the estimated query plans for each and see if the differences tell a story.

Also run sp_whoisactive during the execution and see various things you can see with that.

Yes, gaps and cluster-groups with lots of rows can happen, just like with identities. But a TOP operator will limit the rows and make sure you never do so many...

In reality, the IDs are GUIDs, not sequential IDs. Before anybody goes off on a tangent, I know all the argument against using them. I didn't design the system, I'm...

A minor point, but if you don't need much precision on the seconds this will save you 4 bytes per row that is processed during the actual CONVERTs:

DECLARE @StartDate datetime...

CELKO:

Unfortunately you have a TOP () operator. This proprietary and totally nonrelational operation requires a sort, hence the order by clause. Sorts are a killer in a relational system. You...

CELKO:

1) Take yous ISO elsewhere. This is a SQL Server specific forum and at least 97.4% of the people here couldn't care less. BTW, I'm being generous giving you 2.6%.

2)...

Preethi S Raj (12/6/2016)

---

Thanks Kevin for your reply. I read numerous articles about MDW, its merits and demerits. The reason I was evaluating this less popular feature is because

1) It...

mario17 (12/5/2016)

---

Hi,

I have big process which log all details into TRACKER table for further review and I noticed that I have <Blocking Tree> report once I try to do...

Lots of what looks like painstaking and tedious work there Alan. Kudos for that. It does seem that it only processes 16 digit numbers, which as Jeff pointed out is...

krypto69 (12/5/2016)

---

1) Who said anything about nightly index rebuild? It sounded to me like you did some massive cleanup process that removed hundreds of GB from your database and you...