title: Sql Server Clustered Index "Partitioning". Primary Key issue
body: <p>I am trying to implement the "poor man's partitioning" strategy of having multiple tables with clustered indexes combined with a view. Here is an example of how I am trying to achieve this:</p>
  
  <pre><code>CREATE TABLE customer_data_Q1_2014
  (
      customer_index integer not null,
      customer_id int not null,
      customer_name varchar(128),
      transaction_date datetime2,
      CONSTRAINT PK_data_Q1_2014 PRIMARY KEY CLUSTERED (customer_index, customer_id)
  ) ON [FileGroup_1];
  
  
  CREATE TABLE customer_data_Q2_2014
  (
      -- same as above
  )
  
  CREATE TABLE customer_data_Q3_2014
  (
      --same as above
  )
  
  --and so on...
  
  ALTER TABLE DBO.customer_data_Q1_2014
  ADD CONSTRAINT CK_customer_data_Q1_2014 CHECK (transaction_date &gt;= '2014-01-01 00:00:00.000000' AND transaction_date &lt; '2014-04-01 00:00:00.000000');
  
  -- and so on...
  
  
  
  USE PROD_DB
  GO
  CREATE VIEW customer_data_view 
  WITH SCHEMABINDING
  AS
  
  SELECT customer_index, customer_id, customer_name, transaction_date 
  FROM DBO.customer_data_Q1_2014
  UNION ALL
  SELECT customer_index, customer_id, customer_name, transaction_date 
  FROM DBO.customer_data_Q1_2014
  UNION ALL
  -- AND SO ON...
  </code></pre>
  
  <p>Now this is all well and good until I try to insert anything into my view. The message that I get is "Partitioned View not updatable"</p>
  
  <p>As far as I can tell it has to do with not having the date as part of the primary key. The trouble with that is that, to me, it doesn't make sense and is in fact incorrect to have the date as part of the primary key. </p>
  
  <p>For example:</p>
  
  <p>Let's say that I am trying to insert a duplicate customer_index and customer_id</p>
  
  <pre><code>insert into customer_data (customer_index, customer_id, transaction_date) 
  values (1,2,'2018-01-01 00:00:00.000000')
  
  insert into customer_data (customer_index, customer_id, transaction_date) 
  values (1,2,'2017-05-12 00:00:00.000000')
  </code></pre>
  
  <p>If the date is part of my clustered primary key, this will be allowed and I will be left with a duplicate customer_id, customer_index combo.</p>
  
  <p>How can I implement my poor man's partitioning without having the date as part of my clustered primary key? </p>