XcDCQpSq

1create partition function pf_month(int) as range right for values (2,3,4,5,6,7,8,9,10,11,12)
2;
3create partition scheme ps_month as partition pf_month all to ( [PRIMARY] )
4;
5
6drop table if exists test
7;
8create table dbo.test(
9    id int identity(1,1) not null,
10    partition_month as (datepart(month,created_at)) persisted,
11    created_at datetime not null,
12    value varchar(100) null,
13    constraint pk_test primary key nonclustered (id) on [PRIMARY]
14) on ps_month(partition_month)
15;
16
17create clustered index ci_test on dbo.test (id, partition_month) on ps_month(partition_month)
18;
19
20insert into test (created_at)
21values ('2018-01-01'), ('2017-03-01'), ('2017-05-01')
22     , ('2017-07-01'), ('2017-09-01'), ('2017-11-01')
23     , ('2018-01-01'), ('2018-02-01'), ('2018-03-01')
24     , ('2018-04-01'), ('2018-05-01'), ('2018-06-01')
25     , ('2018-07-01'), ('2018-08-01'), ('2018-09-01')
26     , ('2018-10-01'), ('2018-11-01'), ('2018-12-01')
27;
28
29select so.name as table_name
30     , si.name as index_name
31     , sps.partition_number
32     , sps.row_count
33     , *
34  from sys.objects so
35  join sys.dm_db_partition_stats sps
36    on so.object_id = sps.object_id
37  join sys.indexes si
38    on sps.object_id = si.object_id
39   and sps.index_id = si.index_id
40 where so.object_id = object_id('test')
41 order by 1,2,3
42;