This article warns of the performance pitfalls of using parametrised queries rather than stored procedures. The basic idea is that the server has to guess the data type of your parameters and its guesses pollute the cache. My confusion comes from the article only mentioning stored procedures as a solution. As far as I know, user-defined table-valued function are just as strongly typed as stored procedures, so the server shouldn't have to guess when using them. However, I also know that ADO.NET only has a special ComamndType for stored procedures, so I don't know how it knows that it's talking to a user-defined table-valued function rather than passing around raw SQL.
My question is this: Given that I can't tell ADO.NET that what I'm telling it to call is a user-defined table-valued function rather than a stored procedure, am I at risk of it treating my call to a user-defined table-valued function as a parametrised query and therefore facing performance issues? Or is ADO.NET smart enough to know that user-defined table-valued functions have obvious parameters?
No, they are not the same.
While both get their parameters coerced into the respective types, therefore no issues with sargeable queries, an ad-hoc batch against a TVF still gets a new query plan, so you have the overhead of a recompilation.
As far as I know, user-defined table-valued function are just as strongly typed as stored procedures, so the server shouldn't have to guess when using them.
No they are different things. Procedures have query plans already compiled, TVFs only get their plans compiled when they are used, so if you are using a new data type (or something else changed in the query) then you get a recompile.
You can see this by examining the query plans generated in this fiddle. Note how the query_hash values are the same in the procedure case, but not in the TVF cases.