Part 5 — Building it properly › Chapter 33

Views, procedures, functions and triggers

The four programmable objects, what each is genuinely for, and the one that causes most production incidents.

From the advert

“Sviluppo di stored procedure, viste, funzioni e trigger” — the codebase already has business logic inside the database. You will maintain it whether or not you think that was a good idea.

The four objects, and what each is genuinely for

Object	Genuinely for	Costs
View	An interface: a stable name over a query, so the tables underneath can change	Nothing at runtime — it is expanded. Nesting them hides real cost
Stored procedure	A multi-statement unit that belongs next to the data: a transaction, a batch, a permission boundary	Logic that is hard to version, test and diff
Function	A reusable expression (inline table-valued) — genuinely useful	Scalar ones are a plan disaster. See below
Trigger	Auditing and invariants nothing else can express	Invisible action at a distance. The commonest source of "why did that happen?"

Views: an interface, not a cache

🧒 Simple

A view is a nickname for a question. Saying the nickname does not make the answer arrive any faster — somebody still has to go and count. What it does mean is that if the counting changes, everyone using the nickname carries on unaffected.

⚠ Trap — nested views

A view selecting from a view selecting from two views is expanded into one enormous query the optimiser then has to plan. Each level looks reasonable in isolation; four levels down, a SELECT of three columns joins eleven tables and nobody can see why. Nesting more than two deep is where "just use a view" goes wrong. Keep them shallow, and read the plan of the query you are actually running rather than the view you think you are querying.

Updatable views and WITH CHECK OPTION

A simple single-table view is updatable. Add WITH CHECK OPTION and rows that would disappear from the view cannot be written through it — so a view defined as WHERE region = 'EMR' cannot be used to insert a Lombardia row. It is a neat, little-known way to express a scoping rule, and it is a genuine alternative to row-level security on older engines.

Stored procedures: the real argument

🎓 For

Fewer round trips. A five-statement transaction becomes one call.
A permission boundary. Grant EXECUTE on the procedure and nothing on the tables — the application cannot issue arbitrary SQL even if compromised. This is a real security argument and the strongest one.
Change without redeploying the application. Which is also the strongest argument against.
The logic lives with the data, so three applications sharing a database share the rules.

🎓 Against

Version control is awkward and diffs are poor — an ALTER PROCEDURE in a migration is not the same as a file whose history is the truth.
Testing is harder and there are fewer tools.
Business logic in two places is worse than in either, and that is what usually happens.
Changing without a deploy means changing without review, without CI, and without anybody knowing.

✔ The answer that avoids the trap

This question is often asked to see whether you have an ideology. “It depends on who owns the data. One application, one team — I would keep logic in the application, where it is versioned and tested. Several applications sharing one database, or a hard requirement that the app account cannot run ad-hoc SQL — procedures, and I would keep them in source control as files, deployed by a migration tool like anything else.” That is a position, with a reason, and no contempt for the alternative.

Functions: the one that matters

Two kinds of function with the same keyword: one is a per-row disaster, the other is a parameterised view.

-- SCALAR — avoid in a SELECT list or a WHERE clause over many rows.
CREATE FUNCTION dbo.fn_OrderTotal(@id INT) RETURNS BIGINT AS  -- returns ONE value
BEGIN ... END                                                 -- a procedural body
SELECT id, dbo.fn_OrderTotal(id) FROM orders;   -- once per row, no estimate, serial plan

-- INLINE TABLE-VALUED — a parameterised view. Expanded into the calling query and
-- optimised WITH it. This one is genuinely good.
CREATE FUNCTION dbo.fn_OrderTotals() RETURNS TABLE AS         -- returns a ROW SET
RETURN (SELECT order_id,                                      -- a single SELECT, no BEGIN/END
               SUM(quantity * unit_price_cents) AS total_cents  -- the aggregate itself
        FROM order_lines GROUP BY order_id);                  -- one row per order

SELECT o.id, t.total_cents                                    -- read it like a table…
FROM orders o                                                 -- the driving table
CROSS APPLY dbo.fn_OrderTotals() t WHERE t.order_id = o.id;   -- …called once per order row

Why the difference is so large

A scalar function is a black box to the optimiser: no row estimate, no parallelism for the whole query, and one invocation per row. Before SQL Server 2019 it did not even appear in the plan, so the query looked cheap and ran for minutes.
An inline table-valued function has a single SELECT as its body — no BEGIN/END. That lets the engine paste it into the outer query and optimise the whole thing together. It is a view that takes parameters.
RETURNS TABLE AS RETURN (…) is the inline form. RETURNS @t TABLE (…) AS BEGIN … END is the multi-statement form, which is a black box again with a fixed row estimate. The keywords are nearly identical and the performance is not.

⚠ The three-way distinction to remember

Inline table-valued: expanded, estimated, parallelisable — use freely. Multi-statement table-valued: a black box with a fixed row estimate — avoid in a join. Scalar: historically once per row, no costing, forces a serial plan — avoid over large sets. SQL Server 2019's scalar UDF inlining helps, but only for functions simple enough to inline, and you cannot tell by looking.

Triggers

🧒 Simple

A trigger is a note stuck inside the drawer saying “whenever anyone puts something in here, also do this”. Very useful. Also the reason somebody spends an afternoon asking why a row they never touched changed.

🎓 The rules that make them survivable

They fire per statement, not per row. In SQL Server the inserted and deleted pseudo-tables contain all affected rows. A trigger written as though a single row were being changed — SELECT @id = id FROM inserted — is silently wrong the first time somebody updates two rows at once. This is the single most common trigger bug.
They run inside the caller's transaction. So a slow trigger makes every write slow, and a failing trigger rolls back a statement that looked innocent.
They can cascade. Trigger A writes to table B whose trigger writes back to A. Nesting is limited (32 levels in SQL Server) and the error when you hit it is not obvious.
They are invisible. Nothing in the calling code mentions them. Whatever you do, make them findable: name them consistently, and list them in the schema documentation.

✔ Where a trigger is genuinely the right answer

Auditing. An audit row written by a trigger is written no matter which application, script or person made the change — which is exactly the property an audit trail needs and exactly what application-level auditing cannot promise. Almost every other use of a trigger has a better alternative: a generated column, a constraint, a job, or explicit code.

A trigger written the only way a trigger should be written: set-based.

-- Set-based, so it is correct for a multi-row UPDATE. This is the shape.
CREATE TRIGGER trg_orders_audit ON orders AFTER UPDATE AS   -- fires ONCE per statement…
BEGIN                                                       -- …not once per row
  SET NOCOUNT ON;                       -- or the trigger's row count leaks to the caller
  INSERT INTO order_events (order_id, from_status, to_status, at, actor)  -- the audit row…
  SELECT i.id,                          -- …built from a SELECT, not from variables
         d.status,                      -- the OLD value, from `deleted`
         i.status,                      -- the NEW value, from `inserted`
         SYSUTCDATETIME(),              -- UTC, at full precision
         SUSER_SNAME()                  -- who did it, according to the server
  FROM inserted i                       -- one row per row the statement changed…
  JOIN deleted  d ON d.id = i.id        -- …joined to its own before-image
  WHERE i.status <> d.status;           -- only real transitions get an audit row
END                                     -- end of the trigger body

The mistake this shape avoids

A trigger fires once per statement, and inserted and deleted are tables. Writing SELECT @id = id FROM inserted works perfectly for one row and silently audits one arbitrary row out of a thousand-row update. It is the classic trigger bug.
SET NOCOUNT ON stops the trigger's own “1 row affected” reaching the client, where some drivers read it as the statement's result.
The trigger runs inside the writer's transaction: its cost is the writer's cost, and its failure rolls the writer back. That is the argument for keeping it to one insert.
WHERE i.status <> d.status is what stops an update that changed only an address from writing a status-transition row. Note it would also skip a NULL-to-value change — safe here because status is NOT NULL.

Proof that a view is expanded rather than read.

-- In the playground: a view is expanded into the query, not stored.
EXPLAIN QUERY PLAN SELECT * FROM v_order_totals WHERE order_id = 42;  -- ask for the plan
-- You will see the underlying TABLES and the aggregate, not a line saying "read view".

What that tells you

The plan mentions order_lines, not v_order_totals. The view was substituted before planning, so it costs whatever its query costs.
Good news, mostly: the WHERE order_id = 42 is pushed into the view's query, so the aggregate runs over one order rather than all of them. That is the optimiser doing exactly what you would hope.
It also means a view cannot make a slow query fast. If you need that, you need a materialised view or a summary table (chapter 32).

A view is an interface, not a cache. It is expanded and re-executed every time.
Nested views hide cost. Two levels is plenty; read the plan of the query you actually run.
WITH CHECK OPTION stops writes that would fall outside the view's own filter.
The strongest argument for procedures is the permission boundary: EXECUTE only, no table access.
The strongest argument against is logic that changes without review, CI or anyone knowing.
Inline table-valued functions are good; multi-statement ones estimate a fixed guess; scalar ones run per row.
Triggers fire per statement. A trigger that assumes one row is wrong the first time two are updated.
Triggers run in the caller's transaction, so a slow one makes every write slow.
Auditing is where a trigger genuinely wins, because it cannot be bypassed.

Interview questions this chapter answers

Should business logic live in stored procedures?

It depends on who owns the data. One application and one team: I keep it in the application, where it is versioned, reviewed and tested. Several applications sharing one database, or a requirement that the app account cannot run ad-hoc SQL: procedures — kept as files in source control and deployed by a migration tool, not edited in place.

What is wrong with scalar user-defined functions?

Historically they execute once per row, the optimiser has no cardinality estimate for them, and they force the whole query serial. An inline table-valued function used with CROSS APPLY gets the same reuse and is expanded into the query, so it is estimated and parallelisable.

What is the most common bug in a trigger?

Assuming it fires once per row. It fires once per statement, with all affected rows in inserted/deleted, so a trigger written with a scalar variable silently processes one arbitrary row when somebody updates two. The fix is to write it set-based.