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
;
GO
CREATE TABLE #Test
(
id INTEGER PRIMARY KEY CLUSTERED
);
;
INSERT #Test (id)
SELECT V.number
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:
SELECT T.id
FROM #Test AS T
WHERE T.id IN
(
101,102,103,104,105,106,107,108,109,
111,112,113,114,115,116,117,118,119,
121,122,123,124,125,126,127,128,129,
131,132,133,134,135,136,137,138,139,
141,142,143,144,145,146,147,148,149,
151,152,153,154,155,156,157,158,159,
161,162,163,164,165,166,167,168,169
)
;
That query produces a pretty efficient-looking query plan:
Read More...