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 ?
Edit:
sample of query I'm using :
declare @i int = 1000000
select * from table_1 where id>=@i and id<@i + 200000 order by id