I have create the following table:
CREATE TABLE dbo.TestStructure ( id INT NOT NULL, filler1 CHAR(36) NOT NULL, filler2 CHAR(216) NOT NULL );
and then created a clustered index:
CREATE CLUSTERED INDEX idx_cl_id ON dbo.TestStructure(id);
Next I populated it with 30 rows each size is 256 byte (based on table declaration):
DECLARE @i AS int = 0; WHILE @i < 30 BEGIN SET @i = @i + 1; INSERT INTO dbo.TestStructure (id, filler1, filler2) VALUES (@i, 'a', 'b'); END;
Now based on information I read in "Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 (Itzik Ben-Gan)" book:
SQL Server internally organizes data in a data file in pages. A page is an 8 KB unit and belongs to a single object; for example, to a table or an index. A page is the smallest unit of reading and writing. Pages are further organized into extents. An extent consists of eight consecutive pages. Pages from an extent can belong to a single object or to multiple objects. If the pages belong to multiple objects, then the extent is called a mixed extent; if the pages belong to a single object, then the extent is called a uniform extent. SQL Server stores the first eight pages of an object in mixed extents. When an object exceeds eight pages, SQL Server allocates additional uniform extents for this object. With this organization, small objects waste less space and big objects are less fragmented.
So here I have the first mixed extent 8KB page, populated with 7680 bytes (I have inserted 30 times 256 byte size row, so 30 * 256 = 7680), to check the size I have run size check proc - it returns the following result
index_type_desc: CLUSTERED INDEX index_depth: 1 index_level: 0 page_count: 1 record_count: 30 avg_page_space_used_in_percent: 98.1961947121324 name : TestStructure rows : 30 reserved : 16 KB data : 8 KB index_size : 8 KB unused : 0 KB
So 16 KB are reserved for the table, first 8 KB page is for Root IAM page, the second one is for leaf data storage page which is 8KB with occupation of ~ 7.5 KB, now when I insert a new row with 256 Byte:
INSERT INTO dbo.TestStructure (id, filler1, filler2) VALUES (1, 'a', 'b');
it is not stored in the same page although it have a space of 256 byte (7680 b + 256 = 7936 which is still smaller than 8KB), a new data page is created, but that new row could be fit on the same old page, why does SQL Server create a new page when it could save space and searching time buy inserting it in the existing page?
Note: the same thing is happening in heap index.