Summary of SQL Server partition tables-Summary of some questions

In the partitioned table used, you may encounter some problems. Together.

1. The same partition architecture and partition functions are applied for a structure like a primary table-A slave table, such as an order primary table-an order detail table.

In this way, alignment can be stored to Improve the Performance of Io and join queries.

However, if there are two unrelated tables, it is best not to use the same partition architecture and partition function. This is because when you adjust the partition structure of one table, it also applies to another table.

2. Each partition corresponds to a different file group and is placed on different physical disks. This is an official best practice.

However, I found it inconvenient to manage it in practice. For example, the Order table retains the data of the last 12 months and is divided into 12 partitions. Order archiving table, because the amount is too large, may be based on a partition three years ago,

Each year in the last three years, there are four partitions in total. In this way, when the "out-of-date" partition of the Order table is switched to the order archiving table, because it is not in the same file group, a zero-time intermediate table is required, and data is transmitted across file groups,

You cannot use the fast switch of the partition switch. If the data volume is large, it will slow down or use other methods.

I personally think that it is better to create a dedicated file group for these two tables, create several more data files and place them on different physical disks. Because the data files in the file group are filled by ratio, Io balancing can also be achieved.

When querying data, you can benefit from parallel disk operations. In addition, when performing partition switching in the same file group, you can apply the partition switch for fast switching.

3. for partition table queries, use partition columns as the primary search condition. When querying a single table, you can locate a specific partition. When a join query is performed, the joined columns are also partition columns. If they are stored alignment, partitions can be eliminated to further improve performance.

4. Some large tables use auto-incremental IDs for primary key indexing. Such as the product table. For such a table, the most associated with other tables is ID. If you want to partition It, I personally think that the partition column is the ID column.

However, the ID column partition is selected. When associated with other tables, only a partition of the product table can be quickly located during query, which reduces the scanning volume and improves I/O.