Composable logic

Composable logic is what some developers use to make a single T-SQL statement do more than one thing, which allows us to reuse the same code for multiple tasks. When writing procedural code, reusability is desired because it makes the code more concise and maintainable. It allows developers to create libraries of modules that can be reused in other areas of the application, or even in other applications altogether. In T-SQL, however, there can be a hefty performance penalty for writing generic reusable code.

For the SQL Database Engine to execute a query in the most efficient way, it needs to estimate the cost of the query and choose operators that will return the results in the cheapest way possible. This is all done at compile-time based on how the query is written. With composable logic, however, the true cost of the query cannot be known until runtime because it is based on variables that change whenever the query is run. This type of generic code causes the...