Something like this should work:

SELECT   EMPLOY_REF
FROM     EMPLOYEE_BACKGROUND
GROUP BY EMPLOY_REF
HAVING   MAX(CASE WHEN CHECK_TYPE='DBS' THEN 1 ELSE 0 END)=0;

Can you post the statistics histogram for the statistics associated with each index?

Cheers!

Yeah, as others have pointed out, the JSON is actually improperly formatted with a missing comma in one place and an extra comma elsewhere.

Since "The JSON is improperly...

Thanks for putting in the effort to give us consumable DDL and sample data!

As J pointed out, the scripts don't work in their current form, for a few...

Hmmm...you might want to check again.

Compare what's in your FROM clause to what the IO statistics and query plan say you're accessing.

The FROM clause shows these...

That's a bit of a monster, especially since it looks like a lot of what's in your FROM clause are themselves very complicated views. On that note, the first takeaway...

The whole pagination + total count thing is a well researched problem, fortunately 🙂

I can put together a test harness tomorrow to demonstrate, but typically an extra CTE...

The problem is that the subqueries are asking for exactly what you're getting: just the total count, no matter what the value of A.Custom.

There are a couple ways,...

Just to clarify, if you're trying to create a demo so you can observe a Sch-S lock being used, queries will not always take one.

They're taken when a...

On the other side of that coin, I'll use SSMS from the server running the instance if necessary, but I generally discourage others from doing it.

To pick just...

You need to alias the table variable to reference it outside FROM (e.g., column references in the JOIN criteria).

Check the General Remarks section at  https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql

If you...