comma separated values or table variable in case of using IN clause

Question

Suppose we want to pass an array of values as a parameter to an SP in which it is used as values in IN clause.

Which one is better in case of performance :

1- Passing values as comma separated string

ie

Select * FROM myTable WHERE Id IN (@myConcatenatedValues)

OR

2- Passing values as a table variable consist of a column that holds this values as rows

ie

Select * FROM myTable WHERE Id IN (Select Id from @myVariableTable)

Thanks in advance

http://stackoverflow.com/questions/1200295/sql-join-vs-in-performance — Squirrel5853, Aug 14 '13 at 09:38
@Secret Squirrel : you are right but my question was about IN clause specifically not JOIN or EXISTS or something else. — Mostafa Armandi, Aug 14 '13 at 10:09
if you pass comma separated string, then you have to use prepared statement in your stored procedure. But if have those values already existing in a table, it will be better if you pass table variable. — Krishna Rani Sahoo, Aug 14 '13 at 10:23

Luke Merrett · Accepted Answer · 2013-08-14T10:25:46.407

I did a quick test run of this using the below 3 queries:

DECLARE @UsersInString VARCHAR(255) = '1, 2, 3, 4, 5, 6, 7, 8, 9, 10'

/* Prone to SQL Injection, do not use like this! Sanitise your inputs */
EXEC (' SELECT * FROM [dbo].[User] WHERE UserID IN ( ' + @UsersInString + ' ) ')
GO

and:

DECLARE @UsersInTable TABLE (
    UserID INT
)

INSERT INTO @UsersInTable
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

SELECT *
FROM [dbo].[User] U
JOIN @UsersInTable UT ON UT.UserID = U.UserID

and:

DECLARE @UsersInTable TABLE (
    UserID INT
)

INSERT INTO @UsersInTable
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

SELECT *
FROM [dbo].[User] U
WHERE U.UserID IN (
    SELECT UserID
    FROM @UsersInTable
)

The actual execution plan returned indicates that the top query is faster in my case; the query used a Clustered Index Seek only.

The middle query was marginally slower as along with the Clustered Index Seek it also used a Table Scan on the table variable.

The bottom query was even slower as along with the Clustered Index Seek and Table Scan, it also performed a Sort on the table variable; one would assume in an attempt to optimise the IN () query against the sorted Clustered Index.

However it is largely dependent on the indexing of the column being queried; along with how long it would take to build the string or table variable.

A fairly important side note: Passing the values as a comma separated string could be risky; you would have to dynamically construct the IN () statement using string concatenation; which could leave you open to SQL Injection.

Is there any additional difference in using the table variables if you include an index in their definition? `declare @severities table (SeverityId uniqueidentifier unique clustered)` — Mr.Mindor, Sep 20 '13 at 15:06

comma separated values or table variable in case of using IN clause

1 Answers1