Index Creation In SQL Azure

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.

 

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