navtec (8/24/2016)
Ok thanks.Still very strange why SQL Server would pick up on nvarchar data type as a LOB data type though.
Go and read the documentation on the (MAX) setting for NVARCHAR/VARCHAR. It changes the storage mechanism for the data from row storage to LOB storage and allows up to 2gb for each NVARCHAR/CHAR column. The TEXT data type is deprecated and will, in theory, go away in a future release of SQL Server. It has been replaced by the (MAX) setting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning