Press "Enter" to skip to content

Creating Goal Post Tables

Aaron Bertrand solves a problem of unchecked growth:

Many of us deal with logging tables that grow unchecked for years, while reporting queries against them are expected to continue running quickly regardless of the size of the table. A common issue when querying by a date range is that the clustered index is on something else (say, an IDENTITY column). This will often result in a full clustered index scan, since SQL Server doesn’t have an efficient way to find the first or last row within the specified range. This means the same query will get slower and slower as the table grows.

I like this solution but only in cases where you expect no after-the-fact updates to dates, such as late-arriving date information or “fixing” the date later. With Aaron’s log example, where we expect log entries to be immutable, this can work really well in a “pseudo-materialized view” sort of way.