currently, I created 1 partition table with 1 file group and 4 data files (4 quarter/year).
Data have been inserted successfully. And everything is right when I check records belong to which partition by query:
Select i.name, i.index_id, p.partition_number, p.rows From sys.partitions As p Join sys.indexes As i On p.object_id = i.object_id And p.index_id = i.index_id Where p.object_id = object_id('DiASCII_850_PO') Order By i.index_id, p.partition_number;
Next, I add one more data file (named 'filegroup_20011_q1.ndf') to this file group, it created success with size 1mb. Next, I split the partition function by range '2011-01-01'. It's still fine.
So now, I have 5 partitions with the fifth partition stores data from date '2011-01-01'. And when I queried data with Execution Plan, it told me that data come from the fifth partition.
But when I check the size of data file 'filegroup_20011_q1.ndf', size is still 1mb.
So, where is the data from date '2011-01-01'? which data file is storing them?
thank you very much.