Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using
VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should always just use
VARCHAR(MAX) and linked Why use anything but varchar(max). I read it but it seemed dated, as it was focusing on 2005, and didn't seem to offer any real justification to allocate potentially up to 2 GB per row on all text fields.
From a performance, storage, etc. standpoint, how should one go about deciding whether to use
VARCHAR(MAX) or a smaller more specific type for modern versions of SQL Server? (e.g., 2008, 2012, 2014)