[SOLVED] creating index on nvarchar(max) column

creating index on nvarchar(max) column

I have table contains three columns. If I select NVARCHAR(MAX) column in my select statement, I see poor performance. Is it possible to create index on a NVARCHAR(MAX) column?.

CREATE TABLE TEST
(
     id primary key,
     product VARCHAR(50),
     Title NVARCHAR(max)
)

INSERTING MILLIONS OF RECORDS....

SELECT product, Title 
FROM TEST

The table contains million of records. How can I create an index for this column? Is it really improve performance of my select statement? Or is there any other method to improve this?

Solution

You can index an NVARCHAR(MAX) with a "Full-Text Search index".

As an aside, the SQL in your post doesn't include a WHERE or a JOIN, so I'm pretty sure that an index won't improve the SELECT performance of all the titles. It will decrease the speed of inserts, of course, because the inserts will be slower if you add an additional index.