SQL Server: Avoid the use of Indexes on VARCHAR columns

 I am also adding a Database Design category for this post, because what I am feeling that you guys must know about this before designing tables or indexes.

You can create Indexes on VARCHAR columns, but you should not create indexes on big size of VARCHAR Columns.

Indexes also require storing data as key for accessing associated records. There is a size limitation of index key values.

Before SQL Server 2016, Index key size limitation was 900 bytes for Clustered and Non-Clustered index column.
SQL Server 2016 increased this limitation for Non-Clustered Index and now it is up to 1700 bytes, but for Clustered Index column it is still 900 bytes.

Let me demonstrate this.

Create a sample table with Nonclustered Index:

1 2 3 4 5 6 7 8 9

CREATE TABLE tbl_TestVarcharNonClustered ( ID INT ,Name VARCHAR(2000) ) GO   CREATE NONCLUSTERED INDEX idx_tbl_TestVarcharNonClustered_Name ON tbl_TestVarcharNonClustered(Name) GO

You will get a below warning:
Because we have set Name VARCHAR(2000).

1	Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'idx_tbl_TestVarcharNonClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

Try to insert 1701 bytes data:

1 2 3

INSERT INTO tbl_TestVarcharNonClustered VALUES (1,REPLICATE('a',1701)) GO

You will get a below error:

1 2	Msg 1946, Level 16, State 3, Line 1 Operation failed. The index entry of length 1701 bytes for the index 'idx_tbl_TestVarcharNonClustered_Name' exceeds the maximum length of 1700 bytes for nonclustered indexes.

You can successfully run below INSERT, because it inserts only 1700 bytes:

1 2 3

INSERT INTO tbl_TestVarcharNonClustered VALUES (1,REPLICATE('a',1700)) GO

Now, create a sample table with Clustered Index:

1 2 3 4 5 6 7 8 9

CREATE TABLE tbl_TestVarcharClustered ( ID INT ,Name VARCHAR(2000) ) GO   CREATE CLUSTERED INDEX idx_tbl_TestVarcharClustered_Name ON tbl_TestVarcharClustered(Name) GO

You will get a below warning:
Because we have set Name VARCHAR(2000) on cluster index column.

1	Warning! The maximum key length for a clustered index is 900 bytes. The index 'idx_tbl_TestVarcharClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

Try to insert a over size data like more than 900 bytes:

1 2 3

INSERT INTO tbl_TestVarcharClustered VALUES (1,REPLICATE('a',1001)) GO

You will get a below error:

1 2	Msg 1946, Level 16, State 3, Line 1 Operation failed. The index entry of length 1001 bytes for the index 'idx_tbl_TestVarcharClustered_Name' exceeds the maximum length of 900 bytes for clustered indexes.

You can successfully run below INSERT, because it inserts only 900 bytes:

1 2 3

INSERT INTO tbl_TestVarcharClustered VALUES (1,REPLICATE('a',900)) GO