title: Optimize SQL Server 2008 query
body: <p>Update: edited the table alias. </p>
  
  <p>I am trying to find whether the below query can be rewritten to improve performance. We started noticing huge performance impact on the query recently. Person table has close to 10 million and Contact table has close to 17 million 
  records. </p>
  
  <pre><code>SELECT 
      ID, NAME, DEPARTMENT,
      CASE 
         WHEN PrimaryContact = 'MOBILE'
            THEN (SELECT TOP 1 MOBILE 
                  FROM CONTACT 
                  WHERE ContactType = 'MOBILE' AND CONTACT.PID = PERSON.PID)
            ELSE (SELECT TOP 1 HOME 
                  FROM CONTACT 
                  WHERE CONTACTTYPE = 'HOME' AND CONTACT.PID = PERSON.PID),
  FROM 
      PERSON
  WHERE 
      DEPARTMENT = @DEPARTMENT
  </code></pre>
  
  <p>Each person can have one/multiple Mobile/Home phone number in contact table. Based on primary contact type, it should fetch only one phone number based on the <code>PrimaryContact</code> type. </p>
  
  <p>On a side note, we are also planning to partition the <code>Person</code> table based on <code>Department</code>. </p>
  
  <p>Any suggestions to improve the overall performance will be much appreciated. </p>
  
  <p>Thanks</p>