-2

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
0
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.