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.

indexing a column of that length would seem largely pointless....suggest you look at Full Text Indexinhg — Mitch Wheat, Sep 09 '12 at 05:45

jozi · Accepted Answer · 2012-09-09T06:00:25.507

20

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

edited Sep 09 '12 at 06:00

answered Sep 09 '12 at 05:52

jozi

2,833
6
28
41

1

yeah, but what's the point of indexing a long text column? Other than a covering index (i.e. part of include, and even then its doubtful optimiser would use it) – Mitch Wheat Sep 09 '12 at 05:54
1

That doesn't index the column. It just includes it in the index data. – gliljas Jan 31 '17 at 10:00
Afarin Hossein you are the best. – saber tabatabaee yazdi Aug 21 '21 at 04:50

score 3 · Answer 2 · edited Mar 09 '16 at 00:56

3

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]

edited Mar 09 '16 at 00:56

Tim Penner

3,551
21
36

answered Mar 09 '16 at 00:19

CoachJames

31
1

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

2 Answers2