Tags: Database Index , MSSQL

Today I was creating an Index on columns and got warning from SQL server. I never know that Microsoft SQL Server has put Maximum size limitation on Index key column. I prefer to write SQL script rather design mode of SQL Server Studio. I got warning message in console as follow...

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

I did Google for more information and read few articles and also SQL Server library articles. Lets understand warning message with example.

Step 1: First create table. Lets assume below table for the sake of example.

CREATE TABLE [dbo].[index_master](
 [id] [bigint] IDENTITY(1,1) NOT NULL,
 [indexCol1] [nvarchar](450) NULL,
 [indexCol2] [bigint] NULL,
 [indexCol3] [int] NULL,
 [indexCol4] [varchar](442) NULL,
 [indexCol5] [varchar](450) NULL
) ON [PRIMARY]

Step 2: Find out individual column size in bytes.

SELECT name, max_length as size_in_bytes
FROM sys.columns
WHERE OBJECT_NAME(object_id) = 'index_master'

Step 3: Create an index on indexCol2 [8] + indexCol3 [4] + indexCol4 [442] + indexCol5 [450] = 904 bytes.

CREATE NONCLUSTERED INDEX [IndxNc_index_master__MultiCol01] ON [dbo].[index_master] 
(
 [indexCol3] ASC,
 [indexCol4] ASC,
 [indexCol5] ASC,
 [indexCol2] ASC
)WITH (PAD_INDEX  = ON, FILLFACTOR = 80)

/* If you create an index using above script, it'll gives you warning as follow */
/* Warning! The maximum key length is 900 bytes. The index 'IndxNc_index_master__MultiCol01' has maximum length of 904 bytes. For some combination of large values, the insert/update operation will fail.*/

/* If you create an index using SQL Studio design mode, it'll gives you warning as follow */
/* Adding the selected columns will result in an index key with a maximum length of 904 bytes.  The maximum permissible index length is 900 bytes. INSERT and UPDATE operations fail if the combined value of the key columns exceeds 900 bytes. */

Step 4: Lets try to insert record with random data to check what happens if you try cross the limit of 900 bytes.

INSERT INTO index_master values(
'indexCol1',
1234567891012312312,
12345,
'12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218',
'123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186');

It'll throw an error as follow...

Operation failed. The index entry of length 902 bytes for the index 'IndxNc_index_master__MultiCol01' exceeds the maximum length of 900 bytes.

Notes
- If a table column is a Unicode data type such as nchar or nvarchar, then column size in bytes is two times the number of characters specified in the CREATE TABLE statement. Check indexCol1.
- If you have index that exceed the 900 bytes, It'll allow insert/update until unless total sum of key columns not more than 900 bytes. In above insert query cut down long string into small and you'll be able to insert the record without any error.
- You can add big columns in index using INCLUDE clause [except text, ntext, and image datatype columns], SQL Server will not consider included columns for 900 bytes.

Tags: Database Index , MSSQL