Is a nvarchar(max) less performant than a nvarchar(100) for instance?
sql-server
Solution
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...
Problem
Is a nvarchar(max) less performant than a nvarchar(100) for instance?