comma separated values or table variable in case of using IN clause
sql, sql-server
Solution
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.
Problem
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