Indexing an NVARCHAR(MAX) column in SQL Server

Question

When I define a column as NVARCHAR(MAX), I can't index that column. But I certainly can't define the column as NVARCHAR(4000) since I assume the data string will be longer sometimes.

Can anyone suggest how to index the column with NVARCHAR(MAX) data definition or is it possible to increase the length from 4000 to more?

Answer 1

1. You have either nvarchar(4000) or nvarchar(max). Nothing in between
2. Maximum length of the index key column(s) together is 900 bytes, so you can't index nvarchar(4000) either (which is 8000 bytes)

Personally, I can't see why you need to index nvarchar(max).
Are you seriously going to search for strings up to 1GB long?

Anyway, your only option is to use HASHBYTES in a persisted computed column.
You create a hash of the column, and index the HASH.

Note, depending on what version you may not be able to hash nvarchar(max)

For SQL Server 2014 and earlier, allowed input values are limited to 8000 bytes.

SQL Server 2016 code example that also enforces uniqueness
The SHA2_512 hash gives the least chance of collision. For a sufficiently large table a weaker hash like MD4 will hit the birthday problem

CREATE TABLE dbo.HashExample (
    SomeID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    SomeLongText nvarchar(MAX) NULL,
    SomeHash AS HASHBYTES('SHA2_512', SomeLongText) PERSISTED
)
GO
CREATE UNIQUE INDEX UX_SomeHash ON dbo.HashExample(SomeHash) WHERE SomeLongText IS NULL
GO

INSERT dbo.HashExample (SomeLongText) VALUES ('Row 1'), ('Row 2')
GO
SELECT * FROM dbo.HashExample
GO

DECLARE @LookFor nvarchar(MAX) = 'Row 3'
SELECT * FROM dbo.HashExample WHERE SomeHash = HASHBYTES('SHA2_512', @LookFor)
SET @LookFor = 'Row 2'
SELECT * FROM dbo.HashExample WHERE SomeHash = HASHBYTES('SHA2_512', @LookFor)
GO

Note, you can't have LIKE searches. Only = or <>