Brent Ozar lays out an important point:
You might say, “But SQL Server has statistics on those columns, and it knows what the top values are!” Well, that’s true, but…data can change without the statistics being updated. For example, say that one user logs in right now, and then we run the MAX query again:
Statistics tell the engine what they learned at the time they were ran. If you need guarantees, that’s what constraints are for.