title: Primary key & clustered index
body: <p>Say I have<br>
  - Clients table, PK is ClientId<br>
  - Products table, PK is ProductId<br>
  I need to store, for a few Clients, <em>their</em> internal Product Reference, so I create a Client-Product table:</p>
  
  <pre><code>CREATE TABLE [dbo].[Product-Client](
      [IdProduct] [varchar](15) NOT NULL,
      [IdClient] [varchar](10) NOT NULL,
      [RefClient] [varchar](20) NOT NULL,       --client's internal product Id
   CONSTRAINT [aaaaaArticles-Clients_PK] PRIMARY KEY CLUSTERED   -- sure ???
  (
      [IdClient] ASC,
      [IdProduct] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>Of course I will also add 2 FOREIGN KEY constraints to make sure that the ProductId exists and the Client exists.<br>
  I want a unique index on ProductId + ClientId.<br>
  I want an unique index on ClientId + RefClient.<br>
  Knowing that these Client References are quite static, so they will be rarely updated, but often read, my questions are:  </p>
  
  <ol>
  <li>what is the ideal PK ?  </li>
  <li>which index should be the CLUSTERED one ?</li>
  </ol>
  
  <hr>
  
  <p>Edit<br>
  For question 1 there are 3 possible answers of course:<br>
  a) ClientId+ProductId  (a unique index will have to be created anyway)<br>
  b) ClientId+RefClient  (a unique index will have to be created anyway)<br>
  c) a surrogate key</p>