You can filter the rows that will be compared by applying a WHERE clause to the comparison. This is useful, for example, if you want to exclude a particular set of test data, or to speed up the comparison.

Note that:

• You can only filter rows if the data source is a database. WHERE clauses are not available for backups and scripts folders.
• The WHERE Clause Editor does not validate the WHERE clauses. The clauses are validated when the databases are compared.

To specify a WHERE clause for a table, on the Tables & Views tab, double click the table, or select it and click WHERE Clause

The WHERE Clause Editor is displayed:

To apply the same WHERE clause to multiple tables, use SHIFT+Click or CTRL+Click. Alternatively, you can right-click the tables or views and click Open WHERE clause editor.

Type a valid Transact-SQL WHERE clause. For example, if a table has columns ID, FirstName, and LastName, you may want to compare only rows where LastName is Smith. To do this, type the following in the box:

LastName='Smith'

Click OK.

If you want to apply a different WHERE clause for each data source, clear the Use the same WHERE clause for both data sources check box and type the WHERE clause in the box for the database on the right.

For example, if you have two databases for two different time zones that are 5 hours apart and you want to repair damaged data in one of the tables, you can specify a different WHERE clause in each database for that table.

Since you know approximately when the problem occurred, you can limit the number of rows that are compared. Clear the Use the same WHERE clause for both data sources check box, and specify WHERE clauses for each data source.

For more powerful filtering of rows, the WHERE clause can call functions in your database, which may be useful if you would like a very long WHERE clause or one that uses local variables.

Alternatively, you can construct a simple view on the table in both databases. You can then use SQL Data Compare to compare the views.