I've come across an issue where data compare isn't picking up data changes when comparing two source control revisions where there have been structural changes involved.
I have a table with static data linked in to source control, lets call this revision 1.
I add some columns to a table and fill in the data in those columns, when checking in the changes using SQL source control I commit the columns and the data changes as well at the same time (forced by the GUI), which gives me revision 2.
Now, when I use SQL data compare and tell it to give me scripts which change revision 1 to revision 2 it tells me there have been no data changes. This seems to be because as revision 1 doesn't have the newly added columns they are being ignored in the comparison (as they were only added in revision 2).
What I think needs to happen in this case is for data compare to assume NULL's for the newly added columns in revision 1, it would have to be smart enough to realise they were added in revision 2 to do this.
My first 'workaround' thought was to check in all structure changes first seperately, then check in the data changes as a seperate checkin and then compare those two in Data Compare. SQL Source control forces you to check in both however.
My current approach is to apply the structure scripts to a copy of the database and then use data compare to compare just the tables I know have changed, this is a bit time consuming however and I'd prefer not to have to write this in to the deployment procedures moving forward.