Master Table Partitioning in SQL Server: Boost Query Performance

Big data handling can be painful without DBA skills, so learning some database tricks is useful. This article shares common techniques.

Method 1: Table Partitioning

Table partitioning splits a large table into several physical tables while keeping it logically as one table, improving query performance for massive data sets such as a user table with millions of rows.

Example steps: create file groups, data files, a partition function, a partition scheme, and then assign a table to the scheme.

CREATE PARTITION FUNCTION partitionFunArea (nvarchar(50))
AS RANGE LEFT FOR VALUES ('广东','湖南','四川');

CREATE PARTITION SCHEME partitionSchemeArea
AS PARTITION partitionFunArea TO (Area01, Area02, Area03, Area04);

CREATE TABLE TestUser(
    [Id] int IDENTITY(1,1) NOT NULL,
    [Area] nvarchar(50),
    [UserName] nvarchar(50)
) ON partitionSchemeArea([Area]);

INSERT TestUser (Area,UserName) VALUES ('四川','肖一');
INSERT TestUser (Area,UserName) VALUES ('四川','肖二');
INSERT TestUser (Area,UserName) VALUES ('四川','肖三');
INSERT TestUser (Area,UserName) VALUES ('四川','肖四');
INSERT TestUser (Area,UserName) VALUES ('广东','张一');
INSERT TestUser (Area,UserName) VALUES ('广东','张二');
INSERT TestUser (Area,UserName) VALUES ('广东','张三');
INSERT TestUser (Area,UserName) VALUES ('湖南','杨一');
INSERT TestUser (Area,UserName) VALUES ('湖南','杨二');

Querying by partition:

SELECT $PARTITION.partitionFunArea([Area]) AS PartitionNumber, COUNT(id) AS RecordCount
FROM TestUser
GROUP BY $PARTITION.partitionFunArea([Area]);

SELECT * FROM TestUser WHERE $PARTITION.partitionFunArea([Area]) = 1;
SELECT * FROM TestUser WHERE $PARTITION.partitionFunArea([Area]) = 2;
SELECT * FROM TestUser WHERE $PARTITION.partitionFunArea([Area]) = 3;
SELECT * FROM TestUser WHERE $PARTITION.partitionFunArea([Area]) = 4;

The simple partitioning example works, though real scenarios may require adding or dropping partitions.

Method 2: Using XML Type Instead of Master‑Detail Tables

Improving database performance often starts with better schema design. A meeting reservation system typically uses multiple tables (Meeting, MeetingUser, MeetingDevice), leading to complex joins and costly updates.

Storing all meeting information in a single XML column simplifies queries—C# LINQ‑XML can parse the XML, and updates require only one UPDATE statement. Advanced queries, such as finding all meetings a user participated in, can be handled with XQuery functions like exist() and value().

These techniques provide practical ways to handle large data sets and improve query performance.