Text and image functions operate on TEXT, NTEXT and IMAGE data types. These data types are deprecated with SQL Server 2005 and should be replaced by VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) when possible. Alternatively you can also consider storing large string values using XML data type. Text and image functions are nondeterministic.
Future versions of SQL Server will not support TEXT, NTEXT and IMAGE data types. Columns with TEXT / NTEXT data type do not support commonly used string functions such as LEN, LEFT, RIGHT, etc. Furthermore, due to the large size of TEXT data you're likely to see performance issues if you store such data in the database. However, at times you can't help but use the TEXT / NTEXT / IMAGE data types (for example when supporting 3rd party databases when you cannot change the schema) - this is when text and image functions come in handy.
View Complete Post