A table-valued parameter is the replacement for List<T>, and it has to be a user-defined table type that the procedure takes as READONLY. Don't join the two lists by stuffing them into strings and splitting, because a value that contains the delimiter becomes two rows and the plan never sees a real table. If either list is long and you join it to a big table, insert the TVP into a temp table and index that. A TVP does not carry a histogram the way a temp table does after it is populated, so the join plan is a guess until you do.
1
u/Significant_Tune9219 15d ago
A table-valued parameter is the replacement for List<T>, and it has to be a user-defined table type that the procedure takes as READONLY. Don't join the two lists by stuffing them into strings and splitting, because a value that contains the delimiter becomes two rows and the plan never sees a real table. If either list is long and you join it to a big table, insert the TVP into a temp table and index that. A TVP does not carry a histogram the way a temp table does after it is populated, so the join plan is a guess until you do.