SQL Server: Table-valued Functions vs. Stored Procedures

An inline table valued function (TVF) is like a macro: it’s expanded into the outer query. It has no plan as such: the calling SQL has a plan.

A multi-statement TVF has a plan (will find a reference).

TVFs are useful where you want to vary the SELECT list for a parameterised input. Inline TVFs are expanded and the outer select/where will be considered by the optimiser. For multi-statement TVFs optimisation is not really possible because it must run to completion, then filter.

Personally, I’d use a stored proc over a multi-statement TVF. They are more flexible (eg hints, can change state, SET NOCOUNT ON, SET XACTABORT etc).

I have no objection to inline TVFs but don’t tend to use them for client facing code because of the inability to use SET and change state.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)