You could go the redo route if you could afford some brief downtime. Stop replication, delete the data and rebuild indexes, then re-initialize the replication with a fresh snapshot.

Quite right: if DFUR1.[AsOfDate] is any date/datetime type, you don't want to use ISDATE() on it.

Also, you can simplify the year and month comparisons:

AND

YEAR(MD1.[Inception Date])...

What percentage of the data does the part to be deleted represent? For example, if the database has 100 years of history, then 2 years is a relative pittance....

I think you need to step back...

And I realize that an absolute rule is not the intent of what you're doing, but that is what very often happens now. Identity keys have become so much...

But that's the problem that is over-looked in the all-out desire to achieve a "small clustering key". Often the "always a small clustering key" approach requires creating far more...

I think that Query1 data got scrambled or something.

Can you use a spreadsheet instead? After you run the queries, in the Results/Output area, left-click in the empty box...

Another statement of fact:...

The Dixie Flatline (7/15/2014)

---

Scott, I respectfully have to ask for clarification. Whenever you add multiple columns to the clustered index, you increase the size of ALL nonclustered indexes...

You don't need to explicitly convert it. Just get the format to a string of YYYYMMDD, which is always a valid date/datetime format:

SELECT DATEADD(HOUR, @hour, CAST(@date AS varchar(8)))

Here's a few thoughts:

If T270.C18133 might often be less than 19 bytes, add this to the WHERE clause:

WHERE

LEN(T270.C18133) >= 19

Since you don't have an ending %,...

Interesting, but not definitive.

Would you please run these commands on that database and post the results? That will show what indexes SQL "thinks" are missing, and how existing indexes...

It will cover that specific process as it exists now, yes, but you're doubling one table's size. And you'll have to periodically go back and re-do the "covering" index...

D'OH, sorry, I left off one all-important WHERE condition, an absolute NO-NO for a DBA :-):

;WITH

cteTally10 AS (

SELECT 0 AS tally UNION ALL SELECT 1 UNION...

I strongly agree that you should substitute numeric values in place of the (very) long varchar key columns: [SubscriberID],[UserID] and [DeviceID].

But, until then, based on what you've posted, you should...