Sql-server – Creating an Index on a varchar(1024) column succeeded. Does it work

sql serversql-server-2012

When I created an Index on a varchar(1024) column using SSMS GUI, it failed with the message:

"Warning! The maximum key length is 900 bytes. The index 'XXX' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail."

However, the same operation succeeded when I used SQL (not GUI). I'm not sure why this happened, but does this mean the index will work until the data over 900 bytes really is inserted into the column?

(The column currently doesn't have value over 900 bytes.)

environment:
– SQL Sever 2012
– Windows Server 2008 R2 SP1

Best Answer

All versions

If you create an index on a column that its definition is longer than 900 or 1700 (depending on version and type of index), you'll either get a warning and the index creation will succeed (if the table is empty or the existing data are not that long) or an error and the index creation will fail (if a row or more exceed the limit).

For the warning case, the limits are:

SQL Server 2014 and Older

If you create a clustered index on a varchar(>900) column you will receive a warning when creating the index.

If you create a non-clustered index on a varchar(>900) column you will receive a warning message when creating the index.

SQL Server 2016 and Newer

If you create a clustered index on a varchar(>900) column you will receive a warning message when creating the index.

If you create a non-clustered index on a varchar(>1700) column you will receive a warning message when creating the index.

Inserting Data

If you don't insert more data than is limited by the index and the version of SQL Server then you will not receive an error message in your application / SSMS / SQL Query / ...

However, inserting more than the limits specified by the version and index type will result in an error message.

Testing

You can try this out over at db<>fiddle. The links for each test are provided below the test examples.

Varchar Non-Clustered Index Limitations on SQL Server 2012

CREATE TABLE VarcharNonClustered
(
  ID INT
  ,Name VARCHAR(2000)
)

GO

✓

CREATE NONCLUSTERED INDEX idx_VarcharNonClustered_Name ON VarcharNonClustered(Name)
GO

✓

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

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

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

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

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1700 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharNonClustered 
VALUES (1,REPLICATE('a',901))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 901 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

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

1 rows affected

db<>fiddle here

Varchar Clustered Index Limitations on SQL Server 2012

CREATE TABLE VarcharClustered
(
  ID INT
  ,Name VARCHAR(2000)
)
GO

✓

CREATE CLUSTERED INDEX idx_VarcharClustered_Name ON VarcharClustered(Name)
GO

✓

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

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

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

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

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1700 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharClustered 
VALUES (1,REPLICATE('a',901))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 901 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

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

1 rows affected

db<>fiddle here

Varchar Non-Clustered Index Limitations on SQL Server 2016

CREATE TABLE VarcharNonClustered
(
  ID INT
  ,Name VARCHAR(2000)
)

GO

✓

CREATE NONCLUSTERED INDEX idx_VarcharNonClustered_Name ON VarcharNonClustered(Name)
GO

✓

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

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

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

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

1 rows affected

INSERT INTO VarcharNonClustered 
VALUES (1,REPLICATE('a',901))
GO

1 rows affected

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

1 rows affected

db<>fiddle here

Varchar Clustered Index Limitations on SQL Server 2016

CREATE TABLE VarcharClustered
(
  ID INT
  ,Name VARCHAR(2000)
)
GO

✓

CREATE CLUSTERED INDEX idx_VarcharClustered_Name ON VarcharClustered(Name)
GO

✓

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

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

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

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

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

INSERT INTO VarcharClustered 
VALUES (1,REPLICATE('a',901))
GO

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

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

1 rows affected

db<>fiddle here

Summary

  • SQL Server 2012 + Non-Clustered Index on Varchar() = 900 characters max
  • SQL Server 2012 + Clustered Index on Varchar() = 900 characters max
  • SQL Server 2016 + Non-Clustered Index on Varchar() = 1700 characters max
  • SQL Server 2016 + Clustered Index on Varchar() = 900 characters max

Your Question

Creating an Index on a varchar(1024) column succeeded. Does it work?

It will work with the above limitations.

References

Related Question