Should archive tables have their own surrogate identity id/key? For a customer sales table example:
- We have original table, and then an additional archive table with surrogate archive id.
- Rules for primary clustered indexes are:
- Ever Increasing
Reference: Effective Clustered Indexes (Red-Gate Hub)
At the last company I worked for, there was a thought, that if we accidentally missed a day or period of importing, e.g. imported data for May 3, accidentally skipped May 4 (issue with system), and imported May 5, that without the new archive id, we would have to reinsert data between the pages, causing fragmentation, and slower inserts.
With the archive id, we can just add on in an ever-increasing manner.
Just inquiring if adding archiveid column is a standard industry practice.
create table dbo.CustomerSalesId ( CustomerSalesId bigint primary key identity(1,1), CustomerId bigint not null, PurchaseDate datetime not null, Amount decimal (10,2) not null, ......... }
create table dbo.ArchiveCustomerSalesId ( ArchiveCustomerSalesId bigint primary key identity(1,1), CustomerSalesId bigint, CustomerId bigint not null, PurchaseDate datetime not null, Amount decimal (10,2) not null, ......... } create unique index ux_CustomerSalesId on ArchiveCustomerSalesId(CustomerSalesId)