Partition split

ALTER PARTITION FUNCTION SPLIT is an operation on a Partition Function, and will affect all Partition Schemes using that function, and all tables and indexes residing on those Partition Schemes.

Here's a function to list all the partitions with their boundary values and row counts:

SELECT   
  tbl.name table_name,  
  indx.name index_name,  
  pf.name partition_function,  
  ps.name partition_scheme,  
  prt.value boundary_value,  
  p.partition_number,  
  prt.boundary_id,  
  prt.value boundary_value,  
  p.rows  
FROM   
  sys.tables AS tbl  
  JOIN sys.indexes AS indx   
    ON indx.object_id = tbl.object_id  
  JOIN sys.partition_schemes ps   
    ON indx.data_space_id = ps.data_space_id  
  JOIN sys.partition_functions pf   
    ON ps.Function_id = pf.Function_id   
  JOIN sys.partitions p   
    on p.object_id = indx.object_id  
    and p.index_id = indx.index_id  
  LEFT JOIN sys.partition_range_values prt   
    on prt.function_id = pf.function_id  
    and prt.boundary_id + case when pf.boundary_value_on_right = 1 then 1 else 0 end = p.partition_number   

Both tables are using different partion functions and schemes. They only share filegroups. Does it matter in this case?

The error pertains to having a Columnstore index on your table. There is a limitation when you have a columnstore index on the table and it is that you cannot have data in the partition that you are splitting (1 of them has to be empty and the other can have data).

So basically if you have a Columnstore index and you are trying to split a partition in the middle of a table and not on the ends of a table, you will get an error because you have to have 1 empty partition on the side of the split.

Partition 1 - data
Partition 2 - data -- want to split this one
Partition 3 - data
Partition 4 - no data

If you want to do like above, you can if you do NOT have a Columnstore index on your table. If you do, you will get the error message you have above. (This will work if you drop the columnstore and split and then put the columnstore back on)

Partition 1 - data
Partition 2 - data
Partition 3 - data -- want to split
Partition 4 - no data

If you want to do this, then you can even with a Columnstore, because there is one empty partition on the side of the split. (this will work with or without Columnstore indexes on the table)

Hope that makes sense.

Just to be clear, columnstore index had been dropped and the last partition had been switched out. so its empty.

The table is now a heap table (partitioned).

Partition 1 - data
Partition 2 - data 2017-10-01
Partition 3 - data 2017-11-01
Partition 4 - no data (empty - switched out) 2017-12-01
New Partition - Failed adding new 2018-01-01

Here are the results. As you can see partition 28 is empty, trying to add another partition after 28 is failing.

partition_number    row_count
1   0
2   764365
3   1947913
4   9557952
5   27624757
6   50458571
7   44443201
8   61740713
9   87396953
10  58255125
11  80414892
12  44353571
13  41169213
14  37814501
15  47813687
16  63350026
17  48993126
18  50130871
19  63185287
20  60620138
21  53509630
22  57301035
23  58564594
24  60276995
25  54829324
26  50389878
27  59874418
28  0


boundary_id rows_per_boundary
1   6
2   6
3   6
4   6
5   6
6   6
7   6
8   6
9   6
10  6
11  6
12  6
13  6
14  6
15  6
16  6
17  6
18  6
19  6
20  6
21  6
22  6
23  6
24  6
25  6
26  6
27  6
28  1
29  1
30  1
31  1
32  1
33  1
34  1
35  1
36  1
37  1
38  1
39  1
40  1
41  1
42  1
43  1
44  1
45  1
46  1
47  1
48  1
49  1
50  1
51  1
52  1
53  1
54  1
55  1
56  1
57  1
58  1
59  1
60  1
61  1
62  1
63  1