Azure SQL Database Migration – Remove Index Fillfactor

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:

echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307 

Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you have \0xDCB3 in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).

I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.

Update

to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:

insert into test(a) values  (N'a');
insert into test(a) values  (NCHAR(0xdc83));
insert into test(a) values  (N'b');
go

select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;

My suggestion would be:

(a) talk to Azure support. This is not how it should be working AFAIK.

(b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column:

SELECT name, etc.
 FROM sys.indexes AS i
 INNER JOIN sys.dm_db_index_physical_stats AS s
 ON ...
 WHERE ... criteria ...
 AND NOT EXISTS 
 (
   SELECT 1 FROM sys.index_columns AS ic
     INNER JOIN sys.columns AS c
     ON ic.object_id = c.object_id
     AND ic.column_id = c.column_id
     AND ic.key_ordinal = 1 -- only leading column
     AND c.system_type_id = 36 -- uniqueidentifer
     WHERE ic.index_id = i.index_id
 );

(c) before deciding to manually deal with these columns, wait until they actually cause you a performance problem that you can demonstrate and undeniably identify the fragmentation of this index as the problem.

(d) or, just use the inverse query in (b) to perform manual drop/create all the time instead of dealing with the problem over and over.