In my earlier article we have discussed about the SQL Server schema comparison tool in Visual Studio 2010 http://www.dotnetspark.com/kb/3658-sql-server-schema-comparison-tool-visual.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 Production).
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 maintenance etc.
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 Professional
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.
- You will see a dialog box which requests for the source and
destination db and the properties to connect to those db servers as
- 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.
- 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 FINISH button.
- 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
- 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 below image.
- 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
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