There's no urgent need to do this right away. However, ntext is deprecated,
so when you have the chance, you can make the changes. It's not used just
in procs. You can declare nvarchar(max) as a variable, too. The DB size
shouldn't change, since you're storing the same amount of data.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
I would like to know if I should replace all ntext datatypes in my current
database with nvarchar(max) or is it only for use in stored procs? Is it
possible and if so would it make the database size less?

That depends on the average size of the content in your rows. With
nvarchar(max), rows less than a certain size are stored in page, larger than
a certain size are stored in their own separate page. You can configure this
threshold so that when the threshold is threshed (thrished, thrashed, I am
not sure how to conjugate this word) the data is stored in its separate page
by using the sp_tableoption option. The default is 8k.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

To add to Tom and Hilary's comments. Keep in mind that the (max) data types
use standard SQL statements to modify data instead of the nonstandard
WRITETEXT, UPDATETEXT statements. While this means that the (max) data types
are generally easier to use and can take advantage of string functions like
SUBSTRING and LEN, it also means that you may need to modify existing procs
and applications that use statements like WRITETEXT and UPDATETEXT.

See the Books Online topic "Using Large-Value Data Types" for more
information.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

[quoted text, click to view]

I would like to know if I should replace all ntext datatypes in my current
database with nvarchar(max) or is it only for use in stored procs? Is it
possible and if so would it make the database size less?