Ok, so you have a table which grew huge – way bigger than you’d like, and loading new data into it takes forever. Time to split it, so at least new data goes into new partitions.
So, say we have a table t, which contains 100 recods, and we want next records to go into different partition. We also want to keep the name.
Here are the steps.
1. If the database is using a single file group, create new ones. Nice if they’re on a different disks, but it’s not necessary.
— create new file groups
ALTER DATABASE db ADD FILEGROUP fg_db_1
–add new phisical files to database
ALTER DATABASE db
ADD FILE
(
NAME = fg_db_1_data, -- this you just make up in this step
FILENAME = 'c:SqlDatadb1.ndf'
) TO FILEGROUP fg_db_1;
Repeat this step if you want to create multiple file groups.
2. Create partition function.
CREATE PARTITION FUNCTION pfFunc (int) AS RANGE RIGHT FOR VALUES (100, 200, 300); -- values will be used to split data into different partitions; 3 values create 4 partitions.
3. Create partition scheme
CREATE PARTITION SCHEME partScheme1 AS PARTITION pfFunc TO ([PRIMARY], fg_db_1); -- [PRIMARY] will enable to use existing table. You must have as many partitions as number of splits made by function.
4. Rename the source table (say, new name is t1).
5. Create new partitioned table with same structure as t1 on partition scheme
CREATE TABLE [dbo].[t](
[ID] [int] NOT NULL primary key,
Name [varchar](9) NULL
) ON partScheme1(ID)
— ID is the column that will be used to partition this table
6. Add check constraint to a source table t1. Say, if you’re splitting this table by id, then
ALTER TABLE dbo.t1 ADD CONSTRAINT idLimit CHECK (id < 101) -- 101 because we're using RANGE RIGHT in partition function.
If we don’t do this – we’ll get this exception: ALTER TABLE SWITCH statement failed. Check constraints of source table allow values that are not allowed by range defined by partition 1 on target table
7. Add source table to new partitioned table
ALTER TABLE dbo.t1 SWITCH TO t PARTITION 1
Done.
At this point you can drop the source table, so it doesn’t clutter the list – the data will remain in target table.
Note that you’ll have to recreate all the indexes you had in source table in target table.
To view how many rows per partiotion use this query:
SELECT * FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='t';
To view range values:
Select b.name, a.value from sys.partition_range_values a
inner join sys.partition_functions b on a.function_id = b.function_id
where name = 'pfFunc'