creating index on nvarchar(max) column

Question

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?

Taryntaryne

You can't index an nvarchar(MAX) column. According to the documentation, "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. – Turbinate

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. – Volnak

Thanks.shall we use include(index) like below? is it correct and useful? create index ix_test on test(id)include(title) – Taryntaryne

Answer 1

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.

Indoeuropean

Answer 2

When this question was posted for SQL Server 2012 it was true that 900 bytes was the limit for any type of index key (both clustered and non clustered) this has changed in later versions of SQL Server.

Up to SQL Server 2014

Any type of index key can be maximum 900 bytes.

So maximum nvarchar should be something like nvarchar(448)

SQL Server 2016 and above

Clustered index key: 900 bytes Non clustered index keys: 1700 bytes

So maximum nvarchar should be something like nvarchar(848)

Reference: https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2016

Epistle