title: How do I compare whether same/intersection data is there in one and another row in same table when I have huge data in the table in sql server?
body: <p>I have a table with the sample data below. Now, I just want to compare one record with all other records in the same table and we have to give ID if that record colloids with any other records in the remaining records. And column is with comma separated data, So if we have 'A,C' as Name in one record and 'A' in another record(Check the input from text) then it colloid each other because 'A' is common in both.
  In the same way one of the record is not having anything in the Name it is NULL. When it is Null it should colloid with remaining other records. Like this Name column I have around 10 columns to verify data.</p>
  <p><strong>Input</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">ID</th>
  <th style="text-align: right;">Name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: right;">A,C</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: right;">B</td>
  </tr>
  <tr>
  <td style="text-align: left;">3</td>
  <td style="text-align: right;">A</td>
  </tr>
  <tr>
  <td style="text-align: left;">4</td>
  <td style="text-align: right;">NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>OUTPUT</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">ID</th>
  <th style="text-align: center;">ColloidID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">3</td>
  </tr>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">4</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">4</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">4</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Problem : I have implemented solution like below, and it working fine as expected. But the thing here is it is fine when less data in the table(&lt;100k) but it's taking more time and space when dealing with millions of data(Ex : &gt;20M Data)</p>
  <pre><code> SELECT DISTINCT A.ID,B.ID AS ColloidID
   FROM #Temp1 A
   CROSS APPLY #Temp1 B
   WHERE A.ID&lt;&gt;B.ID
   AND master.dbo.fIntersection(COALESCE(A.Name,B.Name,''),COALESCE(B.Name,A.Name,'')) = 1
  </code></pre>