Step by step Partition Table in SQL Server

Arief Hardiansyah 51 Reputation points
2022-11-21T09:39:23.97+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,343 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
516 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. NikoXu-msft 1,911 Reputation points
    2022-11-22T02:56:36.857+00:00

    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".

    0 comments No comments