Step by step Partition Table in SQL Server

Question

Hi All,

We have several tables with rows that have reached billions, we plan to partition the tables, but we don't have the experience to do it. From the research we did, here are the steps in more detail:

• Create file groups that hold the partitions of the table.
• Create a partition function that maps the rows of the table into partitions based on the values of a specified column.
• Create a partition scheme that maps the partition table to the new filegroups
• Create the table based on the partition scheme with clustered index.
• Insert Data (rows) to new partitioned table.

We have tried to do this on the testing table, but we are still unsure whether it is correct or not. because what our client wants is that the data (rows) that has been partitioned can be archived so that it can reduce the database size. Has anyone experienced the same thing for this?

Really appreciate for your answer.

Answer 1

Hi @Arief Hardiansyah ,

Your program is generally fine, but you need to be aware that
The scope of a partition function and scheme is limited to the database in which they have been created. Within the database, partition functions reside in a separate namespace from other functions.
If any rows in a partitioned table have NULLs in the partitioning column, these rows are placed on the left-most partition. However, if NULL is specified as the first boundary value and RANGE RIGHT is specified in the partition function definition, then the left-most partition remains empty, and NULLs are placed in the second partition.

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".