Microsoft BizTalk France

Have you ever had a table that kep growing, while the performaces kept degrading, the first nice idea  would be to define indexes, fair enough, but as the table continues to grow so does the indexes, and still the indexes might not be sufficient. So what do you do? buy a more powerful server? 

Well this is an extreme solution, before just try to partition your huge table (well what is huge? above 10Gb is already huge in my case)

Partitionning is equivalent to create as many smaller objects as your partition boundaries

Create a Partition Function

This function allows you to define what is the column (only one could be used, and it would be nice if it is the primary key as well)to partition your table.

CREATE PARTITION FUNCTION fn_myPartitionFunction(int)
AS
-- This funtion is used to partition the table
RANGE LEFT FOR VALUES(134895, 170000, 180000, 190000, 200000)
GO

Create a Partition Scheme 

This scheme is used when creating the partitionned table and or Index, here we define which parition function is used

CREATE PARTITION SCHEME sch_myPartitionSchema
AS
partition fn_myPartitionFunction
ALL TO ([PRIMARY]) -- only one Primary File Group is used in this case
GO

Transfering data to Partitionned Table

If your table already exists,then you cannot partionned it on the fly, you need to create a new partitionned table, then move all the data to this new table, and then drop the old table and rename the new one, the steps are described below

• Create a new Partitionned Table (here a new schema is used, because, primary keys could not have the same name in the same schema)

CREATE TABLE mySchema.newPArtitionTable(
    [idValo] [int] NOT NULL,
    [idBundle] [int] NOT NULL,
  CONSTRAINT [PK_MyPrimaryKey] PRIMARY KEY CLUSTERED
  (
    [idValo] ASC,
    [idBundle] ASC
  ) ON sch_myPartitionSchema(idValo)
) ON sch_myPartitionSchema(idValo)

• Transfer the Data to the newly created table

INSERT INTO mySchema.newPArtitionTable(idValo,idBundle)
SELECT *
FROM dbo.myTable

• Rename and transfer to the default schema

sp_rename 'mySchema.PK_MyPrimaryKey', 'PK_MyPrimaryKey_xxx'
ALTER SCHEMA dbo TRANSFER mySchema.newPArtitionTable

• Drop the old table and rename the newly partitionned table

DROP TABLE dbo.myTable
sp_rename 'dbo.newPArtitionTable', 'dbo.myTable'

• Check the partition status

-- select to check that the partition is correctly set
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('dbo.myTable')