ScottPletcher that is not entirely true and that is not what I was talking to. I was looking at the bigger picture or the why are they creating these...

You don't really need to partition the table if you (1) cluster the table by date first and (2) the lookup queries specify a WHERE clause on a date or...

(1) You could use dynamic SQL and change the table name literally in the code before executing it.

(2) Or you could instead use a synonym to point to the active...

SELECT 
    CAST(Time_2 AS date) AS Date,
    CAST(SUM(CASE WHEN DATEPART(HOUR, Time_2) = 1 THEN Unit ELSE 0 END) AS decimal(9, 3))...

Since you're only doing INSERT and not UPDATE, you don't need MERGE.

I really can't follow the different tables you have since there's only one source table for all the columns...

You'll need to CAST any text column(s) to varchar(max) in the PARTITION BY.

;WITH cte_count_dups AS (
    SELECT 
        *, COUNT(*) OVER(PARTITION BY Nationalnumber, Birthdate, Mobilphone, Emailaddress, Firstname) AS dup_count
...

Not a lot of details.  Maybe something like this:

SELECT 
    NationalNumber
FROM Contact
GROUP BY NationalNumber
HAVING /*COUNT(*) > 1 AND*/
    ((COUNT(DISTINCT Birthdate) >...

You can't include any columns in a clustered index (by definition it automatically includes all columns in the table anyway).

Just use the key column(s) you need.  It's OK, and often...

The DBA's right about the concurrency issue.  Also, you should really only create a clustered index on a temp table and it should be created before the table...

The DBA's right about the concurrency issue.  Also, you should really only create a clustered index on a temp table and it should be created before the table is loaded.

You...

You can reference a temp table in dynamic SQL.  Could you create a temp table and load it instead?

SELECT j.name AS running_job_name, 
    DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS duration_mins,
    ja.* 
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = ja.job_id
WHERE...

> Two synchronous replica in one region <<

The prod server must wait for the replicas to be in sync.  That could also be adding to delay in prod.

Typically you create only the clustered index prior to load, and any non-clustered indexes after the load.  That's generally the best-performing approach, although theoretically it could be different for a...