Doing some testing in SQL Azure, I found that table creation is a slightly different beast. Simply using a SQL table creation script generated by the SQL Server Management Studio does not translate into the Azure environment. I needed to make some modifications first to get the code working correctly, specifically how Indexes are created.
Below is a sample index generated by SQL Server Management Studio:
CREATE UNIQUE NONCLUSTERED INDEX [IX_TestTable] ON [dbo].[TestTable] ( [StringField] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
To get this index code to successfully work within Azure, I had to make the code look like the following:
CREATE UNIQUE NONCLUSTERED INDEX [IX_TestTable] ON [dbo].[TestTable] ( [StringField] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
I had to remove “ON [PRIMARY” from the end of the code, as well as removing the following commands: PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, ALLOW_ROW_LOCKS = ON, AND ALLOW_PAGE_LOCKS = ON. After that, the index creation code worked successfully in this new SQL environment.