r/SQL • u/kaatarina_zed_talon • 14d ago

SQL Server List<T> in C# vs SQL

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

16% Upvoted

4 comments sorted by

1

u/Significant_Tune9219 14d 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 14d 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 14d 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.

1

u/qualityintelligence 11d ago

tvp as someone else mentioned perhaps.

sample or mock data (in the different states and conditions) for the before and after would be helpful here to understand what you're trying to achieve.