title: Identifying similar records in SQL Server
body: <p>I am doing a cleanup of a customer table. The customer table receives data from 4 different sources and there are many duplicate records. I am using a combination of rules to identify similar customer records. The rule I have are:</p>
  <ol>
  <li>they have same firstname,lastname, email and mobile</li>
  <li>they have similar lastname, mobile and email</li>
  <li>they have similar firstname, lastname and email.</li>
  </ol>
  <p>I want to assign them a new unique_id like the output below:</p>
  <p><img src="https://i.stack.imgur.com/mRSSf.png" alt="expected output" /></p>
  <p>Here is the code for the table and data:</p>
  <pre><code>CREATE TABLE dbo.customer(
  customer_id int,
  first_name varchar(100),
  last_name varchar(100),
  email varchar(100),
  mobile varchar(20),
  unique_id int)
  GO
  
  INSERT INTO dbo.customer(customer_id, first_name, last_name, email, mobile)
  values (5475872,'','SMITH','john.smith@fakeemail.com','123456'),
  (623073,'JOHN','SMITH','john.smith@fakeemail.com','123456'),
  (1286681,'JOHN','SMITH','john.smith@fakeemail.com','123456'),
  (1303103,'JOHN','SMITH','john.smith@fakeemail.com','123456'),
  (7063902,'JOHN','SMITH','john.smith@fakeemail.com','987654'),
  (1808155,'JOHN','SMITH','john.smith@fakeemail.com','123456'),
  (2151985,'','SMITH','john.smith@fakeemail.com','123456'),
  (7221764,'JOHN','SMITH','john.smith@fakeemail.com','987654'),
  (1870704,'JOHN','','john.smith@fakeemail.com','123456'),
  (887842,'BILL','GATES','bill@microsoft.com','555555'),
  (736363,'','GATES','bill@microsoft.com','555555')
  GO
  </code></pre>
  <p>Can anyone help me on how I can generate that unique_id column based on different combinations?</p>
  <p>Thanks</p>