Is a nvarchar(max) less performant than a nvarchar(100) for instance?

Question

Is a nvarchar(max) less performant than a nvarchar(100) for instance?

Answer 1

Same question was answered here (SO) and here (MSDN)

Quoting David Kreps's answer:

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...