An OrderLines table stores Quantity, UnitPrice and LineTotal. Several applications write to it, and some forget to recalculate LineTotal when Quantity changes. The DBA wants LineTotal to always equal Quantity multiplied by UnitPrice without relying on application code. Which solution meets this requirement?
Select an answer to reveal the explanation.
Short Explanation
Why trust five different apps to do the math when the database can do it for you? A computed column derives LineTotal from Quantity and UnitPrice every time, so it simply can't drift. The nightly job is the trap: it fixes yesterday's mistakes but leaves the data wrong all day.
Full Explanation
A computed (generated) column is defined by an expression over other columns in the same row, so its value is derived by the database rather than written by applications. Defining LineTotal as Quantity multiplied by UnitPrice guarantees the stored or returned value always reflects the current inputs, no matter which application changed them, and applications can no longer write a conflicting value. A DEFAULT supplies a value only when an insert omits the column, applies only at insert time, and in standard SQL cannot reference other columns, so later quantity changes still leave the total stale. A nightly recalculation job leaves the data inconsistent for up to a day and adds a maintenance process that can fail silently. An index on Quantity and UnitPrice improves searches on those columns but performs no calculation and enforces nothing about LineTotal. Exam caveat: computed columns are usually virtual (calculated on read) unless declared stored or persisted, and engines restrict them to deterministic expressions over the same row. Operational check: after the change, update Quantity on a test row and confirm LineTotal reflects the new product immediately, and verify that an attempt by an application to write LineTotal directly is rejected.