Partitioning Fact Table

Question

Afternoon (London GMT)
Just a quick question / advice

Been asked to partition a rather large table (fact) . I have done some reading on partitioning and understand how it works
but I'm just confused on how you would partition a table that already exists ??

Ive set up the function and schema but not i want to break this table up (weekly)

Do i have to do a massive insert into (could take forever) OR is there some Alter table sql that I'm missing

Cheers everyone

---

Akiraikonics

Answer 1

You basically have to create the partitioned tables, and then move the data from your main table to the new partition using insert into..select.
You also need to drop the index on your existing table and create a partitioned index.

---

Abdallah, PMP, MCTS

Answer 2

okay slightly annoying but if thats the way

once the insert is done is there a way of seeing how the partition have been broken up - because to me even tho the function specifies how to break the data
you still get one table

---

Akiraikonics

Answer 3

Hi Akiraikonics

You don't need to create a new table. You need to re-create the clustered index on your new partition scheme. This partitions the data.

CREATE INDEX ix_name
ON table(column)
WITH (DROP_EXISTING = ON)
ON partition_scheme_name ( column_name )

http://msdn.microsoft.com/en-us/library/ms188783.aspx

You'll also need to do the non-clustered indexes if they need to be aligned.

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 4

I think that partitioning the clustered index could do well in your case, unless you have enough space and time to use the insert approach.

Be aware that the number of partitions is limited to 1000.

Check "Converting a Nonpartitioned Table to a Partitioned Table" in BOL.

Modifying Partitioned Tables and Indexes
http://technet.microsoft.com/en-us/library/ms175864.aspx


You will find good stuff in Dan Guzman's Blog, that could help you to get familiar with this theme.

Sliding Window Table Partitioning

Automating Sliding Window Maintenance

Automating RANGE RIGHT Sliding Window Maintenance

Partition Details and Row Counts

SQL Server Partition Details Custom Report


I also suggest to read these two outstanding white papers about table partitioning.

Partitioned Tables and Indexes in SQL Server 2005

Partitioned Table and Index Strategies Using SQL Server 2008


AMB

Answer 5

His requirement is to partition the table, and tables are only partitioned when they are created.

---

Abdallah, PMP, MCTS

Answer 6

Cheers i will try this and let you know how i get on

---

Akiraikonics

Answer 7

Hello Abdallah

OPs requirement for partitioning (in SQL2005/2008) can be achieved by rebuilding the clustered index.

Please read the links provided in the post above by Hunchback.

Regards

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 8

I see. But that basically means dropping the table and recreating it using the new clustered index in one step, right?

---

Abdallah, PMP, MCTS

Answer 9

Hi Abdallah

Well, that's one way of looking at it - but only if you consider rebuilding a clustered index to be same as dropping and recreating the table.

The difference is that FKs, constraints and other NC indexes are preserved during the rebuild operation.

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 10

Thank you Ewan.

---

Abdallah, PMP, MCTS

Answer 11

Hey everyone i did the following  


-- first create partition function and its range

 

CREATE PARTITION FUNCTION

Facttable_function

 

(int)

 

AS RANGE LEFT

 

FOR VALUES

 

(20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 20070701, 20070801, 20070901, 20071001, 20071101, 20071201 -- =< 2007

 

,20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 20080701, 20080801, 20080901, 20081001, 20081101, 20081201 -- = 2008

 

,20090101, 20090201, 20090301, 20090401, 20090501, 20090601, 20090701, 20090801, 20090901)--, 20091001, 20091101, 20091201 -- = 2009

 

 

 

-- Create schema

CREATE

 

PARTITION SCHEME

Facttable_Scheme

 

AS PARTITION Factcdr_total_function

 

TO ([PRIMARY], [PRIMARY]...etc how ever many is needed for the function-- recreate indx

-- recreate indx

 

CREATE

 

CREATE

CREATE

 

INDEX ix_kdate

 

ON dbo.FactTABLE(kdate)

 

ON Facttable_Scheme (kdate) (nonclustered)

Also found a hand report that analyzes this http://ssmspartcustomrpt.codeplex.com/

---

Akiraikonics

Answer 12

Hi Akiraikonics

This only creates a partitioned nonclustered index on the table. The table itself is still unpartitioned until you do the clustered index.

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 13

Hi

So created the clustered inx (it will took a while) my main issue is visibility as it is slightly different from cube partitions but of the same flavour

What would be the best way in Updating the partitions on in coming data ?

CREATE

 

CLUSTERED INDEX indx_kdate

ON

 

dbo.FactTABLE(kdate)

WITH

 

(DROP_EXISTING = ON)

ON

 

FactTABLEScheme (kdate)

---

Akiraikonics

Answer 14

Hi Akiraikonics

After you partition the table, all new inserted/updated data is automatically distributed according to the partition scheme.

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 15

Hi Akiraikonics

If your question has been answered can you mark any helpful posts, and any answers, to close the thread. This keeps the forums tidy and helps those who experience the issue in future to find relevant answers.

Regards

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.