Is there a downside by chosing ntext as datatype for all text columns? [duplicate]

This question already has answers here: Closed 12 years ago.

Possible Duplicate:

Are there any disadvantages to always using nvarchar(MAX)?

Is there a general downside by ch开发者_JAVA百科osing 'ntext' as column type instead of a type that contains chars but with a limited max size like 'char' or 'varchar'?

I'm not sure whether a limited column size is applyable to all my colums. Therefore I would use 'ntext' for all columns containing text. Might this lead to problems in the future?

(I'm using Linq-To-SQL in a ASP.net Webforms application)

---

NTEXT is being deprecated for a start, so you should use NVARCHAR(MAX) instead.

You should always try to use the smallest datatype possible for a column. If you do need to support more than 4000 characters in a field, then you'll need to use NVARCHAR(MAX). If you don't need to support more than 4000 characters, then use NVARCHAR(n).

I believe NTEXT would always be stored out of row, incurring an overhead when querying. NVARCHAR(MAX) can be stored in row if possible. If it can't fit in row, then SQL Server will push it off row. See this MSDN article.

Edit:
For NVARCHAR, the maximum supported explicit size is 4000. After that, you need to use MAX which takes you up to 2^31-1 bytes.
For VARCHAR, the maximum supported explicit size is 8000 before you need to switch to MAX.

---

In addition to what AdaTheDev said, most of the standard T-SQL string functions do not work with NTEXT data types. You are much better off using VARCHAR(MAX) or NVARCHAR(MAX).

---

NVARCHAR furthermore is for widechars, e.g. non latin letters.

I had a stored procedure which ran with NVARCHAR parameters, when I changed it to use VARCHAR instead, I more than doubled the performance.

So if you know you won't need widechars in your columns, you're best of using VARCHAR.

and like the other answers says, don't use TEXT/NTEXT at all, they're deprecated.

---

You can never have an index on any text column because an index is limited to 900 bytes.

And ntext can't be indexed anyway, but there are still limitations on newer BLOB types too.

Do you plan on having only non-unique text columns? Or never plan to search them?