topic SQL Server and index key size limit of 900 bytes in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/sql-server-and-index-key-size-limit-of-900-bytes/m-p/206166#M159296 <HTML><HEAD></HEAD><BODY><SPAN>I just ran a fresh install of a process engine database of version 5.16.1 to a MS Sql Server (2012).&nbsp; The create-engine script ran with the following warning…</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR />(1 row(s) affected)<BR /><BR />(1 row(s) affected)<BR /><BR />(1 row(s) affected)<BR />Warning! The maximum key length is 900 bytes. The index 'ACT_UNIQ_PROCDEF' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><SPAN>It appears the index in question is from this uniqueness constraint:</SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />alter table ACT_RE_PROCDEF<BR />&nbsp;&nbsp;&nbsp; add constraint ACT_UNIQ_PROCDEF<BR />&nbsp;&nbsp;&nbsp; unique (KEY_,VERSION_, TENANT_ID_);<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><SPAN>The data types on the constraint are nvarchar(255), int, and nvarchar(255), for KEY_, VERSION_, and TENANT_ID_, respectively.&nbsp; Their data storage adds up as follows:&nbsp; 2*255 + 4 + 2*255 = 1024.&nbsp; Oops!&nbsp; So, it sounds like there will be trouble if a Tenant_ID more than 193 characters long is provided to a SQL Server database.</SPAN><BR /><BR /><SPAN>I am tempted to "fix" the script, but I have no idea what that might break.&nbsp; I don't plan on using the tenant_id, or at least, with values that long anyway.&nbsp; Something to consider if you see the same error.</SPAN><BR /></BODY></HTML> Thu, 04 Sep 2014 21:10:31 GMT gregdavisfromnj 2014-09-04T21:10:31Z SQL Server and index key size limit of 900 bytes https://connect.hyland.com/t5/alfresco-archive/sql-server-and-index-key-size-limit-of-900-bytes/m-p/206166#M159296 I just ran a fresh install of a process engine database of version 5.16.1 to a MS Sql Server (2012).&nbsp; The create-engine script ran with the following warning…(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)Warning! The maximum key length is 900 bytes. The index 'ACT_UNIQ_PROCDEF' has maximu Thu, 04 Sep 2014 21:10:31 GMT https://connect.hyland.com/t5/alfresco-archive/sql-server-and-index-key-size-limit-of-900-bytes/m-p/206166#M159296 gregdavisfromnj 2014-09-04T21:10:31Z Re: SQL Server and index key size limit of 900 bytes https://connect.hyland.com/t5/alfresco-archive/sql-server-and-index-key-size-limit-of-900-bytes/m-p/206167#M159297 <HTML><HEAD></HEAD><BODY><SPAN>Thanks for reporting this. However the chance that this becomes an issue is quite small, because tentant ids are often not that large.</SPAN><BR /><BR /><SPAN>Best regards,</SPAN></BODY></HTML> Mon, 08 Sep 2014 18:59:32 GMT https://connect.hyland.com/t5/alfresco-archive/sql-server-and-index-key-size-limit-of-900-bytes/m-p/206167#M159297 trademak 2014-09-08T18:59:32Z