The following script creates a single-column clustered table containing the integers from 1 to 1,000 inclusive.
IF OBJECT_ID(N'tempdb..#Test', N'U')
IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test
id INTEGER PRIMARY KEY CLUSTERED
INSERT #Test (id)
FROM master.dbo.spt_values AS V
WHERE V.[type] = N'P'
AND V.number BETWEEN 1 AND 1000
Let’s say we need to find the rows with values from 100 to 170, excluding any values that divide exactly by 10. One way to write that query would be:
FROM #Test AS T
WHERE T.id IN
That query produces a pretty efficient-looking query plan: Read More...