I need to apply Sliding Window Partitioning on a table that contains 1B of records.
CREATE TABLE table_1( [Id] [bigint] IDENTITY(1,1) NOT NULL, [log_date] datetime NULL, [field1] [bigint] NULL, [field2] [int] NULL, [field3] varchar(20) NULL, CONSTRAINT [PK_table_1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
I want to do the partitioning on log_date field (1 partition per day) , knowing that my queries will not use log_date field , and only Id will be used.
I'm confused here if I have to add log_date to my clustered index , or Id field alone will be alone. Also if I added log_date to the clustered index , can I still query on the table while filtering only on the Id ?
sample of query I'm using :
declare @i int = 1000000 select * from table_1 where id>=@i and id<@i + 200000 order by id