How to create partition function on two columns like integer and datetime

Can a table be partitioned in two ways? For example, both with date and with the unit

``````if unit=1 and date > 2021-09-01 save rows in file1.ndf
if unit=1 and date > 2021-10-01 save rows in file2.ndf
if unit=2 and date > 2021-09-01 save rows in file3.ndf
if unit=2 and date > 2021-10-01 save rows in file4.ndf
``````

Possible? Yes. A good idea? Perhaps not.

Here is one possible way to use a calculated column to facilitate placing rows onto individual partitions based on a composite value.

Do this in `tempdb`, so we don't inadvertently clobber your work:

``````USE tempdb;
GO
DROP TABLE dbo.Units;
DROP PARTITION SCHEME units_ps;
DROP PARTITION FUNCTION units_pf;
GO
``````

Create the partition function:

``````CREATE PARTITION FUNCTION units_pf (varchar(100))
AS RANGE LEFT
FOR VALUES
(
'001|20210901'
, '001|20211001'
, '002|20210901'
, '002|20211001'
, '002|20211101'
, '003|20210901'
);
GO
``````

Create the partition scheme:

``````CREATE PARTITION SCHEME units_ps
AS PARTITION units_pf
ALL TO ([PRIMARY]);
GO
``````

Create a table that stores rows using the partition scheme:

``````CREATE TABLE dbo.units
(
[unit]                int                 NOT NULL
, [date]                datetimeoffset(3)   NOT NULL
, [blah]                varchar(4000)       NULL
, [partition_column]
AS CONVERT(varchar(100), (RIGHT('000' + CONVERT(varchar(3), [unit], 0), 3) + '|' + CONVERT(varchar(8), [date], 112)), 0)
PERSISTED
, CONSTRAINT [units_pk]
PRIMARY KEY
CLUSTERED
([partition_column])
) ON units_ps([partition_column]);
GO
``````

Insert some sample values:

``````INSERT INTO dbo.[units] ([unit], [date], [blah])
VALUES (1, '2021-09-30T00:00:00.000Z', 'row_1')
, (2, '2021-10-31T00:00:00.000Z', 'row_2')
, (2, '2021-11-30T00:00:00.000Z', 'row_3');

SELECT *
FROM dbo.[units];
``````
unit date blah partition_column
1 2021-09-30 00:00:00.000 +00:00 row_1 001|20210930
2 2021-10-31 00:00:00.000 +00:00 row_2 002|20211031
2 2021-11-30 00:00:00.000 +00:00 row_3 002|20211130

Show the partition ranges:

``````SELECT *
FROM sys.partition_range_values prv
INNER JOIN sys.partition_schemes ps ON prv.function_id = ps.function_id
``````
function_id boundary_id parameter_id value name data_space_id type type_desc is_default is_system function_id
65555 1 1 001|20210901 units_ps 65618 PS PARTITION_SCHEME 0 0 65555
65555 2 1 001|20211001 units_ps 65618 PS PARTITION_SCHEME 0 0 65555
65555 3 1 002|20210901 units_ps 65618 PS PARTITION_SCHEME 0 0 65555
65555 4 1 002|20211001 units_ps 65618 PS PARTITION_SCHEME 0 0 65555
65555 5 1 002|20211101 units_ps 65618 PS PARTITION_SCHEME 0 0 65555
65555 6 1 003|20210901 units_ps 65618 PS PARTITION_SCHEME 0 0 65555

Show the number of rows in each partition:

``````SELECT ObjectName = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name)
, p.partition_number
, p.rows
FROM sys.partitions p
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = N'dbo'
AND o.name = N'units'
ORDER BY p.partition_number;
``````
ObjectName partition_number rows
[dbo].[units] 1 0
[dbo].[units] 2 1
[dbo].[units] 3 0
[dbo].[units] 4 0
[dbo].[units] 5 1
[dbo].[units] 6 1
[dbo].[units] 7 0