Since you've very likely to (almost) always query by trans date, you should change the clustering on the table to:
( [transactionDate], [transactionId] )

That will help performance for...

Certain errors cause SQL to exit the batch, no matter what the code says.

You shouldn't really need to completely flush the buffers.  Comparing Logical I/O should be sufficient, since you can't really know what physical I/O will occur for any given query at...

But that doesn't match your original request.  You said you wanted "records" (i.e. rows), which would include the Due_time.

If you need just the client_id and PU, you can...

SELECT tn.*
FROM dbo.table_name tn
INNER JOIN (
    SELECT client_id,PU
    FROM dbo.table_name
    GROUP BY client_id,PU
    HAVING MIN(Due_time) <> MAX(Due_time)

If you're on SQL 2016, you should use SQL's native data masking capability.  It will easily and efficiently do exactly what you want.

If that won't work, you could...

(1) Get rid of the ActiveStatus column.  It's useless, and it can cause headaches for the optimizer.  That type of column really is a throwback to tape-based data.

(2) At...

No, it's not necessarily a good idea.  In fact, stop it first, then see what's it doing.  If it's using generic rules, like 10% and 30%, then don't restart the...

MS will probably get rid of the old-style joins at some point, but it will likely be a long while, since INNER joins cannot be misinterpreted in the WHERE clause...

It can be done using ISNULL().  I'm not trying to say that's necessarily better, just that it can be done, assuming col1 <> col2:

SELECT ISNULL(NULLIF(ISNULL(col1,...

Use "TOP (1)" to limit the results to one row.  For example:


SELECT TOP (1) Field 
FROM TABLE 
ORDER BY Field