Functions vs stored procedures vs views vs CTEs vs temp tables
SQL gives you a lot of tools. Overusing one of them (usually stored procedures or temp tables) can make systems hard to evolve. Here’s how I think about each one.
1. Stored procedures
I use stored procedures for:
- Clear units of work (e.g., “close invoice”, “post transaction”).
- Operations that bundle multiple statements and need to be atomic.
- Places where permissions should be scoped to the procedure.
2. Functions (scalar / table-valued)
I use functions sparingly:
- Scalar functions for small calculations that don’t kill performance.
- Inline table-valued functions as reusable query fragments.
I avoid multi-statement TVFs in hot paths — they can hide poor plans.
3. Views
Views are good for:
- Providing a stable read model to consumers.
- Hiding join complexity from reporting queries.
I treat them as lenses over data, not as a replacement for well-designed tables.
4. CTEs
Common Table Expressions are great for readability:
- Breaking complex logic into named steps.
- Recursive queries (hierarchies, trees).
They don’t guarantee reuse or materialization; I still check execution plans.
5. Temp tables
Temp tables are useful when:
- You genuinely need to persist intermediate results.
- You need to index those intermediates differently.
I avoid using them as a default; if a single set-based query works, I prefer that.
6. Putting it together
Roughly:
- Start with readable inline SQL (maybe with CTEs).
- Extract to views / inline TVFs when reuse makes sense.
- Use stored procedures for business operations and permission boundaries.
- Reach for temp tables only when you’ve proven they help.