sql server - Once again: Stored Procedure vs. TV-UDF -

i know, discussed several times (most discussions years back...).

but - looking through these discussions - feeling, there broadly spread common sense wrong: tv-udfs bad in performance.

many people prefer sps collect data, why?

when tv-udf, i'm speaking single-statement-udf ("inline-udf") only. optimizer handle right clever, if part written directly query. multi-statement-udfs perform worse...

from point of view, why ever should use udf collect data, see these points:

• best performance (i did lot of comparisons!)
• you can use them join or apply , reach complex still readable queries (i use 1 "univers" query consisting of more 30 functions coming more 1000 columns - in seconds!)
• you can call them everywhere (e.g. fill excel-sheet 1 call via odc)
• you can mix them xml-calls perfectly
• if select needs sub-set of columns optimizer skip unneeded parts - not sp
• with inline-udfs optimizer can predict row count , able use capabilities of indexes, statistics... not sp
• with inline-udfs there's no need write whole table definition insert when want use data in latter
• you "wrap" udf select count(*) from(select * dbo.myfunc()) predict row count. optimizer execute parts needed count...
• and - last not least - udf never writes anything, lighter in point of locking

i use multi-statement-udfs when result 1 distinct row in case , sps when - rare - cursor or dynamic sql needed.

so: why many people using sps collect data? why many people think, udfs bad? there reason or old common sense?

thank inputs!

i think comparing apples , oranges , @ least have never seen discussions this. there discussions if should use udfs @ , there discussions if should use stored procedures or ad hoc sql.

an inline udf can use in query , stored procedure can execute , of bullet points consequence of difference.

an inline udf more view stored procedure. parameterized view can used in queries , can sometimes used speed things up.

best performance (i did lot of comparisons!)

i see scenario inline udf , stored procedure same thing , have different performance.

and - last not least - udf never writes anything, lighter in point of locking

if stored procedure never writes there no difference in locking.

so: why many people using sps collect data?

don't know people me discussion on stored procedure vs ad hoc sql. prefer stored procedures other prefer ad hoc. if want use user defined functions instead of procedures end in ad hoc sql camp.