Code reuse and modularity in SQL

Question

Is code reuse and modulatiry a good idea for SQL Stored Procedures programming?

And if so, what's the best way to add these features to a SQL stored procedure code base?

I usually create scalar valued functions for tasks that are common and repeated. I find that it eases both development of new procedures similar to existing ones, but also aids a lot in bugtracking and troubleshooting.

I try to stay away from table valued functions though, due to performance issues.

My rule of thumb is that if it is a calculation, and it's used in several places, then I create a scalar valued function.

Answer 1

You are going to find that using functions within your queries is a disaster for performance. The functions become a black box for the optimizer, so you will end up re-coding the function call back into the query to make it run fast once you get up to a large number of rows in your tables.

A better way to deal with common calculations is to insert them into a new column with a trigger, or in your insert/update queries. That way you can index the calculated value and use it directly instead of figuring it out each time you need it.

Answer 2

Sql doesn't give you a lot of flexibility when it comes to code reuse. I usually create functions when it comes to calculations or other tasks that don't involve modifying tables. But all tasks that involve writing to tables and that sort of things I usually use a stored procedure to get a better control of the transactions.