Is there any reason why one would not use ntext and specifying no maximum length for all text entry fields?
Asked
Active
Viewed 891 times
2
-
1Apart from the use of a deprecated type which adds an additional reason not to most of the answers here will still apply. [Are there any disadvantages to always using nvarchar(MAX)?](http://stackoverflow.com/q/148398/73226) – Martin Smith Oct 07 '11 at 09:54
3 Answers
5
One reason for not using ntext is that it will be removed from future versions of SQL Server.
http://msdn.microsoft.com/en-us/library/ms187993.aspx
You should use nvarchar(max) instead of ntext.
I would not use nvarchar(max) for all text fields. I like to have some control over what and how much gets stored. It is for instance a lot easier to build a client that needs to present the information if you know how much text is to be expected. Also users that wants to do bad things can fill a zip code field with all five books of the trilogy Hitch Hikers Guide to the Galaxy. Not that the books are bad but it should not be part of a zip code.
Mikael Eriksson
- 136,425
- 22
- 210
- 281
0
Another reason to add to this apart from it being a depreciated type is that ntext cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators.
You will get the following error message
The data type ntext cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
I suggest to stop using TEXT as it is obsolete and Cast it to NVARCHAR(MAX):
Community
- 1
- 1
Joshua Duxbury
- 4,892
- 4
- 32
- 51
0
One reason is to do with how it's stored (although this depends on how much you store) and also partly do with restrictions on using some functions with 'text' type datatypes.
In addition, if you are worried about length, the nvarchar(max) is the recommended data type to use these days.
See here for more:
SQL Server Text type vs. varchar data type
http://geekswithblogs.net/johnsperfblog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx
