sliding window partitioning on date field

Question

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

Answer 1

Starting from the fact that I wouldn't partition on a column which is not used in a where clause, yes, your clustered index will have to change to include the log_date into its key columns, and yes, you can still query it filtering on ID only. I'd still suggest to include the partitioning column into your where clause to leverage partition elimination.

It's explained to much detail here: https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-2017