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

yhived7q 于 2023-04-10 发布在 SQL Server

关注(0)|答案(2)|浏览(142)

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?

2条答案

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:

The maximum number of bytes for a non-clustered Index (pre 2016) is 900:

So, give VARCHAR(900) a go and let us know how you get on.