Functions vs complex joins in SQL

Is it a good practice to use make functions (both scaler and table valued) over using complex joins with multiple tables again and agai开发者_如何学Pythonn. For instance

Case 1:

SELECT 
*
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID=t2.ID
INNER JOIN table2 t3 ON t2.ID=t3.ID
INNER JOIN table2 t4 ON t3.ID=t4.ID
INNER JOIN table2 t5 ON t4.ID=t5.ID
INNER JOIN table2 t6 ON t5.ID=t6.ID
INNER JOIN table2 t7 ON t6.ID=t7.ID

CASE 2

SELECT *
FROM table1 t1
INNER JOIN udf_myFunc() udf ON udf.ID=t1.ID

Are there cases where one method has definite advantages?

For readability, #1 is better - it's clear what the object is doing, even though it's more verbose.

For re-use, #2 is better - especially if you see the logic possibly changing across many calls, it's better to change it in just one place.

I guess it depends on what your goals are. I typically choose readability over re-use.

No.

• If you want to hide/manage complexity and enable re-use, use a View
• You will not get a performance increase using UDFs in joins
• You may want to set up an indexed View - that may lead to better performance
• UDFs are difficult to organize and manage in MS SQL. Leaning on them too much could quickly end up with a mess of UDFs

It depends.

• A multi-statement table-valued function is a performace killer as compared to a join. It cannot be unwound and integrated into the query. Particularly bad when used in apply.
  Sometimes, however, it is a benefit, because a join that yiels same results is a nightmare.
• An inline table-valued function is fine. The server can unwind it and integrate into the query. There will be no difference in performance, the execution plan will be the same as for the join.
  So it might be a good option to incapsulate some logic without degrading performance.
• A scalar function is mostly fine, except for cases when the query optimiser does strange things and begins to call it much more times than it really should. In this situation it is possible to rewrite such function as a table-valued function that returns one row with one column and join it as a table instead of using the value in the where condition.
  But otherwise it's fine, too.

I have to vote against the function direction. If for no other reason, it obfuscates intent. In addition, while it may save a bit of work if you reuse it, that is the only likely benefit, as it is not going to increase the performance.

I like functions when you have to massage a particular piece of data, but to use one to avoid joins is a nasty way to handle things.