VERIFIED SOLUTION i

Impact of the maximum key length is 900 bytes warning when running upgrade script in EngageOne Server

Product Feature: Installation / Upgrade

Database: SQL2012 and SQL2014
 
When upgrading the EngageOne® Server database using the script 10-EngageOne-mssql-4.4.6_to_4.4.7.sql  the following warning is displayed: 

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

This warning would occur if an upgrade or a new install was performed using SQL2012 or SQL2014 as the database server type.  If SQL2016 is deployed, this warning will not occur. 

The warning says that in some situations, for combinations of large values the INSERT/UPDATE operation can fail. This statement is true, but is extremely unlikely to occur.

The index IDX_OCM_WK_DOM_OWN_STS_ID is created on table OCM_WORK_QUEUE with four columns:

DOMAIN_NAME (nvarchar (255))
OWNER_NAME (nvarchar (255))
STATUS (int) 
ID (int)

Without the index, it would be possible to insert up to 1028 bytes to those four columns (in one row). The presence of the index limits this to just 900 bytes, otherwise an error is thrown, but in reality, there will never be a situation where the limit of 900 bytes is exceeded.  For this to happen. It would require a very long user name and domain identifier. Theoretically, the user name can be as long as 255 characters but community identifiers are created automatically inside the system, and their length never exceeds 40 characters therefore if we try to insert the row with the longest values possible (40 characters for DOMAIN_NAME, 255 for OWNER_NAME), the command is executed successfully.
UPDATED:  July 20, 2018