Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, I will briefly demonstrate the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types, and the impact on performance from using NTEXT/TEXT.

Let’s create a table named Student using the below T-SQL.
create table student
(
student_id int identity(1,1),
student_name varchar(max)
)

Let’s insert a few records into the table using the below T-SQL.
insert student(student_name)
select 'abc'
union
select 'pqr'
union
select 'xyz'

It is obvious from this that you do not have a Clustered Index on the table. Let’s create it using the below T-SQL:

create clustered
index clu_student_stuid on student(student_id)


Now you can see that the Table Scan gets converted to a Clustered Index Scan which means that the base table is completely scanned by the Clustered Index. Now let’s try to improve it’s performance by creating a Non Clustered Index on the column named Student_name using the below T-SQL.

As you can see, a Non Clustered index isn’t allowed to be created on a column using VARCHAR(MAX) as a data type. This will hurt the performance when the there is a large volume of data inside the table and hence this is one of the major disadvantages of using the VARCHAR(MAX) data type.

Full article...