Partitioned table showing false in table properties

Question

Partitioned table showing false in table properties

rameelster

Right there with Babe

Points: 779
More actions
November 30, 2015 at 4:36 am

#306676

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

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

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)