I just happened to read the Maximum Capacity Specification for SQL Server 2008 and saw a maximum of 8060bytes per row? What the... Only 8KB per row allowed? (Yes, I saw "row-overflow storage" special handling, I'm talking about standard behavior)
Did I misunderstand something here? I'm sure I have, because I'm sure I saw binary objects with several MB sizes stored inside SQL Server databases. Does this ominous per row really mean a table row as in one row, multiple columns?
So when I have three nvarchar columns with each 4000 characters in there (suppose three legal documents written in textboxes...) - the server spits out a warning?
Yes, you'll get a warning on CREATE TABLE, an error on INSERT or UPDATE
LOB types (nvarchar(max), varchar(max) and varbinary(max) allow 2Gb-1 bytes which is how you'd store large chunks of data and is what you'd have seen before.
For a single field > 4000 characters/8000 bytes I'd use nvarchar(max)
For 3 x nvarchar(4000) in one row I'd consider one of: