The IDENTITY issue on SQL Mobile is solved!

SQL Mobile refuses to accept values on IDENTITY columns, contrary to SQL Server and Access. The only way to force an IDENTITY value is by changing its seed. In my case, I’m using OLE DB so the IAlterTable::AlterColumn method is used. This has been an issue for me because it would seem to prevent database copying between the desktop and the device. After a bit of thought I realized that I did not need to call the AlterColumn method for every single insert – I could use the auto numbering to generate IDENTITY values for me.

Most databases that use IDENTITY columns will do so for primary key or index support, meaning that these values are generally laid out in row creation order. For a table with few deletions, it is very likely that you can find large continuous ranges for which you can let the IDENTITY column do its job - generate the next value.

I write this after finishing my first tests on Data Port Wizard and a new version of the device component. For "well behaved" databases such as the Northwind sample, the code only needs to change the IDENTITY seed twice per table and performance does not seem to be an issue. On databases with sparse tables (where the number of gaps in IDENTITY values is large) performance may be a concern because the base table cursor must be closed before the seed is changed, and reopen again to continue data insertion.