To select the records without deleting the duplicates

WITH cteBaseData AS (
SELECT
  cr.ClientRef
  , cr.LinkedClientRef
  , rn = ROW_NUMBER() OVER (ORDER...

This will remove the duplicates

-- Create a table to hold the data
CREATE TABLE #Links (
  ClientRef   INT
, LinkedClientRef INT
);

Your expected results do not match your supplied sample data.
For this reason, I am unable to create a tested result.  However, the code below, should get ypu started.

With some more digging, on my system, this error is caused by a partition scheme

SELECT
  Indexname = i.name
, IndexType = i.type_desc

Reported as spam

There is no one-size-fits-all for indexing.
You need to analyse the various queries that run against each table.  That will shape the indexes that you add.