Index Byte Limitation
The maximum length for an index key in SQL Server is 900 bytes. If an index exceeds the 900 byte limit, an exception is thrown. To prevent an exception from being thrown, ensure that the data values used by the index are less than 900 bytes.
Following are examples of exceptions thrown when the index size exceeds 900 bytes.
For example:
The insert statement is executed and the index size exceeds 900 bytes
wt.pom.DatastoreException: A SQL error has occurred for the statement
"INSERT INTO WTUser(classnameA2A2,updateCountA2,blob$entrySetadHocAcl,
disabled,
classnamekeydomainRef,idA3domainRef,entrySetadHocAcl,eventSet,inherited
Domain,name,repairNeeded,markForDeleteA2,updateStampA2,createStampA2,modify
StampA2,idA2A2) VALUES (’wt.org.WTUSER’,1,?,?,?,?,?,?,?,?,?,?,?,?,?)".
Database system message follows: Nested exception is: java.sql.SQLException:
[ptc][SQLServer JDBC Driver][SQLServer]Operation failed. The index entry of
length 2000 bytes for the index ’WTUser$COMPOSITE’ exceeds the maximum
length of 900 bytes.
For example:
When the update statement is executed and the index size exceeds 900 bytes
wt.pom.DatastoreException: A SQL error has occurred for the statement
"UPDATE WTUser SET blob$entrySetadHocAcl=?,disabled=?,classnamekey
domainRef=?,idA3domainRef=?,entrySetadHocAcl=?,eventSet=?,inherited
Domain=?,name=?,repairNeeded=?,markForDeleteA2=?,
updateStampA2=?,modifyStampA2=?,updateCountA2=updateCountA2+1 WHERE
((idA2A2 = ?) AND (updateCountA2 = ? ))" Database system message follows:
Nested Exception is: java.sql.SQLException: [ptc][SQLServer JDBC Driver]
[SQLServer]Operation failed. The index entry length of 2000 bytes for the
index ’WTUser$COMPOSITE’ exceeds the maximum length of 900 bytes.
Additionally, you may encounter warnings when executing the create_ddl_wt.bat file. These warnings can be ignored.
For example:
Running the create_ddl_wt.bat file
Warning: message=[ptc][SQLServer JDBC Driver][SQLServer]Warning! The
maximum key length is 900 bytes. The index 'WTUser$COMPOSITE' has
maximum length of 4000 bytes. For some combination of large values,
the insert/update operation will fail. command=CREATE INDEX WTUser$
COMPOSITE ON WTUser(name)