SQL Server - Prevent Clustered Index Scan on a specific table
Is there a way to globally prevent SQL Server from generating Clustered Index Scan on this table?


Yes.  Replace the clustered index with a clustered columnstore index.  It will be highly compressed.  And scans can eliminate unneeded columns, and (critically here) unneeded row groups.

The query here is tricky to optimze:

select t.Id, t.ExternalId, t.Source, tn.Code
from XXX.LargeTable t
join Security tn on t.SecurityId = tn.Id
where t.TransactionTime &gt;= @d1
and t.TransactionTime &lt;= @d2
and tn.NationalityId = 22


The optimal plan depends almost entirely on how wide the range [@d1, @d2] is.  But SQL Server can't have multiple plans for the same query.  You're getting a table scan here as the alternative plan has a huge estimated cost, due to the double nested loop join.  As it turns out if few enough rows qualify in the transaction table, that plan will be cheaper than the table scan, but SQL has to come up with a plan that works reasonably well for any values of @d1 and @d2.

If the date range turns out to be verry narrow, as here, the clustered index scan is a terrible plan, as it requires reading the whole table.  

But in the case of a Clustered Columstore SQL Server only has to scan the TransactionTime column to find the matching rows.  Not only that, each 1,000,000 row column segment has the min and max in the column segment header.  So if the range is narrow many of the row groups can be eliminated without actually scanning all the TransactionTime values.

The "bad" plan had mainly IO waits

  &lt;Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="523619" WaitCount="83735" /&gt;
  &lt;Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="13910" WaitCount="14455586" /&gt;


Scanning a CCI for the same query would require a tiny, tiny fraction of the IO required for the clustered index scan.