0

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
  • Could you post a sample of the query you are referring to? – George.Palacios Jan 24 at 9:50
  • @George.Palacios I edited the post to include the query – user123 Jan 24 at 9:56
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.