Changes within function in a calculated column not detected
jgruber
Posts: 5
It seems like Redgate Compare 6.2.0.271 is unable to detect changes within functions in calculated columns (tested on SQL Server 2005).
Here's a test case:
1. On both servers execute
create table dbo.test
( id int identity(1,1) primary key
,moy int
,yr int
,mon as coalesce(yr,0) * 100 + moy
);
2. Synchronize the databases to get the same name for the primary key constraint.
3. On the source server execute
alter table tmp.test
drop column mon;
alter table tmp.test
add mon as coalesce(round(yr,2),0) * 100 + moy;
4. Sync: no change detected.
5. On the source server execute
alter table tmp.test
drop column mon
alter table tmp.test
add mon as coalesce(moy,0) * 100 + moy
6. Sync: no change detected.
Here's a test case:
1. On both servers execute
create table dbo.test
( id int identity(1,1) primary key
,moy int
,yr int
,mon as coalesce(yr,0) * 100 + moy
);
2. Synchronize the databases to get the same name for the primary key constraint.
3. On the source server execute
alter table tmp.test
drop column mon;
alter table tmp.test
add mon as coalesce(round(yr,2),0) * 100 + moy;
4. Sync: no change detected.
5. On the source server execute
alter table tmp.test
drop column mon
alter table tmp.test
add mon as coalesce(moy,0) * 100 + moy
6. Sync: no change detected.
Comments
Using the steps you outlined I was able to recreate the fault using SQL Compare 6.2. It seems that SQL Compare will notice the textual difference in the SQL, but will not mark it as a semantic difference.
However, I believe this has been identified and resolved by our development team, as the latest version SQL Compare 7.0 recognises the difference in the columns.
You will be able to fix this problem by downloading and installing SQL Compare 7.0. You can download the new version from our website, or by using the 'check for updates' function within the tool.
I hope this is helpful