as an aside set base generation is a tad quicker

set statistics io on

set statistics time on

CREATE TABLE tbl_IN_VS_AND (filterCriterion_sv int PRIMARY KEY CLUSTERED NOT NULL)

Insert tbl_IN_VS_AND

SELECT 100000 * U.I + 10000 * V.I + 1000 * W.I + 100 * X.I + 10 * Y.I + Z.I filterCriterion_sv FROM

(SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U,

(SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) V,

(SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) W,

(SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,

(SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y,

(SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Z

set statistics io off

set statistics time off

at about 10 seconds on a dual core at least.

to compare hardware the first two queries in the post on a baseline of 100 I get

43766 ms and 43750 ms elapsed

Biggest shock was the times of

SELECT @results = count(filterCriterion_sv)

FROM tbl_IN_VS_AND

WHERE filterCriterion_sv NOT IN (Select 214 I UNION SELECT 215 UNION SELECT 216 UNION SELECT 217)

SELECT @results = count(filterCriterion_sv)

FROM tbl_IN_VS_AND

WHERE filterCriterion_sv NOT IN (214,215,216,217)

were 27176 ms

and 43486 ms

while

SELECT @results = count(filterCriterion_sv)

FROM tbl_IN_VS_AND

WHERE NOT EXISTS(SELECT * FROM

(

SELECT 214 AS filterValue_val UNION ALL

SELECT 215 UNION ALL

SELECT 216 UNION ALL

SELECT 217 ) AS tbl

WHERE tbl.filterValue_val = tbl_IN_VS_AND.filterCriterion_sv )

ended up was still 14470 ms so twice as good as the union

I guess it must not construct the in predicate into a working table before starting the query. (until you tell it to via union)