October 11, 2010 at 9:30 am
I have a partitioned tabled that uses a partition scheme but we've just noticed that the wrong column was passed when the table was created.
How can I alter the table so that the same partition scheme is used but it is driven by a different column with the same data type as the original partitioning column?
October 11, 2010 at 2:26 pm
you'll need to create a new table and insert the date from the old one.
The probability of survival is inversely proportional to the angle of arrival.
October 12, 2010 at 5:49 am
Thanks - I've just finsihed a simple transfer script.
October 12, 2010 at 6:10 am
You've probably already followed the method of transferring data to a new table but here is another approach that uses the MOVE clause to alter the table to use a different partition key(I think that is what you wanted right?)...
/*
-- modify the DB name and file names as appropriate
*/
USE [ScratchPad];
GO
-- clean up stuff
IF OBJECT_ID('dbo.TestTable') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTable;
END
GO
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'TestPartitionSch')
BEGIN
DROP PARTITION SCHEME TestPartitionSch;
END
GO
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'TestPartitionfn')
BEGIN
DROP PARTITION FUNCTION TestPartitionfn;
END
IF EXISTS (SELECT 1 FROM sys.filegroups WHERE name = 'PartitionFG1')
BEGIN
ALTER DATABASE [ScratchPad] REMOVE FILE [PartitionFile1];
ALTER DATABASE [ScratchPad] REMOVE FILEGROUP PartitionFG1;
END
GO
IF EXISTS (SELECT 1 FROM sys.filegroups WHERE name = 'PartitionFG2')
BEGIN
ALTER DATABASE [ScratchPad] REMOVE FILE [PartitionFile2];
ALTER DATABASE [ScratchPad] REMOVE FILEGROUP PartitionFG2;
END
-- create filegroups
ALTER DATABASE [ScratchPad] ADD FILEGROUP PartitionFG1;
GO
ALTER DATABASE [ScratchPad] ADD FILEGROUP PartitionFG2;
-- create files
ALTER DATABASE [ScratchPad] ADD FILE (NAME = PartitionFile1,FILENAME='C:\PartitionFile1.ndf',SIZE = 128MB,MAXSIZE = 256MB,FILEGROWTH=16MB)TO FILEGROUP [PartitionFG1];
GO
ALTER DATABASE [ScratchPad] ADD FILE (NAME = PartitionFile2,FILENAME='C:\PartitionFile2.ndf',SIZE = 128MB,MAXSIZE = 256MB,FILEGROWTH=16MB)TO FILEGROUP [PartitionFG2];
GO
-- create PF
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'TestPartitionfn')
DROP PARTITION FUNCTION TestPartitionfn
GO
CREATE PARTITION FUNCTION TestPartitionfn (int)
AS RANGE LEFT FOR VALUES (1000);
GO
-- create PS
CREATE PARTITION SCHEME TestPartitionSch
AS PARTITION TestPartitionfn
TO (PartitionFG1,PartitionFG2);
GO
-- partition on number column first
CREATE TABLE dbo.TestTable(ID int IDENTITY(1,1) NOT NULL,name varchar(100) NOT NULL,number int NOT NULL,
number2 int NOT NULL) ON TestPartitionSch(number);
GO
INSERT dbo.TestTable(name,number,number2)
SELECT 'NAME:'+CAST(ROW_NUMBER() OVER (ORDER BY name) AS varchar(30)),number,2000 FROM master.dbo.spt_values;
GO
-- will partition align
ALTER TABLE dbo.TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED(number,ID);
GO
CREATE NONCLUSTERED INDEX NC_TestTable ON dbo.TestTable(number,name);
-- check FG
SELECT ds.name FGName,i.name,i.type_desc,sc.name FROM sys.data_spaces ds INNER JOIN sys.indexes i ON
i.object_id = OBJECT_ID('dbo.TestTable')
--AND i.index_id = 1 -- clustered index only
AND i.data_space_id = ds.data_space_id
INNER JOIN sys.index_columns sic ON
sic.object_id = i.object_id AND
sic.index_id = i.index_id
INNER JOIN sys.columns sc ON
sc.object_id = sic.object_id AND
sc.column_id = sic.column_id
ORDER BY i.type_desc
GO
-- check partitions - number is the currently partition key
-- will give # of rows 1217 and 1289 across 2 partitions as the partition key
-- is number column
SELECT partition_id,index_id,partition_number,rows FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.TestTable')
ORDER BY index_id;
GO
-- now move the table to a different partition key (number2) on the same partition scheme
ALTER TABLE dbo.TestTable DROP CONSTRAINT PK_TestTable WITH (MOVE TO TestPartitionSch(number2));
GO
-- recreate PK with number2 column
ALTER TABLE dbo.TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED(number2,ID);
-- recreate NC for partition align - if you want to
CREATE NONCLUSTERED INDEX NC_TestTable ON dbo.TestTable(number2,name) WITH (DROP_EXISTING=ON);
GO
-- check FG
SELECT ds.name FGName,i.name,i.type_desc,sc.name FROM sys.data_spaces ds INNER JOIN sys.indexes i ON
i.object_id = OBJECT_ID('dbo.TestTable')
--AND i.index_id = 1 -- clustered index only
AND i.data_space_id = ds.data_space_id
INNER JOIN sys.index_columns sic ON
sic.object_id = i.object_id AND
sic.index_id = i.index_id
INNER JOIN sys.columns sc ON
sc.object_id = sic.object_id AND
sc.column_id = sic.column_id
ORDER BY i.type_desc
GO
-- check partitions - number is the currently partition key
-- will give # of rows 2506 in one partiton as number2 column is
-- partition key and has value 2000 across all rows and based on the
-- partition function all rows will come into one partition only
SELECT partition_id,index_id,partition_number,rows FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.TestTable')
ORDER BY index_id;
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply