SQL Server 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. Today in this article I'm going to explain yet another new tool in Visual Studio 2010 which is nothing but the DATA COMPARISION 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 version.
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 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 as below



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 FINISH button.



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 below image.



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.



Conclusion:

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.