Table Creation Error (DATA_COMPRESSION)

Feb 6, 2013 at 3:43 PM
Found a condition were an index on the local db had "DATA_COMPRESSION = ROW" set.

This feature isn't supported in SQL Azure and will need to be filtered out during the script creation process.

it currently errors out creating the table. I've been able to manually remove it from the script prior to executing on Azure.

I just want to add that this tool is great. Its saved me quite a bit of time and energy.

Thanks!
Coordinator
Feb 6, 2013 at 4:26 PM
Hi David,

Thanks for the info! Can you send me an example of the TSQL? I can modify the NotSupportedByAzureFile.Config to trap that and take it out.

Thanks,
George
Feb 6, 2013 at 6:04 PM
Edited Feb 6, 2013 at 6:13 PM
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MEMBERFINANCES]') AND name = N'IX_MEMBERFINANCES_CC')
CREATE NONCLUSTERED INDEX [IX_MEMBERFINANCES_CC] ON [dbo].[MEMBERFINANCES]
(
            [cardname] ASC,
            [cardnumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, DATA_COMPRESSION = ROW)
Coordinator
Feb 6, 2013 at 7:34 PM
Thanks. For now, if you want, edit NotSupportedByAzureFile.Config and go to line 29. You should see this:
        <NotSupported Text="([,\s]|\()+(ALLOW_(ROW|PAGE)_LOCKS|PAD_INDEX)\s*=\s*(ON|OFF)" ReplaceWith="" SeverityLevel="0" ReplaceString="true" DisplayWarning="false" DefaultMessage="true" WarningMessage="" />
Add this line after line 29:
        <NotSupported Text="([,\s]|\()+DATA_COMPRESSION\s*=\s*ROW" ReplaceWith="" SeverityLevel="0" ReplaceString="true" DisplayWarning="false" DefaultMessage="true" WarningMessage="" />
This will remove DATA_COMPRESSION = ROW from the create index statement. If you want, you can turn on DisplayWarning (true) and turn DefaultMessage off (false) and then type in your own WarningMessage. Anyway, it is just something fun to play with.

I will include this in my next release.

Thanks,
George