CODEWITHSUNDEEP
sql-serverindexing
Mazen Abu Taweelih
Mazen Abu Taweelih

Reputation: 647

SQL Server index for large nvarchar column failing

I have an nvarchar column of size 2000. Sometimes when I am inserting, it's failing with the error:

System.Data.SqlClient.SqlException: Operation failed. The index entry of length 1146 bytes for the index 'NonClusteredIndex-20161206-202443' exceeds the maximum length of 900 bytes.

But I don't know what to do. This table has 45m records so it has to have an index.

Any ideas?

Upvotes: 1

Views: 1475

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Indexing such a large string column is usually a rather bad sign. I would first ask if you really want a full-text index. That is much more useful for text than just a regular index.

If you do need an index, then you can use a computed column to extract a certain amount of characters and build an index on that:

alter table t add col100 as (left(col, 100));

create index ind_t_col100 on t(col100);

Be sure to use col100 for any expressions that should use the index.

If your purpose for the index is to guarantee uniqueness, then you can calculate a checksum and the first 100 character and create a unique index on those values.

Upvotes: 3

Related Questions