Search code examples
sql-servermaxlengthnon-clustered-index

What is the max varchar length for creating non-clustered index in sql server?

Creating a non-clustered index on columns of type varchar(max) is not allowed in SQL Server.

So, what is the maximum varchar length that a non-clustered index be created on?

Solution

  • Key column length should not exceed 900 bytes, but with SQL Server 2016 cu2, this has increased to 1700 bytes.

    You can include max columns as included,though they should be not part of key columns..

    create table t1
(
    col1 varchar(1700),
    id varchar(max)
)

create index nc on t1(col1) 
include (id)

    Just to add, from SQL Server 2012, you can also rebuild index columns which are of LOB type, though text, ntext and image are not supported..

    Remus Rusanu has a good blog on why online index rebuild operations were not supported in earlier versions of 2012.

    Further reading: