I am working on an error in my application and found something that I don't understand.
One of my columns has the type nvarchar(max) and contains strings with a length of 65k chars.
Selecting the column in my query causes a performance issue,
SELECT Id
,Description
,DescriptionForUser
FROM MyTable
The query takes 15 sec to execute, but when using substring(colname,1,100000)
SELECT Id
,Description
,SUBSTRING(DescriptionForUser,1,100000)
FROM MyTable
The query execution takes under a second.
What causes this performance change?