I run across this issue occasionally and am curious if there are better design methods to use that would work better than the ones I’ve used.

A relation database table with some varchar fields- you design the fields to be big enough to handle your longest string needed, but over time you may encounter the occasional valid string that is much longer than the field can accommodate.

Increasing the varchar field to handle this much longer string will work, but at the expense of decreasing efficiency from extra storage and potentially decreased query efficiency etc. Change to a text/nvarchar(max) field can create other issues. Splitting a long string across multiple table rows makes a query headache.

Are there current best practices for designing around these scenarios and trying to accomplish the best balances of tradeoffs?