Functions aren’t your friends

- [Instructor] Many developers seem to regard SQL Server…as a black box where magic happens,…good magic and bad magic.…Good magic is good, but having a full understanding…of your platform is better.…SQL Server doesn't always help developers…understand these things.…One place in particular where it does itself no favors…is tolerating and maybe avoiding the use of features…with known problems rather than fixing them.…SQL Server has continued to ignore issues…like we're talking about here.…The performance of scalar UDFs, or user-defined functions.…

This is something that comes up frequently…within the framework code, or just developers who are used…to writing functions in their C# code…and want to translate that design pattern to T-SQL.…The problem with UDFs is that the optimizer deals…with them very inefficiently.…It generally can't decompose and rearrange the function…so that it was inline, so the function gets executed once…for every single row returned.…This often has a huge performance impact.…There is some work that Microsoft Research has done,…