TransWikia.com

sliding window partitioning on date field

Database Administrators Asked on December 19, 2021

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

One Answer

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://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-2017

Answered by GMassDBA on December 19, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP