title: SQL Server: creating a primary key with SORT_IN_TEMPDB
body: <p>I am working on a project where I need to create dozens of nonclustered indexes on various tables that already exist, and I am using this "WITH" clause for my create index statements:</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX IX_blah_blah_blah 
  ON prt.BlahBlah ([blahID])
  WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON,
        IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = ON, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90);
  </code></pre>
  
  <p>These work fine.</p>
  
  <p>However, in one case, I have a table with 3M records, but no indexes and no primary key at all.  I need to create a primary key, so I want to run something like this:</p>
  
  <pre><code>ALTER TABLE prt.AuditLog 
  ADD CONSTRAINT PK_auditlog PRIMARY KEY NONCLUSTERED ([RecordID])
  WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF,
        SORT_IN_TEMPDB = ON,      -- option NOT SUPPORTED for alter table
        IGNORE_DUP_KEY = OFF,
        DROP_EXISTING = ON,       -- option NOT SUPPORTED for alter table
        ONLINE = ON,              -- option NOT SUPPORTED for alter table
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90);
  </code></pre>
  
  <p>But three of the options of the "WITH" clause are not supported for <code>ALTER TABLE</code>.  </p>
  
  <p>I can live without the <code>DROP_EXISTING</code>, but I really need the <code>SORT_IN_TEMPDB</code> and <code>ONLINE</code> options.  </p>
  
  <p>How do I get this done?</p>
  
  <p>I'm using SQL Server 2014.</p>