Running SQL Azure Migration Wizard version 3.8.9.0 to migrate a database from SQL 2008 R2 (10.50.2500) - I started with a completely empty target Azure database, so both the schema script was run and
Source table has an index (unique, non-clustered) that's filtered - the where clause has it only apply the index to the rows where the target column is not null:
CREATE UNIQUE NONCLUSTERED INDEX [IX_Media_unique_cmeid] ON [dbo].[Media]
(
[CmeId] ASC
)
WHERE ([CMeID] IS NOT NULL)
When the index was migrated over, that 'where' clause was not included, so the inserts of the data failed since there are multiple rows with null in that column:
6/19/2012 4:35:20 PM--> Error
Starting copy...
SQLState = 23000, NativeError = 2627
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Violation of UNIQUE KEY constraint 'IX_Media_unique_cmeid'. Cannot insert duplicate key in object 'dbo.Media'. The duplicate key value is (<NULL>).
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated.
BCP copy in failed
It doesn't appear to be a limitation of SQL Azure, since I dropped the index, copy-pasted over the same 'CREATE INDEX' from above, and it worked fine - it showed up in SSMS as 'Filtered' as well, so it appears that it really worked (and didn't just ignore the 'where' clause).
Then I went to the tab for that table (where the above failure had happened) and hit 'Retry', it ran BCP again, and this time it worked fine.
So, at least AFAICT, whatever is generating the script for that index just needs to support the 'where' clause capability and everything else will work as-is.
Thanks!