title: How to re-index an AuditLog table? non clustered primary key, clustered covering index, guid
body: <p>Using SQL Server 2016 Standard. I have an existing <code>AuditLog</code> table, with a PK on a <code>bigint</code> column (generated C# side) and an additional index.</p>
  
  <pre><code>CREATE TABLE [dbo].[AuditLog]
  (
      [Id] [bigint] NOT NULL,
      [ChangeTime] [datetime] NOT NULL,
      [User] [varchar](100) NOT NULL,
      [RootId] [bigint] NOT NULL,
      [EntityId] [bigint] NOT NULL,
      [EntityName] [varchar](100) NOT NULL,
      [Operation] [varchar](100) NOT NULL,
      [OldValue] [varchar](max) NULL,
      [NewValue] [varchar](max) NULL
  )
  
  ALTER TABLE [dbo].[AuditLog] 
      ADD CONSTRAINT [PK_AuditLog] 
          PRIMARY KEY CLUSTERED ([Id] ASC)
  
  CREATE NONCLUSTERED INDEX [IX_AuditLog_RootId] 
      ON [dbo].[AuditLog] ([RootId] ASC)
  </code></pre>
  
  <p>With the current 105,000,000 rows, the sizes are (using used_page_count * 8K per page):</p>
  
  <ul>
  <li>PK_AuditLog: 11,535,112 KB</li>
  <li>IX_AuditLog_RootId: 2,370,480 KB</li>
  </ul>
  
  <p>I now have to create rows in this table from a stored procedure in SQL, not in c# only anymore, so I need a primary key that can be generated SQL side (and C# still). I think my choices are <code>int identity</code> and <code>guid</code> (with a <code>NEWSEQUENTIALID</code> default).</p>
  
  <p>Since most of my usages include the date and ordering by date, I'm thinking of clustering with that. Sounds right?</p>
  
  <p>And since I almost always filter by <code>RootId</code> and <code>User</code>, I'm thinking of including them in my index. Is it a good idea to include the other columns in the clustered index? or should they be in a separate covering index?</p>
  
  <p>Every index needs to identify rows uniquely, so my clustered index will include the primary key even if I don't specify it. So using a <code>Guid</code> as the PK seems a bad idea for storage, particularly with 100million rows. So I'm using a <code>bigint</code>.</p>
  
  <p>Since my PK is notclustered (therefore not stored physically in that order), how does SQL Server work out the next identity? I doubt it sorts the PK to find the max value. Is using identity on a nonclustered column a bad idea?</p>
  
  <p>Also, I guess I could use <code>datetime2</code> with precision 3 (storage 7 bytes) instead of <code>datetime</code> (8 bytes) to keep same precision but save a bit of space (or even precision 4 to increase precision for same storage anyway)?</p>
  
  <p>So I'm thinking of doing:</p>
  
  <pre><code>CREATE TABLE dbo.AuditLog
  (
      Id bigint NOT NULL IDENTITY (1, 1),
      ChangeTime datetime2(4) NOT NULL...
  
  
  ALTER TABLE AuditLog   
      ADD CONSTRAINT [PK_AuditLog] 
          PRIMARY KEY NONCLUSTERED (Id)
  
  CREATE CLUSTERED INDEX CIX_AuditLog_ChangetimeRootUser 
      ON AuditLog(Changetime, RootId, [User])
  </code></pre>
  
  <p><strong>Footnote</strong></p>
  
  <p>This is how the table is used:</p>
  
  <ul>
  <li><p>No foreign keys to or from this table.</p></li>
  <li><p>insert heavy (any add/edit/delete of user entity fields inserts a new AuditLog row, constantly during business hours, must be fast)</p></li>
  <li><p>occasional reads (users check what or who changed something, ie, read the AuditLog, a few times a day, would be nice to not wait ages for a query to return)</p></li>
  <li><p>AuditLog rows are never updated nor deleted once inserted.</p></li>
  </ul>
  
  <p>Typical filters and order:</p>
  
  <ul>
  <li>filter by date only</li>
  <li>filter by date and user</li>
  <li>filter by date and objectId</li>
  <li>filter by date and user and objectId</li>
  <li>filter by objectId only </li>
  <li>almost always sorted by reverse date, to show most recent changes first.</li>
  <li>often used with paging, using "offset x rows" and "fetch next x rows only"</li>
  <li>and a specific use case, which amounts to selecting a subset of PK using a where clause, and then self join on the main table using the PK to retrieve column values</li>
  </ul>
  
  <p>PS: I'm clear on the process and the time it will take, create temporary new table, copy data in chunks, create indexes, etc...</p>