2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Simple Code
  • 2,354
  • 2
  • 27
  • 56

2 Answers2

5

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:

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • If I have description column included in an index, and after that I executed the following query "select from table1 where description='%some text%'" would that be like creating a non-clustered index on description column or not? – Simple Code Mar 01 '18 at 08:55
  • Are you saying if description column is in included list of the index and how your query in comment performs ? – TheGameiswar Mar 01 '18 at 08:57
  • yea I meant if I put description column in an included list of non-clustered index what is the benefit when I execute search query on only the description column – Simple Code Mar 01 '18 at 09:01
  • 1
    SQL can't use indexes on queries using `like` and in your above case,since the column is in included list..even with out using `like` sql might scan the total non clustered index – TheGameiswar Mar 01 '18 at 09:03
  • I have in my project description column which is 400 characters length and it's playing a key role in all my search queries(like queries), wouldn't be good to created non-clustered index on that column(which is relatively long string) – Simple Code Mar 01 '18 at 09:04
  • 2
    I think you should go with full text search instead of like – TheGameiswar Mar 01 '18 at 09:04
2

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

https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server

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

MJH
  • 1,710
  • 1
  • 9
  • 19