Well, on the last day of TechEd I attended a presentation by Ginny Caughey on various data storage approach for mobile application. One of the comments she made was that "50% of the clients who implemented solutions using Sql Ce 2.0 experienced some kind of data corruption". Which definitely explains why inserting failed in my case (See my old blog "upgrading to mobile 2005"). Makes me feel lot better that the upgrade was towards a better solution.
The descendants sql mobile 2005/ sql everywhere has been written from scratch to optimize performance. It now supports ResultSet objects which dramatically improves performance for accessing and manipulating data. Check out post on SmartClientData for more information.