How can I create index on nvarchar(max) datatype in sql?

Question

I have a table with nvarchar(max) datatype column. Max length of data in this column is 37000, ,then I can't use nvarchar(4000). How can I create index for this column ? My data is unicode text in Persian.

Answer 1

1- you could use it in an "INCLUDE"

IF OBJECT_ID('tempdb..#example') IS NOT NULL 
BEGIN
    DROP TABLE #example
END

CREATE TABLE #example (id INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(MAX))

CREATE NONCLUSTERED INDEX IDX_NC_temp_example_name ON #example(id) INCLUDE(name)

2-or You may consider to use "CHECKSUM" method. It's inteded for buidling hash indexes, especially to improve indexing speed for indexing long character columns (as you have). You can read more and find examples: http://msdn.microsoft.com/en-us/library/ms189788.aspx

Answer 2

Best is using DROP_EXISTING = ON, which does a rebuild using existing index.

Here is an example

CREATE NONCLUSTERED INDEX IDX_NC_temp_example_name ON #example(id) INCLUDE(name)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = ON, DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]