Denormalizing Through Join Indexes
Join indexes provide the performance benefits of prejoin tables without incurring update anomalies and without denormalizing your logical or physical database schemas.
Although join indexes create and manage prejoins and, optionally, aggregates, they do not denormalize the physical implementation of your normalized logical model because they are not a component of the fully normalized physical model.
Remember: normalization is a logical concept, not a physical concept.
Example
Consider the prejoin example in “Denormalizing Through Prejoins” on page 129. You can obtain the same performance benefits this denormalization offers without incurring any of its negative effects by creating a join index.
CREATE JOIN INDEX EmployeeJob
AS SELECT (JobCode, JobDescription), (EmployeeNumber, EmployeeName)
FROM Job JOIN Employee ON JobCode;
This join index not only eliminates the possibility for update anomalies, it also reduces storage by row compressing redundant Job table information.