Applications often present a way to allow a
user to select a number of values. These values are then assembled
in an IN() clause that contains a list of values to be matched, as
in the following example:

SELECT * FROM TableA WHERE SomeColumn IN( 1, 2,
3…)

But as the list of items to match grows longer,
it takes SQL more time to perform it. Against a large number of
rows, this can be especially problematic, since there’s no way to
take advantage of the index. The result is a table scan in which
each row is compared to each value in the list.

The values in the IN() clause typically come
from a front-end application whose user makes a run-time selection.
(If that wasn’t the case, we could rewrite the query to optimize
it.)

A better approach in such situations is to
build a table from the list of values and join the table to the
target table. This enables SQL to take advantage of the index,
which significantly increases performance.

The question remains: How do we build the
temporary table to hold the values? There are several ways, but the
one I like uses a user-defined function (UDF) that parses a
delimited string and returns a table in which each item in the
string becomes a row in the table. The UDF looks like this:

CREATE FUNCTION fn_StringToTable (@String
varchar(100))
RETURNS @Values TABLE (ID int primary key)
AS
BEGIN
DECLARE @pos int
DECLARE @value int
WHILE @string > ”
BEGIN
SET @pos = CHARINDEX(‘,’, @string)
IF @pos > 0
BEGIN
SET @value = SUBSTRING( @string, 1, @pos – 1)
select @string = LTRIM(SUBSTRING( @string, @pos + 1,
LEN(@string)-@pos+1))
INSERT @Values SELECT @value
END
ELSE
IF LEN(@string) > 0
BEGIN
SET @value = @string
INSERT @Values SELECT @value
SET @string = ”
END
ELSE
SET @string = ”
END
RETURN
END

You would execute this function, passing it a
comma-delimited string like this:

SELECT * FROM fn_StringToTable( ‘100, 200, 333,
444, 555’)

Now you can join this table to any other table,
view, or table UDF, and get maximum performance with minimal disk
reads.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!