none
Create Partitions on Existing SQLDatawarehouse Table . RRS feed

  • Question

  • Hello,

    We have table [tblabc] with 222183693 records in it, We require to create Partition on the  this table based on Quarter like (2016Q1,2016Q2,2016Q3,2016Q4 till 2019Q4. ).

    CREATE TABLE [tblAbc]

    (

           ID int,

           Column1 varchar(100),

           R_Date DATE

    )

    To Implement this, We have created [tblpqr] with required partitions  and as same schema structure as [tblabc]

    CREATE TABLE [tblPqr]

    (

           ID int,

           Column1 varchar(100),

           R_Date DATE

    )

    WITH

    (

           DISTRIBUTION = HASH ( [ID] ),

           CLUSTERED COLUMNSTORE INDEX,

           PARTITION

           (

                  [R_Date] RANGE RIGHT FOR VALUES ( N'2016-03-31T00:00:00.000', N'2016-06-01T00:00:00.000', N'2016-09-30T00:00:00.000', N'2016-12-31T00:00:00.000',

                                                    N'2017-03-31T00:00:00.000', N'2017-06-30T00:00:00.000', N'2017-09-30T00:00:00.000', N'2017-12-31T00:00:00.000',

                                                    N'2018-03-31T00:00:00.000', N'2018-06-30T00:00:00.000', N'2018-09-30T00:00:00.000', N'2018-12-31T00:00:00.000',

                                                    N'2019-03-31T00:00:00.000', N'2019-06-30T00:00:00.000', N'2019-09-30T00:00:00.000', N'2019-12-31T00:00:00.000',

                                                    N'2020-03-31T00:00:00.000', N'2020-06-30T00:00:00.000', N'2020-09-30T00:00:00.000', N'2020-12-31T00:00:00.000',

                                                    N'2021-03-31T00:00:00.000', N'2021-06-30T00:00:00.000', N'2021-09-30T00:00:00.000', N'2021-12-31T00:00:00.000',

                                                    N'2022-03-31T00:00:00.000', N'2022-06-30T00:00:00.000', N'2022-09-30T00:00:00.000', N'2022-12-31T00:00:00.000')

           )

    )

    GO

    /*We have moved data from tblabc into tblpqr*/

    INSERT INTO [tblPqr]

    SELECT * from [tblAbc]

    /*Rename [tblPqr] to [tblAbc] */

    RENAME OBJECT [tblAbc] TO [tblAbc_old];

    RENAME OBJECT  [tblPqr]TO [tblAbc];

    Apart from above solutions (creating new table tblPqr and moving data into tblpqr). Do we have any other method through which we can directly create Partitions on tblABC itself. Please let us know.


    Friday, July 26, 2019 11:17 AM

All replies

  • Hello,

    Any updates on this ?
    Friday, July 26, 2019 1:02 PM
  • Hi Gourav,

    The clustered column store index needs to be disabled for this operation to be run successfully. Please see the following steps to partition a table that already contains data (link). 

    "Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete."

    Apologies for the delay in getting to you on this topic. You have the correct approach except for the disable/re-enable column store index step.

    Please let us know if you have any additional questions.

    Regards,

    Mike

    Monday, July 29, 2019 11:58 PM
    Moderator
  • Now Suppose , I have table with below schema (without any partitions) with record count 2073939792.

    CREATE TABLE [SAPBW].[ABC]
    (
    [CALENDAR DATE] DATETIME,
    [MATERIAL] bigint (4) ,
    [ZDSCP_VLL] [money] NULL
    )
    WITH
    (
    DISTRIBUTION = HASH ( [MATERIAL] ),
    CLUSTERED COLUMNSTORE INDEX
    )

    Now Can we create partitions on this existing table [SAPBW].[ABC].? 

    We don’t want create new table with partitions and transfer existing data(from [SAPBW].[ABC]) into that like above approach.




    Friday, August 2, 2019 9:19 AM
  • Hi Gourav,

    Ya, I see what you are trying to avoid. The partition setup is done at the time of table creation and since you have data loaded to that table, you will need to export, re-create the table, and then import the data back. I am looking to see if there is a possible solution that does not require all this data movement. 

    Let me research this further.

    Regards,

    Mike

    Friday, August 2, 2019 8:05 PM
    Moderator
  • Thank you Mike. Please let me know if you get anything here . 

    MSDN document don’t have any information related to this(Create 
    partitions on existing table) so i have raised ticket here.

    Regards

    Gourav


    Monday, August 5, 2019 6:44 AM
  • Hi Gourav,

    In looking far and wide for a possible solution, there is no way to perform an action where a non-partitioned table can be converted to a partitioned table. You must perform an off-line operation to recreate the table.

    Please let us know if you have additional questions about this topic.

    Regards,

    Mike

    Tuesday, August 6, 2019 9:37 PM
    Moderator
  • Hi Gourav,

    I am proposing my last response as the answer since it is the expected functionality. What you are looking for is unsupported. You can make a User Voice entry to request this functionality. 

    Regards,

    Mike

    Wednesday, August 14, 2019 1:10 AM
    Moderator
  • Hi Gourav,

    If you rebuild the clustered index with partition schemes then the table will become a Partitioned table. But this feature available only Azure SQL database and NOT on DW.

    in Azure DW, We can use CTAS for changing non partitioned to Partition table. It will give a better performance compare to Insert - Select. No other option in DW

    CREATE TABLE [dbo].[NewTable] WITH ( DISTRIBUTION = HASH([ProductKey]) , CLUSTERED COLUMNSTORE INDEX , PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES (20000101,20010101 ) ) ) AS SELECT * FROM [dbo].[OldTbale] ;

    Thursday, August 15, 2019 1:27 PM