Partition Tables Based on a non-Date Based Column

Question

There is a scenario where many, 175+, databases must be combined into one large cloud database and this should be supported in multiple environments, dev, test, stage prod etc. Currently, the primary key is based on the client identifier which is the database from which that data came from. Row based security will be used in this scenario based on the same column. Some of the larger tables will ultimately reach close to 1 billion rows.

The problem is, especially, in the lower environments, restores are common. In order to accommodate truncating large swaths of data in a timely manner, I have been researching partitioning based on client identifier (i.e "COKE"|"PEPSI" etc.) which is also the PK of all tables. This would allow moving data in and out and possibly indexing more efficient. Can the client key be used in the partitioning function? I have experience with date-based partition to the right, however, not sure how manageable or possible to change a partition function by adding a new partition between two existing ones??

Perhaps this could be solved by using a sequential identifier for clients and as new ones come on board, they get the next id sequentially and a new partition is added to the right? Is partitioning by clients name practical in Sql Server.

Answer 1

Table partitioning in SQL Server is fundamentally range partitioning. One can still partition by exact value boundaries (list partitioning) but, without a check constraint or foreign key constraint, be aware nothing will prevent insertion of a value that doesn't exactly match an existing boundary.

not sure how manageable or possible to change a partition function by adding a new partition between two existing ones?

One can add a new boundary with a partition function SPLIT but there are considerations. A general recommendation is one should avoid splitting a non-empty partition as to avoid excessive data movement and logging. However, this isn't a consideration with list partitioning since no rows will need to be moved into the new partition, assuming no rows exist yet for the new client identifier.

New boundaries can be created before the current lowest one or after the highest.

SPLIT is an offline operation due to the SCH-M lock so it should be planned during off-peak hours. The duration will be short as long as an index with client identifier as the leftmost key column exists. Otherwise, SQL Server will need to scan the partition being split to locate any rows that need to be moved into the new partition (even though none should exist in this case).

Partition-level TRUNCATE and SWITCH can be used as a means to restore individual clients but remember it's an offline operation, albeit usually a fast one.

Below are T-SQL examples.

CREATE PARTITION FUNCTION PF_ClientIdentifier(varchar(10)) 
    AS RANGE RIGHT FOR VALUES('COKE','PEPSI');
CREATE PARTITION SCHEME PS_ClientIdentifier 
    AS PARTITION PF_ClientIdentifier ALL TO ([PRIMARY]);
CREATE TABLE dbo.PartiionedTable(
      ClientIdentifier varchar(10) NOT NULL
    , RowID int NOT NULL IDENTITY
    , CONSTRAINT PK_PartiionedTable PRIMARY KEY(ClientIdentifier, RowID)
    , CONSTRAINT UQ_PartiionedTabl_RowIDe UNIQUE(RowID, ClientIdentifier)
) ON PS_ClientIdentifier(ClientIdentifier);

--create new client boundary
ALTER PARTITION SCHEME PS_ClientIdentifier NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION PF_ClientIdentifier() SPLIT RANGE ('DRPEPPER');

--restore client partition
TRUNCATE TABLE dbo.PartiionedTable
    WITH (PARTITIONS($PARTITION.PF_ClientIdentifier('DRPEPPER')));
ALTER TABLE dbo.PartiionedTableBackup
    SWITCH PARTITION $PARTITION.PF_ClientIdentifier('DRPEPPER')
    TO dbo.PartiionedTable PARTITION $PARTITION.PF_ClientIdentifier('DRPEPPER');

--remove client boundary
TRUNCATE TABLE dbo.PartiionedTable
    WITH (PARTITIONS($PARTITION.PF_ClientIdentifier('DRPEPPER')));
ALTER PARTITION FUNCTION PF_ClientIdentifier() MERGE RANGE ('DRPEPPER');