Apply partition concept to existing table

Question

Apply partition concept to existing table

amit-1143912

Right there with Babe

Points: 774
More actions
October 6, 2010 at 9:28 am

#387949

I have created partition function and partition scheme.
I already have a table with 70 lakhs of records, so I cannot recreate this table. Clustered index not to be created on this table.
how can I apply the partiton scheme to the existing table or a exisiting table with non clustered index.

Viewing 5 posts - 1 through 4 (of 4 total)

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

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

If you are not space constrained and it is imperative that you cannot have down time, I would consider creating a duplicate table based on the partition function of a different name. Then create an insert/update/delete trigger on the source table to fill the destination table. This will ensure the data remains consistant and 100% available. The run an update statement (could be a cursor or while loop if you like) against the whole table setting col1 = col1.

Then when ready, sp_rename both so that one replaces the other. Downtime should be less than 100ms i would guess.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

amit-1143912 (10/6/2010)
...I cannot recreate this table. Clustered index not to be created on this table.
how can I apply the partiton scheme to the existing table or a exisiting table with non clustered index.

Well... those constraints are not helping, huh?

What is the root cause table can't be recreated? is it space or is it downtime?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Reo Hall of Fame Points: 3774 More actions · Answer 3

Hi,

1. Reneme your existing table (Non-Partitioned table)

2. Created Partitioned Function, Schema and New Partitioned Table

3. Upload Non-Partitioned Table data into Partitioned Table

4. Drop Non-Partitioned table

5. Create Indexes if you wish

There is no possibility to apply Partitioning on an existing non-partitioned table

PaulB-TheOneAndOnly (10/6/2010)

amit-1143912 (10/6/2010)
What is the root cause table can't be recreated? is it space or is it downtime?

SQL Server does not have option/feature to move existing table or indexe among filegroups. To moving an existing table to another filegroup you have to recreate table on new filegroup and then move data.

I would like to suggest you please do deep study on SQL Server Internals and understand SQL Server Architecture, File and File Groups, Partitioning, Indexes, B-Tree, Heap, Pages, Extents and lots more.

Ram
MSSQL DBA

winash SSChampion Points: 11446 More actions · Answer 4

Reo (10/6/2010)
SQL Server does not have option/feature to move existing table or indexe among filegroups. To moving an existing table to another filegroup you have to recreate table on new filegroup and then move data.

There is a MOVE TO clause available in the ALTER TABLE syntax to move a table across filegroups. From BOL:

When a constraint that created a clustered index is deleted, the data rows that were stored in the leaf level of the clustered index are stored in a nonclustered table. You can drop the clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option. The MOVE TO option has the following restrictions:

MOVE TO is not valid for indexed views or nonclustered indexes.

The partition scheme or filegroup must already exist.

If MOVE TO is not specified, the table will be located in the same partition scheme or filegroup as was defined for the clustered index.

USE [ScratchPad];

GO

-- create filegroup

ALTER DATABASE [ScratchPad] ADD FILEGROUP PartitionFG;

GO

-- create file for this filegroup

ALTER DATABASE [ScratchPad] ADD FILE

(NAME = PartitionFile,FILENAME='C:\PartitionFile.ndf',SIZE = 128MB,MAXSIZE = 256MB,FILEGROWTH=16MB) TO FILEGROUP [PartitionFG];

GO

-- create table on the primary FG

CREATE TABLE dbo.TableToMove(ID int IDENTITY(1,1),name varchar(100),number int) ON [PRIMARY];

GO

INSERT dbo.TableToMove(name,number)

SELECT 'NAME:'+CAST(ROW_NUMBER() OVER (ORDER BY name) AS varchar(30)),number FROM master.dbo.spt_values;

GO

-- create clustered PK

ALTER TABLE dbo.TableToMove ADD CONSTRAINT PK_TableToMove PRIMARY KEY CLUSTERED(ID);

GO

-- check which FG

SELECT ds.name FGName,i.type_desc

FROM sys.data_spaces ds INNER JOIN sys.indexes i ON

i.object_id = OBJECT_ID('dbo.TableToMove')

AND i.index_id IN (0,1) -- heap/clustered index only

AND i.data_space_id = ds.data_space_id

GO

-- drop the constraint and move data to new FG

ALTER TABLE dbo.TableToMove DROP CONSTRAINT PK_TableToMove WITH (MOVE TO [PartitionFG]);

GO

-- check which FG

SELECT ds.name FGName, i.type_desc

FROM sys.data_spaces ds INNER JOIN sys.indexes i ON

i.object_id = OBJECT_ID('dbo.TableToMove')

AND i.index_id IN (0,1) -- heap/clustered index only

AND i.data_space_id = ds.data_space_id