I have a concatenated field that is usually about 80 characters but sometimes has been up to 600 characters. It could conceivably be longer than that. How should I define that field in my table? Nvarchar(max)? Nvarchar(1000)? If I define it as 1000 will that make performance better or worse than max? And if I then try to insert into it with something more than 1000 characters then what will happen? The update attempt would simply error?
I have another table with the same situation except the field is usually about 10 characters and I've observed it as long as 9,033 characters. Is nvarchar(max) basically the only way to address that one?
If I define it as 1000 will that make performance better or worse than max?
The performance of what: inserts, page reads, parsing column values? As for page reads, if most of your queries don't include this multi-valued column, then it might make sense to keep the column off-row. Also, as mentioned earlier, varchar will suffice, if you don't need support for unicode.
If a row's value-set can be updated after insertion, then I'd definately reccomend containing it in a seperate table, which is the proper normalized way to handle this. Because, if you increase the length of a varchar/nvarchar column on a row, then that causes all sorts of problems like index fragmentation and page splits, which can result in more i/o for both writes and reads.
Personally, the only time I use multi-valued columns are integer based bitflags, which is compact (1 bit per value, 64 bits per BigInt). The column can be "parsed" using bitwise operators, which is indexable. Also, it can be updated without resulting in resize of row.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho