Ygthkb

Case protection with emphasis in biblatex

If I tried and failed to start my own business, how do I apply for a job without job experience?

What does からか mean?

Why didn't Tom Riddle take the presence of Fawkes and the Sorting Hat as more of a threat?

How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?

For a familiar, do the skill ranks you get from a headband of vast intellect count as the master's skill ranks?

Caron Accent v{a} doesn't render without usepackage{xeCJK}

How to completely remove a package in Ubuntu (like it never existed)

Minimum Viable Product for RTS game?

Prevent Nautilus / Nemo from creating .Trash-1000 folder in mounted devices

Word for something that's always reliable, but never the best?

Taking an academic pseudonym?

If angels and devils are the same species, why would their mortal offspring appear physically different?

How can I handle players killing my NPC outside of combat?

Democratic Socialism vs Social Democracy

why typing a variable (or expression) prints the value to stdout?

Why did Luke use his left hand to shoot?

Are all power cords made equal?

What is the draw frequency for 3 consecutive games (same players; amateur level)?

Writing dialogues for characters whose first language is not English

Is the percentage symbol a constant?

To be or not to be - Optional arguments inside definition of macro

What are some ways of extending a description of a scenery?

Plausible reason for gold-digging ant

Table partition existing table where partiotionkey is not part of the primary key

Large Fact table and partitioning key dilemmaHow to create a partitioned table based on date?Switching a partition on a table with one to many relationCan I Implement a Sliding Window for a Table Spread Across Two Partition Schemes on the Same Partition Function?How to drop partitioned index in SQL Server 2005Partitioning a large table has not improved performance, why?Large table partitionhow to align data with an specific partition? plus 2 other questions related to the same table partitionHow to add more partitions to Existing partitioned TableQuestions on database table and index partitioning in SQL Server

0

Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning)

I have an existing table Order, like this:

CREATE TABLE Order (

    OrderId INT,

    OrderDate Datetime,

    Quantity INT,

    CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED 

(

    [OrderId] ASC

) 

ON [PRIMARY];

This table contains 50 million rows from the last 10 years.
I only need the last 5 years data.

I have a partition function like this:

CREATE PARTITION FUNCTION OrderPF (datetime)

AS RANGE RIGHT FOR VALUES ('2014-01-01')

I have a partition scheme like this:

CREATE PARTITION SCHEME OrderPS 

AS PARTITION OrderPF ALL TO ([PRIMARY]) 

My question is how to proceed?
I still want a primary key on the table.

Does the [OrderDate] column have to be a part of the clustered index? (Main question)

CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order(OrderDate,OrderId) ON OrderPS(OrderDate) ;

If so, do I then have to create an extra non-clustered Primary Key purely on [OrderId]?

ALTER TABLE Order ADD CONSTRAINT PK_OrderId PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

Is this the correct approach?

partitioning sql-server-2017

share

asked 2 mins ago

DagDag

1

New contributor

Dag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning)

I have an existing table Order, like this:

CREATE TABLE Order (

    OrderId INT,

    OrderDate Datetime,

    Quantity INT,

    CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED 

(

    [OrderId] ASC

) 

ON [PRIMARY];

This table contains 50 million rows from the last 10 years.
I only need the last 5 years data.

I have a partition function like this:

CREATE PARTITION FUNCTION OrderPF (datetime)

AS RANGE RIGHT FOR VALUES ('2014-01-01')

I have a partition scheme like this:

CREATE PARTITION SCHEME OrderPS 

AS PARTITION OrderPF ALL TO ([PRIMARY]) 

My question is how to proceed?
I still want a primary key on the table.

Does the [OrderDate] column have to be a part of the clustered index? (Main question)

CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order(OrderDate,OrderId) ON OrderPS(OrderDate) ;

If so, do I then have to create an extra non-clustered Primary Key purely on [OrderId]?

ALTER TABLE Order ADD CONSTRAINT PK_OrderId PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

Is this the correct approach?

partitioning sql-server-2017

share

asked 2 mins ago

DagDag

1

New contributor

Dag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning)

I have an existing table Order, like this:

CREATE TABLE Order (

    OrderId INT,

    OrderDate Datetime,

    Quantity INT,

    CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED 

(

    [OrderId] ASC

) 

ON [PRIMARY];

This table contains 50 million rows from the last 10 years.
I only need the last 5 years data.

I have a partition function like this:

CREATE PARTITION FUNCTION OrderPF (datetime)

AS RANGE RIGHT FOR VALUES ('2014-01-01')

I have a partition scheme like this:

CREATE PARTITION SCHEME OrderPS 

AS PARTITION OrderPF ALL TO ([PRIMARY]) 

My question is how to proceed?
I still want a primary key on the table.

Does the [OrderDate] column have to be a part of the clustered index? (Main question)

CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order(OrderDate,OrderId) ON OrderPS(OrderDate) ;

If so, do I then have to create an extra non-clustered Primary Key purely on [OrderId]?

ALTER TABLE Order ADD CONSTRAINT PK_OrderId PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

Is this the correct approach?

partitioning sql-server-2017

share

asked 2 mins ago

DagDag

1

New contributor

Dag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

CREATE TABLE Order (

    OrderId INT,

    OrderDate Datetime,

    Quantity INT,

    CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED 

(

    [OrderId] ASC

) 

ON [PRIMARY];

CREATE PARTITION FUNCTION OrderPF (datetime)

AS RANGE RIGHT FOR VALUES ('2014-01-01')

CREATE PARTITION SCHEME OrderPS 

AS PARTITION OrderPF ALL TO ([PRIMARY]) 

CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order(OrderDate,OrderId) ON OrderPS(OrderDate) ;

ALTER TABLE Order ADD CONSTRAINT PK_OrderId PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

share

asked 2 mins ago

DagDag

1

New contributor

Dag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 2 mins ago

DagDag

1

New contributor

Dag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 mins ago

DagDag

1

New contributor

Dag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 mins ago

DagDag

1