0

I have one large table in SQL Server (around 5,5 billion rows) and I intend to create new partitioned table for that data, probably by year, with filegroup for each year for better management. This is fact table with transactional data which is loaded daily (around 7 million rows) and is not queried often and is never updated. My idea was to load data in new table and make filegroups for past data read only.

Question is what would be best way to load data in new table. Should I load into heap table and then create CCI or create CCI before loading data? Are there any other considerations I should make?

1 Answer 1

2

Should I load into heap table and then create CCI or create CCI before loading data? Are there any other considerations I should make?

and

I intend to create new partitioned table for that data

means you should create your table as clustered on the partition scheme. If you first create partitioned heap, once you create clustered index on it your partitioning will gone, and creating clustered index on the partition scheme over the heap with 5,5 billion rows will take too much time and your database log will likely grow so much you just cannot expect how.

So you should first create empty clustered table on the partition scheme. Then I'd create a staging table to accomodate the data for one year, on the same filegroup where the partition where you want to move your data resides. For example your first partition is intended for 2015, create staging table on the same filegroup by doing select ... into myStagingTbl from myTbl where year = 2015. Create clustered index on this table and create check constraint: year = 2015. Now you can switch myStagingTbl to your empty partitioned table. Repeat this for every partition.

5
  • How do you mean partitioning will be gone after I create clustered index on partitioned heap? Could I load data by year via staging table into intended partition as heap and create CCI on that partition after that?
    – Ludijak
    Mar 5 at 16:05
  • @Ludijak There cannot be "one clustered partition and others no", the entire table is either clustered or a heap
    – sepupic
    Mar 5 at 16:34
  • @Ludijak by creating clustered index on the staging table (the same as on your partitioned table) you already have one ready partition that you only switch into your destination, no "load data" occurs, switch operation within the same filegroup is metadata-only operation
    – sepupic
    Mar 5 at 16:47
  • What is difference between in final result when using staging table as you described versus loading directly in partitioned table with created CCI?
    – Ludijak
    Mar 6 at 15:06
  • @Ludijak In your case it may be the same, to create first staging table or to load into your destination (just not to forget to specify the correct filter for every portion of data: where myyearcolumn >= boundary1 and myyearcolumn < boundary2 in order to insert in one partition at a time). I suggested to use staging table in general case when data load into destination can cause blocking, not in your case when the destination is not in use. The solution with switch will take a very short lock on destination table, less than 1 second
    – sepupic
    Mar 6 at 16:16

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.