Windows Azure SQL Data Sync – The Maximum Limit For Index or Statistics Key Column List is 16

May 6, 2013 — 2 Comments

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-face1

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

Trackbacks and Pingbacks:

  1. Dew Drop – May 7, 2013 (#1,542) | Alvin Ashcraft's Morning Dew - May 7, 2013

    […] Windows Azure SQL Data Sync – The Maximum Limit For Index or Statistics Key Column List is 16 and Windows Azure SQL Data Sync – Optimizing The Initial Sync Process For Large Data Sets (Alexandre Brisebois) […]

    Like

  2. Reading Notes 2013-05-13 | Matricis - May 13, 2013

    […] Windows Azure SQL Data Sync – The Maximum Limit For Index or Statistics Key Column List is 16 – Nice workaround of an Azure Data Sync limitation. […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s