RSS Feeds

Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

SQL Azure

Developer

General DBA

ASP.NET / ADO.NET

SQL Azure

Training Videos

Check out our new SQL Server Training Videos section More...

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

Data Comparison with Data Dude

By : Dinesh Priyankara
Mar 05, 2008

Page 2 / 3

The result of the comparison looks like below;

The result window of the comparison contains three sections but by default, you will see only two sections. The first section shows each object selected for the comparison and the number of records identified under specific category. For example, it says that there are 451 identical records in both databases for the table of "dbo.DatabaseLog". Since there is no difference between the two selected databases, you will not see any tables that have values for columns except "Identical Records".

The second section shows the records related to the selected row(object). It is a tab-sheet that has four tabs, each representing columns in above section. The tab, "Different Records" shows two columns for one data column (field) representing both source and target whereas other tabs use single column to a data column. If you click on "Identical Records" tab sheet, you will see records for the selected object.

The last section of the result is, by default not shown, the script for updating the target. It can be opened by clicking the icon "Show Data Update Script" (or Data -> Data Compare -> Show Update Script menu item). The default setting instructs to drop foreign keys before updating data and recreate them. We will see other setting later in this article. Examine the code and see.

As no data to be updated in the target, the buttons "Write updates" that allows to update the target database directly and "Export to Editor" that allows to load the update script onto separate editor for modification and execution, are disabled. These two buttons are mainly used for updating the target.

Now you have seen the way of doing the data comparison. Let's discuss few more things and then see how changes are reflected through the comparison.

Comparison Key
Data Dude requires a column that has unique values for each record in the table for data comparison. If the table does not contains a unique key, it will be not be listed in the second screen of "New Data Comparison" and it will not be taken into comparison. So, remember a unique key is a must for data comparison.

If the table contains more than one unique key, both will be listed in the drop-down under the "Comparison Key" column. Data Dude will select the first one for doing the comparison, but you can change it as you want. The scrip below creates a table in both databases and shows how it is displayed in the second screen.

use AdventureWorks
go
create table TestTable (Column1 int primary key not null, Column2 varchar(100))
go
create unique index IX_TestTable on TestTable (Column2)
go
insert into TestTable values (1, 'Value1')
insert into TestTable values (2, 'Value2')
go
use AdventureWorksTemp
go
create table TestTable (Column1 int primary key not null, Column2 varchar(100))
go
create unique index IX_TestTable on TestTable (Column2)
go

Once executed, start a new data comparison and note the second screen;

It shows both the primary key and the unique key. Anyway I am not too sure whether the operation or performance will be affected by changing the comparison key but selecting the clustered index (PK) may improve the performance of data comparison.

Reflection of data changes
Now let's change some data in both database and see how data changes are shown and scripted by Data Dude by following the steps below.

Open Management Studio and open a new query window.
Run the below script;

USE AdventureWorks
GO
UPDATE Sales.SalesOrderDetail
SET OrderQty = 3
WHERE SalesOrderDetailID = 27070
GO

USE AdventureWorksTemp
GO
INSERT Person.ContactType
(Name) VALUES ('Assistant Account Manager')

Open the project we created and start a new Data Compare.

Select "AdventureWorks" as the source and "AdventureWorksTemp" as the target. Click on "Finish" to start the process.
Analyze the result of the comparison. You will see the first difference for the "Person.ContactType" table. Note the value "1" for "Only in Target" column and it is supposed to be deleted from the target.

<< Prev Page Next Page>>