0

Here orders table was partitioned but at the order table property, it was not partitioned. What is the reason for that? Because when I am trying to switch partition 2 from the old table to another table it shows orders table is not partitioned.

OrdersTable

CREATE TABLE [dbo].[orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderDate] [date] NULL,
    [ProductID] [int] NULL,
    [CustomerName] [nvarchar](100) NULL,
    [TotalAmount] [decimal](10, 2) NULL,
PRIMARY KEY CLUSTERED 
([OrderID] ASC)) ON [PRIMARY]

Switch Partition

ALTER TABLE [dbo].[Orders] SWITCH PARTITION 2 TO [dbo].[OrdersTest];

enter image description here

enter image description here

enter image description here

0

2 Answers 2

2

PRIMARY KEY CLUSTERED ([OrderID] ASC)) ON [PRIMARY]

ON [PRIMARY] means that the clustered index is not partitioned. To partition it place it on a partition scheme rather than a filegroup.

To enable partition switching all the indexes must be on compatible partition schemes.

2
  • Here I did partition using NONCLUSTERED INDEX because of that it said Order table not partitioned. Commented Aug 14, 2023 at 17:10
  • Right. You partitioned one index, but left the table itself non-partitioned. Commented Aug 14, 2023 at 18:45
0

It was because of the NONCLUSTERED index. Initially, I created a partition key with a non-clustered index. Because of that status has not changed at properties.

As per the investigation, I found partition key must be created with CLUSTERED index to avoid this type of issue. But as per the document clustered index should be one within a single table because of that we need to make sure that no other clustered index on that table.

If already has a clustered index on another column, 1st need to remove it from that column's clustered index and create clustered index on desired partition column and make the previous clustered index column into a non-clustered index.

  • 1st create Filegroup
  • 2nd create File
  • 3rd create a partition function
  • 4th create a partition schema

1-4 steps as usual. Next, I just did the below steps to solve that error.

ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [PK__OrdersNe__C3905BAFA58DE79A];
CREATE CLUSTERED INDEX ix_order_date_db ON [dbo].Orders(OrderDate) ON order_by_year_schema(OrderDate);
CREATE UNIQUE NONCLUSTERED INDEX ix_order_id ON Orders(OrderID);

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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