5

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ram
  • 727
  • 2
  • 16
  • 33
  • 3
    You can't index an `nvarchar(MAX)` column. According to [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql), "columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index". An index won't typically improve performance of a query without a `WHERE` or `JOIN` clause. – Dan Guzman Dec 13 '18 at 17:09
  • 5
    The **max size** for an index entry in SQL Server is **900 bytes** - and that's the **possible** size of a variable length column - so for `NVARCHAR`, the max would be a `NVARCHAR(450)` column. Anything bigger **CANNOT** be indexed. – marc_s Dec 13 '18 at 17:23
  • Thanks.shall we use include(index) like below? is it correct and useful? create index ix_test on test(id)include(title) – Ram Dec 14 '18 at 01:44

1 Answers1

3

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.

Oleksandr Sharparchitect
  • 3
  • 2
DharmaTurtle
  • 6,858
  • 6
  • 38
  • 52