Isn't this the third iteration of the same essential q?  Why do you keep re-posting the same q??

I suggest just this.  If that still performs poorly, we'll have to look at the view itself and tune it.

DECLARE @StartDate date = '01-01-2018'
,...

SELECT
    EmployeeId, Forename, Surname, DateOfBirth, leaver, EmployeeStartDate, LeftDate
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY EmployeeId
        ORDER...

I'm assuming, and hoping, that you copied the files to the new locations rather than moved them (never do that, always copy and retain the originals until the ONLINE works...

The only thing I see that could help would be a separate index on just:
(col1, col2)
If possible, use compression on the index, at least row but page if...

I think you could use an INSTEAD OF INSERT trigger to do that, adding a view if necessary to avoid SQL "compile"/pre-scan issues.  Of course the trigger would apply to...

I don't follow.
So there can be multiple rows for Product ABC123.  Why?  To allow changes over time?  Or for diff locations?

It might be helpful to do this logically,...

If you want to add an IDENTITY column in table1 that is not in table2, you can do this:

select IDENTITY(int, 1, 1) AS id, *
into table1...

SELECT EName, UnitsSold, Category
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Category ORDER BY UnitsSold DESC) AS row_num
    FROM #t
    WHERE...

You should do at least some normalization here.  I'd say the below is the minimum.  Easiest is a separate row for each column.  Without more normalization, yes, that will result...

You could change the date format just for that code:

DECLARE @dateformat_setting char(3)
IF OBJECT_ID('tempdb.dbo.#useroptions') IS NOT NULL
  DROP TABLE #useroptions
CREATE TABLE #useroptions (

Sorry, extremely busy at work, didn't finish code before.  Maybe as below?

INSERT INTO [dbo].[SomeCause] ( SomeID, SomeType, SomeThingElseID, SomeDesc )
SELECT SC.SomeID, SC.SomeType, SC.SomeThingElseID_New, SC.SomeDesc