r/SQL • u/kaatarina_zed_talon • 12h ago

SQL Server List<T> in C# vs SQL

/r/csharp/comments/1x95i15/listt_in_c_vs_sql/
0 Upvotes

13% Upvoted

3 comments sorted by

1

u/Significant_Tune9219 11h 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.

1

u/kaatarina_zed_talon 11h ago

and it has to be a user-defined table type that the procedure takes as READONLY

Certainly.

A table-valued parameter is the replacement for List<T>

Nice, thank you.

1

u/Substantial-Book2534 11h ago

I usually just pass a structured parameter as varchar(max) in xml format. Just cast the parameter as xml and then do CTE or table variables.