CODEWITHSUNDEEP
sql-servert-sqldatabase-partitioning
Zapnologica
Zapnologica

Reputation: 22556

Does one need to create objects on top of a partition scheme?

I have been following some SQL Server table partitioning tutorials, but i can't seem to find a overview or explanation of how all the aspects fit together.

I have an existing table that I want to partition called 'Orders' for example.

So I go and create my partition function on Orders table.

I then create my partition scheme on top of the partition function.

Does that mean that if I insert into Table1 that it will now be partitioned automatically? Or which is the part I don't understand, in the tutorial there is the following:

--******************
--6. CREATE OBJECTS ON THE PARTITION SCHEME          
--******************    
--Create a partitioned heap... yep, you can do that!

 CREATE TABLE OrdersDaily (
    OrderDate DATETIME2(0) NOT NULL,
    OrderId int IDENTITY NOT NULL,
    OrderName nvarchar(256) NOT NULL
) on DailyPartitionSceheme(OrderDate)
GO

From what I understand about this, is they are essentially creating a duplicate table but putting it as an object on top of the partition scheme. Do I have to do this?

Upvotes: 0

Views: 72

Answers (1)

Jason
Jason

Reputation: 621

Partitioning an already existing non-partitioned table is tricky and not-necessarily recommended. I'm not sure how big your existing table is, or how many tables may already have foreign keys referencing it, etc., so YMMV. Two possible approaches come to mind:

I. Go old-school with a partitioned view instead.

OR

II. 1. Create a partition function that, initially, has only a single partition, e.g.:

CREATE PARTITION FUNCTION OrdersFunction (datetime2) AS RANGE LEFT FOR VALUES ();
  1. Create a partition scheme that, initially, maps to a single filegroup, e.g.:
CREATE PARTITION SCHEME DailyPartitionScheme AS PARTITION OrdersFunction TO ([PRIMARY]);
  1. Create a duplicate Orders table as you have already discerned, on the DailyPartitionScheme.
  2. Use SWITCH to migrate the contents of the non-partitioned Orders table to the partitioned OrdersDaily table, e.g.:
ALTER TABLE Orders SWITCH TO OrdersDaily PARTITION 1
  1. Finally, alter the data files, partition scheme, partition function (SPLIT RANGE) as needed to partition the data as desired across filegroups. Also, I'm assuming your filegroups, in turn, have data files that are distributed across different disks (or will be eventually) otherwise the value of partitioning in the first place is debatable.

Upvotes: 1

Related Questions