I have non-partitioned production table with 100 million records for last 2 years. The table has the clustered and non clustered indexes.
I want to insert daily about 150K records from external server. The load will happen early in the morning when there's no user activity. We decided to reload entire table once a week on Sunday due to the business rules. I would like to partition the data by 3 months and I don't need to archive the deleted data.
I need to rebuild entire table with 2 years of data on Sunday, delete records from last week Sunday and reload them daily for prior week plus this week to date(I.e. load 8 days on Monday, 9 - on Tuesday, 10 - on Wednesday etc until full rebuild on Sunday).
What strategy could you recommend? Do I need to use the staging table?
Here is my table structure:
CREATE TABLE [dbo].[SALES_PROD] ( [PROD_DIM_ID] [decimal](18, 0) NOT NULL, [UPC] [decimal](18, 0) NULL, [GREG_DATE] [date] NOT NULL, [LOC_DIM_ID] [int] NOT NULL, [STORE_NBR] [int] NULL, [FIL_LOC_DIM_ID] [int] NOT NULL, [FIL_STORE_NBR] [int] NULL, [GROSS_SALES_UNITS] [decimal](15, 0) NULL, [GROSS_SALES_AMT] [decimal](15, 2) NULL, [GROSS_SALES_POS_AMT] [decimal](15, 2) NULL, [OCD_CD] [smallint] NOT NULL, CONSTRAINT [PK_OC_SALES_PROD_LOC_DAY] PRIMARY KEY NONCLUSTERED ( [PROD_DIM_ID] ASC, [GREG_DATE] ASC, [LOC_DIM_ID] ASC, [FIL_LOC_DIM_ID] ASC, [OCD_CD] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] --CLUSTERED Index CREATE CLUSTERED INDEX [IX_GREG_DATE] ON [dbo].[SALES_PROD] ( [GREG_DATE] 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, FILLFACTOR = 90) ON [PRIMARY]