What about an option to MARK ALL AS READ.
Then when looking at the UNREAD tab, we can start with fresh data.

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.