title: Rails: How do I create a clustered index on a non-id column in rails?
body: <p>I want to store time series data in a database. The data will be organized as follows:</p>
  
  <ul>
  <li>A header table has information about a dataset (format, source, etc). Typically a dataset will have about 600 rows.</li>
  <li>A "Big Data" table has the actual rows of data. Each row will have a header_id, timestamp, and some data points.</li>
  </ul>
  
  <p>I'm trying to create a table with a clustered index on a data_header_id column instead of the id column. This is so that I can get all of the data points by doing a simple <code>SELECT * FROM big_datums WHERE data_header_id = 9001 ORDER BY timestamp ASC</code>. </p>
  
  <p>I have the following code, but get errors because of Rails defaults:</p>
  
  <pre><code>class CreateBigData &lt; ActiveRecord::Migration[5.2]
    def up
      create_table :headers do |t|
        t.string :data_format
        t.timestamps
      end
  
      create_table :big_datums do |t|
        t.references :data_header, null: false # This will need to be a CLUSTED index
        t.integer :timestamp
        t.integer :point1
        t.integer :point2
      end
  
      execute "CREATE CLUSTERED INDEX [data-header-index] ON [dbo].[big_datums] ( [data_header_id] ASC )
               WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
               ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"
      end
    end
  
    def down
      execute "DROP INDEX [data-header-index] ON [dbo].[big_datums] WITH ( ONLINE = OFF )"
      drop_table :big_datums
      drop_table :headers
    end
  end
  </code></pre>
  
  <p>I get this error:</p>
  
  <p>ActiveRecord::StatementInvalid: TinyTds::Error: Cannot create more than one clustered index on table 'dbo.big_datums'. Drop the existing clustered index 'PK__build_te__3213E83F6568EFB2' before creating another.</p>
  
  <p>This appears to be because rails already gives id a clustered PK.</p>
  
  <p>Questions:</p>
  
  <p>1) How do I create a table as described?</p>
  
  <p>2) Do I still need an 'id' column? I won't ever be querying by an ID column, but it might still be good to have a PK column to uniquely identify records -- especially since header_id + timestamp might not be a good clustered PK</p>