June 18, 2013 at 1:41 am
Hi All,
I have partitioned five tables with same date range for all tables(with single partition function and single partition scheme) . Now i want to remove partition from one table among the five tables(Remaining 4 tables should have partition) .
How i can remove partition from that singe table with out losing any data. I have 20 millions data in that table.
Note: While creating partition , is it possible to create non clustered index on table partitioned column.
If it possible, is there any impacts on partition.
June 18, 2013 at 1:43 am
Ramana Reddy P (6/18/2013)
How i can remove partition from that singe table with out losing any data. I have 20 millions data in that table.
Rebuild the table and specify a filegroup instead of a partition scheme.
Note: While creating partition , is it possible to create non clustered index on table partitioned column.
If it possible, is there any impacts on partition.
Yes. Create the nonclustered indexes on the same partition scheme that the table uses.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 12:50 am
For removing the partition: I had dropped the non clustered index on partitioned column and created non clustered index with primary file group. even though partitioned is not remove.
i executed below query before removing partitioned index and after re-creating the index(with out partition). It is returning same out put.
SELECT $PARTITION.testFn1(LMD) AS Partition,
COUNT(*) AS [COUNT]
FROM tblpart2
GROUP BY $PARTITION.testFn1(LMD)
ORDER BY Partition ;
Can u explain how to remove partition on single table.
June 19, 2013 at 12:53 am
For removing the partition: I had dropped the non clustered index on partitioned column and created non clustered index with primary file group. even though partitioned is not remove.
i executed below query before removing partitioned index and after re-creating the index(with out partition). It is returning same out put.
SELECT $PARTITION.testFn1(LMD) AS Partition,
COUNT(*) AS [COUNT]
FROM tblpart2
GROUP BY $PARTITION.testFn1(LMD)
ORDER BY Partition ;
Can u explain how to remove partition on single table.
June 19, 2013 at 1:02 am
Ramana Reddy P (6/19/2013)
Can u explain how to remove partition on single table.
I did.
Rebuild the table (heap or clustered index) onto a filegroup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 1:26 am
you mean, Rebuilding the index or table,
I have no idea, how to rebuild "Table" and verified in msdn for re-building the table , I didn't get any information on this.
Please provide example script for re-build the table.
June 19, 2013 at 1:51 am
Rebuild the table (heap or clustered index) onto a filegroup.
Not a nonclustered index. Rebuilding the clustered index is done just the same way as rebuilding a nonclustered index, you just specify the clustered index name.
Does the table have a clustered index?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 2:50 am
I rebuild the clustered index. Event though , partition is not removed. I used the below script for rebuilding the index.
ALTER INDEX [PK_tblpart2] ON [dbo].[tblpart2] REBUILD WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
June 19, 2013 at 2:59 am
Rebuild it onto a filegroup, not just rebuild it.
CREATE CLUSTERED INDEX .... WITH DROP_EXISTING ON [Filegroup Name]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 3:20 am
I executed this one also, Partition not removed .
ALTER TABLE [dbo].[tblPart2] DROP CONSTRAINT [PK_tblPart2]
GO
ALTER TABLE [dbo].[tblPart2] ADD CONSTRAINT [PK_tblPart2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Could you please provide script for dropping the partition.
June 19, 2013 at 3:25 am
How are you identifying that the partitions are still there?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 5:02 am
By using this below scripts.It showing data from each partition
--Getting each partitin record count
SELECT $PARTITION.testfn1(LMD) AS Partition,
COUNT(*) AS [COUNT] FROM tblPart2
GROUP BY $PARTITION.testfn1(LMD)
ORDER BY Partition ;
OutPut
PartitionCOUNT
1 5
2 3
3 6
4 11
5 5
6 15
--Getting partition tables
Select s.name As SchemaName, t.name As TableName
From sys.tables t
Inner Join sys.schemas s On t.schema_id = s.schema_id
Inner Join sys.partitions p on p.object_id = t.object_id
Group By s.name, t.name
Having Count(*) > 1
Order By s.name, t.name;
June 19, 2013 at 5:22 am
No, it's not showing there's data in multiple partitions.
From Books online:
$PARTITION returns the partition number for any valid value, regardless of whether the value currently exists in a partitioned table or index that uses the partition function.
SELECT $PARTITION.testfn1(1) will return a partition value. It's just telling you what partition of that function that value would belong to if it were in a table or index created on a partition scheme.
The check of sys.partitions also isn't valid as there's minimum 1 partition per index, hence an unpartitioned table with one clustered and one nonclustered index will give you a result of 2 from that last query.
I tested earlier, built a table, created the clustered index on a partition scheme and then recreated the clustered index on primary and the resultant table was not partitioned.
The code to convert a partitioned clustered index (ie table) to an unpartitioned table is:
CREATE CLUSTERED INDEX <index name> ON <table name> (<Clustered index column name>)
WITH DROP_EXISTING
ON <filegroup name>
You can see after running that code that the table is unpartitioned with this:
SELECT i.name, i.type_desc, ds.name AS FileGroupName, ds.type_desc AS StorageType
FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE t.name = '<table name here>' AND index_id = 1
If storage type is 'ROWS_FILEGROUP', then the table is unpartitioned, if the storage type is 'PARTITION_SCHEME', then the table is partitioned.
You can use the same query to check nonclustered indexes, just change the index id or filter rather on the index name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply