title: stored data sorting: nonclustered primary key overrides clustered index
body: <p>I need to create a table with a nonclustered primary key (to set foreign keys on other tables to it) and a clustered index to store the data in the intended order.</p>
  
  <p>However, the resulting stored data is sorted in the primary key's order as opposed to the index's.</p>
  
  <p>Is there a way to prevent this from occurring? Here is an example (SQL Server 14.0 RTM):</p>
  
  <pre><code>create table dbo.a (
       x nvarchar(50) not null
      ,y nvarchar(100) not null
      ,index ix_a clustered (y)
      ,constraint pk_a primary key nonclustered (x)
  )
  
  insert dbo.a
  values
       ('d','p')
      ,('c','q');
  
  select * from dbo.a
  </code></pre>
  
  <p>the result should be sorted with p first, then q. Howerver, q is in the first row and p is in the second row.</p>
  
  <p>In a similar case, this approach worked when the primary key was in 2 columns as opposed to only 1 column.</p>