NVARCHAR (MAX) size in SQL Server

Question

When I define a column as NVARCHAR(MAX)I cannot index this column. But I definitely can't determine the column as NVARCHAR(4000), since I assume the data row will be longer.

Can anyone suggest how to index a column with data definition, NVARCHAR(MAX)or can you increase the length from 4000 to more?

Answer 1

• You have either nvarchar (4000) or nvarchar (max). Nothing between
• The maximum length of the column (s) of the index column is 900 bytes, so you cannot index nvarchar (4000) either (which is 8000 bytes)

, nvarchar (max).
1 ?

, - HASHBYTES .
HASH.

, , - nvarchar (max)

SQL Server 2014 8000 .

SQL Server 2016,
SHA2_512 . , MD4, .

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

: LIKE. = <>