Have you ever faced the problem of assigning more than 8000 characters to varchar data type or more than 4000 characters to nvarchar data type? If your answer is yes, then I can understand your frustration due to this small limit.
Since SQL Server uses 8KB page to store data to disk therefore, it does not allow you more than 8000 characters in varchar or 4000 (2 Bytes per Unicode character) in nvarchar.
But wait, don’t get disappointed, Microsoft came up with Varchar(MAX), NVarchar(MAX) and VarBinary(MAX) data types in SQL Server 2005 which allows you to save upto 2GB in a single variable. The best part is that It allows you to use these data types as stored procedure parameters, internal variables etc.
So stop using TEXT and NTEXT data types because they are just there for backward computability and will be deprecated in next versions of SQL Server.
(Found this article on http://sqltips.wordpress.com/2007/05/28/use-varcharmaxnvarcharmax-instead-of-text-ntext/ and wanted to add it here for others to read!