First I create an empty database with default values as below.
Lets execute "sp_spaceused" & see the output.
As per these, size of the database is 16MB. Log file is 8 MB & Data file is 8 MB. For our analysis what is important here is the size of the data file which is 8 MB.
As per the output above reserved space is 2386 KB. It is the sum of data, index & unused space.
That is
Reserved = data + index_size + unused
2368 KB = 960 KB + 1040 KB + 268 KB.
This can also be called as the allocated space. Then, size of the data file can be found as below
reserved space + unallocated space = datafile size
2368KB(2.31MB) + 5.69 MB = 8 MB.
Above calculations are useful to understand the data file size & usage. Now lets create a table, insert some data & see how this behaves.
Below table shows how the space is allocated based on each step.
First row in the table shows the sizes at the beginning. Then I insert 1000 records & then delete 200 records. After deleting 200 records, unused space has been increased to 1808 from 376. Then when I shrink, this space is not released but the unallocated space. Then i delete another 500 records & unused space is increase up to 4736.
Now the question is how to release this space. I rebuilt the index. After rebuilding, SQL Server arranges pages & released the unused space.
I hope this explanation clarifies on how the space in the data file behaves when data is deleted.
