I came to know that,
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. (http://msdn.microsoft.com/en-us/library/ms187993%28v=sql.90%29.aspx)
Now I have some tables with Text columns. They contain a lot of text, my concern is does a Varchar(max) can keep our needed length of text instead?
Should I update my table and change the datatype to Varchar(Max)? Will it effect the already existing data?
I am using SQL Server 2008.
VARCHAR(MAX) is the recommended replacement for TEXT, NVARCHAR(MAX) the one for NTEXT. But you cannot simply do an ALTER TABLE and change these fields without reviewing your uses first -- in particular, TEXT and NTEXT have special functions for editing them (READTEXT, WRITETEXT, UPDATETEXT) that don't work for (N)VARCHAR(MAX) (and are partially unnecessary, since the regular string operations just work). Updating very big MAX fields efficiently (as in, megabytes of data that need to be modified somewhere in the middle) is done through UPDATE ... C.WRITE() instead.
If you don't use these functions anywhere and all code simply sets values (which is common), then changing the column types should be no problem, but be aware that this requires rebuilding the entire table and is a potentially expensive operation requiring downtime if your tables are large.
For new work, don't use the TEXT types. For existing software, review your needs. Eventually TEXT and NTEXT support will be removed entirely, but that time has not arrived yet -- as of SQL Server 2014, they are still supported, with no indications as to when they will be finally removed.
Please find the equivalent for text,ntext and image datatypes for forward compatibility.
text -> varchar(max)
ntext -> nvarchar(max)
image -> varbinary(max)
Check for Deprecated Datatypes here.
VARCHAR(MAX). AndVARCHAR(MAX)can hold 2 gigabytes (2 billion characters) in each column - that's enough for roughly 175 copies of Leo Tolstoj's War and Peace - in a single column! Hard to believe you'd ever outgrow that...... The conversion toVARCHAR(MAX)is non-destructive, any text contained in that column will be preservedTEXTcan do the same thing, so that's not why you upgrade.VARCHAR(MAX)now finally support all the usual string functions - whileTEXTonly supported a frustratingly limited subset of string functions....