Microsoft Dynamics CRM 4.0 has built-in duplicate detection which is great, but in 3.0, which many businesses are still using, the only options for duplicate detection were some third-party applications or a a custom-developed one like the minimally functional example in the 3.0 SDK.

Because of the shortcomings of duplicate detection in version 3.0, a lot of these systems have ended up with some data quality issues. If you're running CRM 3.0 and you'd like a quick way to check your duplicates, here's a simple SQL query you can run to find them.

  1. Open SQL Server Management Studio and select your CRM database (it will be called something like 'YourCompanyName_MSCRM')
  2. Right-click the CRM database name and select 'New Query.'
  3. In the query window, type the following query:

      SELECT name, COUNT(name) AS NumOccurrences
      FROM FilteredAccount
      GROUP BY name
      HAVING (COUNT(name) > 1)
      ORDER BY NumOccurrences DESC
  4. Then click the 'Execute' button to run the query. You'll get back a list that looks like this:


    name NumOccurrences
    Bakersfield Furniture 3
    Dodge City Wholesale 2

If you are using an account number or other identifier that should be unique, you can replace 'name' in the query with the name of that field to be more precise.  Then you can go about locating the accounts in CRM and deciding what to do with them. Remember that CRM 3 and 4 have a "merge" function that lets you select two records at a time in a list view and merge them together by clicking the merge icon at the top of the list.