Partitioned table showing false in table properties

  • Hi,

    I have partitioned a large table in test database which was performing slow archiving process using the below query

    CREATE PARTITION FUNCTION TransactionHistoryPartitions (datetime)

    AS RANGE RIGHT FOR VALUES ( '2015-10-05', '2015-10-31')

    GO

    CREATE PARTITION SCHEME TransactionHistory_PartitionScheme

    AS PARTITION TransactionHistoryPartitions

    TO (FG1, FG2, [PRIMARY] )

    GO

    CREATE NONCLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.Transaction_history (date_time_stamp)

    WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    ON TransactionHistory_PartitionScheme(date_time_stamp)

    GO

    SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]

    FROM sys.partitions p

    INNER JOIN sys.objects o ON o.object_id=p.object_id

    INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id

    WHERE o.name LIKE '%transaction_history%'

    The result is as below

    objectnameindexnamepartition_idpartition_numberrowsname

    AR_TRANSACTION_HISTORYNULL7205759887946547211805135FG1

    TRANSACTION_HISTORYXIE2TRANSACTION_HISTORY72057599821021184122833928FG1

    TRANSACTION_HISTORYXIE6TRANSACTION_HISTORY72057599821283328122833928FG1

    TRANSACTION_HISTORYXIE7TRANSACTION_HISTORY72057599821348864122833928FG1

    TRANSACTION_HISTORYXIE8_RPT_TRANSACTION_HISTORY72057599821414400122833928FG1

    TRANSACTION_HISTORYXPKTRANSACTION_HISTORY72057599821479936122833928FG1

    AR_TRANSACTION_HISTORYXIE7TRANSACTION_HISTORY_AR7205759986460262411805135FG1

    TRANSACTION_HISTORYXIE1TRANSACTION_HISTORY72057599869255680122833928FG1

    TRANSACTION_HISTORYXIE3TRANSACTION_HISTORY72057599869321216122833928FG1

    TRANSACTION_HISTORYXIE4TRANSACTION_HISTORY72057599869386752122833928FG1

    TRANSACTION_HISTORYXIE5TRANSACTION_HISTORY72057599869452288122833928FG1

    TRANSACTION_HISTORYIX_TABLE1_partitioncol72057599871680512118609348FG1

    TRANSACTION_HISTORYIX_TABLE1_partitioncol7205759987174604824223912FG2

    TRANSACTION_HISTORYIX_TABLE1_partitioncol720575998718115843668PRIMARY

    In the results it is showing as partitioned into different file groups but in properties it appears as false. Kindly help

  • rameelster (11/30/2015)


    Hi,

    I have partitioned a large table in test database which was performing slow archiving process using the below query

    CREATE PARTITION FUNCTION TransactionHistoryPartitions (datetime)

    AS RANGE RIGHT FOR VALUES ( '2015-10-05', '2015-10-31')

    GO

    CREATE PARTITION SCHEME TransactionHistory_PartitionScheme

    AS PARTITION TransactionHistoryPartitions

    TO (FG1, FG2, [PRIMARY] )

    GO

    CREATE NONCLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.Transaction_history (date_time_stamp)

    WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    ON TransactionHistory_PartitionScheme(date_time_stamp)

    GO

    SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]

    FROM sys.partitions p

    INNER JOIN sys.objects o ON o.object_id=p.object_id

    INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id

    WHERE o.name LIKE '%transaction_history%'

    The result is as below

    objectnameindexnamepartition_idpartition_numberrowsname

    AR_TRANSACTION_HISTORYNULL7205759887946547211805135FG1

    TRANSACTION_HISTORYXIE2TRANSACTION_HISTORY72057599821021184122833928FG1

    TRANSACTION_HISTORYXIE6TRANSACTION_HISTORY72057599821283328122833928FG1

    TRANSACTION_HISTORYXIE7TRANSACTION_HISTORY72057599821348864122833928FG1

    TRANSACTION_HISTORYXIE8_RPT_TRANSACTION_HISTORY72057599821414400122833928FG1

    TRANSACTION_HISTORYXPKTRANSACTION_HISTORY72057599821479936122833928FG1

    AR_TRANSACTION_HISTORYXIE7TRANSACTION_HISTORY_AR7205759986460262411805135FG1

    TRANSACTION_HISTORYXIE1TRANSACTION_HISTORY72057599869255680122833928FG1

    TRANSACTION_HISTORYXIE3TRANSACTION_HISTORY72057599869321216122833928FG1

    TRANSACTION_HISTORYXIE4TRANSACTION_HISTORY72057599869386752122833928FG1

    TRANSACTION_HISTORYXIE5TRANSACTION_HISTORY72057599869452288122833928FG1

    TRANSACTION_HISTORYIX_TABLE1_partitioncol72057599871680512118609348FG1

    TRANSACTION_HISTORYIX_TABLE1_partitioncol7205759987174604824223912FG2

    TRANSACTION_HISTORYIX_TABLE1_partitioncol720575998718115843668PRIMARY

    In the results it is showing as partitioned into different file groups but in properties it appears as false. Kindly help

    You created a partition function, a partition scheme, and an index on that scheme. No where are you applied the partitioning to the table. You either need to recreate the table on the partition scheme and transfer the data to it from the monolithic table or create the clustered index for the for the monolithic table on the partition scheme.

    I'll also recommend that names like FG1 and FG2 aren't going to cut it for ease in maintenance or troubleshooting. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply