|SQL Server and Table partitionning|
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)
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
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
INSERT INTO mySchema.newPArtitionTable(idValo,idBundle)
sp_rename 'mySchema.PK_MyPrimaryKey', 'PK_MyPrimaryKey_xxx'
DROP TABLE dbo.myTable