A quick example of why IN can be bad

Here’s just a quick demo that illustrates why the IN operator in TSQL might not perform as well as alternatives (like a range query or joining onto a temporary table containing your values).

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
	DROP TABLE #temp;
END;

CREATE TABLE #temp
(
	id INTEGER NOT NULL IDENTITY(1,1),
	col1 VARCHAR(10) NOT NULL DEFAULT 'ABCDEFG',
	CONSTRAINT PK_tmp PRIMARY KEY CLUSTERED
	(
		id
	)
);
GO

INSERT INTO #temp DEFAULT VALUES
GO 100000

SET STATISTICS IO ON;

SELECT *
FROM #temp
WHERE id BETWEEN 1 AND 10;

SELECT *
FROM #temp
WHERE id IN (1,2,3,4,5,6,7,8,9,10);

SET STATISTICS IO OFF;

Note the scan count and logical reads. The IN operator causes an index scan for each value. While the cost difference is trivial in this case it obviously increases as the number of values in the IN clause increase.

(10 row(s) affected)
Table '#temp_0000001B2DA4'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(10 row(s) affected)
Table '#temp_0000001B2DA4'. Scan count 10, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

If you can’t change your query to use a range then something like this may be viable.

SET STATISTICS IO ON;

DECLARE @table TABLE
(
	id INTEGER NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO @table (id) VALUES (13),(21),(30),(47),(59),(63),(73),(81),(91),(109);

SELECT *
FROM #temp t1
INNER JOIN @table t2
	ON t1.id = t2.id;

SET STATISTICS IO OFF;