I recently used SQL Data Sync to sync a Windows Azure SQL Database with an on-premises database instance hosted in SQL Server 2008 R2. When SQL Data Sync tried to create the schema it threw the following exception.
Database provisioning failed with the exception "SqlException Error Code: -2146232060 – SqlError Number:1904, Message: The index ‘XI_IndexName’ on table ‘dbo.TableName’ has 21 column names in index key list. The maximum limit for index or statistics key column list is 16. " For more information, provide tracing ID ‘802aeb36-fa15-****-****-b8acc99125d3’ to customer support.
Confused as to where this exception came from, looked around and found a few posts (listed below) explaining restrictions for creating Indexes.
Then I realized that my schema has already been created on Windows Azure SQL Database, so I tried publishing the schema from my Database Project to my on-premises database instance and for some reason, the error was not raised.
Then I reinitiated the data sync process and was able to complete the initial synchronization.
My guess is that by publishing the schema myself, I was able to relieve SQL Data Sync from trying to create the schema and allowed it to directly start synchronizing the data.
More Posts About This Limitation
- Creating Indexes (Database Engine)
- SQL Azure Data Sync error – maximum limit for index key column list is 16?
- Any way around unique index 16 column max
- What are the maximum number of Columns per index key,foreign key,primary key in SQL Server
- SQL SERVER – Maximum Columns per Primary Key – Fix : Error : Msg 1904, Level 16, The index on table has column names in index key list. The maximum limit for index or statistics key column list is 16
Alexandre Brisebois ☁ 