February 17, 2009
Unless you are expect to have a lot of rows where the text is fairly large, NVARCHAR(MAX) is the way to go.
The reason is that it stores the text in row by default. NTEXT stores the text in LOBs by default.
Also, NTEXT is deprecated in SQL2005 and forward.
See this blog article for a complete analysis: Making it Faster
Applies to: SQL Server 2005, SQL Server 2008