This article explains the new Data Comparison tool in visual studio 2010.
In my earlier article we have discussed about the SQL Server
schema comparison tool in Visual Studio 2010 http://www.dotnetfunda.com/articles/article1121-sql-server-schema-comparison-tool-in-visual-studio-2010-.aspx.
Today in this article I’m going to explain yet another new tool in Visual Studio
2010 which is nothing but the DATA COMPARISON tool. This tool is of extreme
use for the developers to compare data in 2 tables in different environment (in
real time say a reference table data should be compared from Development to
Many third party software’s available in market which
servers the same purpose for comparing the data between the environments, but
with Visual Studio Microsoft has given this functionality inbuilt as a
comparison tool so that Visual Studio emerges as a bundled product which
servers most of the development tasks and day to day activities which the
developers and DBA’s required to do. Microsoft Database server version for
Database administrators and developers has provided with many features like
database development, version controlling, unit testing, code analysis, code
Microsoft has provided option in Visual Studio 2010 to
compare the data for SQL Server 2005 and 2008 versions, since SQL Server 2000
support is not provided by Microsoft this version is not included with VS 2010.
So both SQL Schema Comparison and Data Comparison are supported for SQL Server
2005 and 2008 versions. These tools are available for the licenses with Visual Studio
Premium and Visual Studio Ultimate versions and not available for the Visual Studio
Let’s jump into the demonstration on how to use
the Visual Studio 2010 Data Compare tool; I’m taking Sql server database data
comparison. Let’s see the step by step process on how to use this tool.
Step by Step Process
Step 1 – Open Visual Studio 2010 by moving to Start > Program > Microsoft Visual Studio 2010 > Microsoft Visual Studio 2010.
Step 2 – Once Visual Studio is opened, go to Data -->
Data Compare --> New Data Comparison…
Step 3 – You will see a dialog box which requests for
the source and destination db and the properties to connect to those db servers
Step 4 – Choose the source and the destination DB
servers which you want to compare with. If you can see the above image, there
are the options available for the comparison like Different Records, Only in
Source, Only in Target, Identical Records. Based on the needs these options can
be selected to make the comparison of the data. Once the options are selected
click on the FINISH button, it will open a new screen.
Step 5 – The new window will show the list of tables
and views in the Source/destination servers and the list of columns for each
table. You have given the option to select the columns which needs to be
compared. The main point to note down here is the tables to be compared needs
to have a Primary or Unique Key by which the tool will make the comparison. So Select the list of tables and the
constraints as per the requirement as shown in the below figure and click on
Step 6 – Once we click the FINISH button, the tool
will start doing the comparison based on the selection we made and gets the
result in a new windows as shown in the below image. The window has few
sections which gives the user to make analysis of the data which are compared.
The options which we gave at the step 3 are shown as output here. The window
will have the Count of data difference in both the source and the destination,
Count of data difference in source only, Count of data difference in
destination only and finally the identical records in both the servers.
Step 7 – In the same window we have some useful information, and
guess what are the information’s?? Yes the tool gives which are the difference
in the data stored as per the selection we made in the step 3 as shown in the
Step 8 – Finally in
the same window we have some more set of information’s like the tool itself
generates the insert and the update scripts to sync between the databases as
shown in the below image.
This tool is really going to be more productive for the day
to day activity of the developers and improves the productivity. Thanks to
Microsoft for incorporating these tools into Visual Studio 2010.