Why SQL Server splits the page? I am changing an integer value.

If someone asks “Why SQL Server splits pages”, I know that all of you say “If an insert or update requires more space, SQL Server splits the page and moves half of the records to the new page”. It is true. This makes the table externally fragmented too. However, can there be a scenario which an change of integer value splits the page? There can be, and here it is.

Before that, if you need to understand page-split and fragmentation, read this: http://dinesql.blogspot.com/2013/08/understanding-index-fragmentation-ss.html.

We will start with a simple code. Have a look on below code;

USE tempdb

GO

-- creating test table

CREATE TABLE dbo.TestTable

(

    Id int PRIMARY KEY

    , Value char(4000) NOT NULL

)

GO

-- inserting 2 records

INSERT INTO dbo.TestTable

    VALUES 

    (2, replicate('a', 4000)), (5, replicate('b', 4000))

-- check records with page numbers

SELECT Id, Value, ph.*

FROM dbo.TestTable

CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph

I have used two functions for getting page numbers related to inserted records. For more info on these two functions, read this: http://dinesql.blogspot.com/2013/12/finding-pages-or-physical-location-of.html

Here is the result of the SELECT;

As you with the result, both records are in same page, and becuase of the size of records, page is almost full. Let’s do a modification to Value column first and see whether it causes a page split.

UPDATE dbo.TestTable

    SET Value = replicate('e', 4000)

WHERE id = 2

SELECT Id, Value, ph.*

FROM dbo.TestTable

CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph

Result is same. Getting 4000 bytes as a changed value has not required additional space and page split has not occurred. Now let’s make a change on Id column.

UPDATE dbo.TestTable

    SET Id = 3

WHERE id = 2

SELECT Id, Value, ph.*

FROM dbo.TestTable

CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph

Page split has occurred. Why SQL Server needs space and splits the page ONLY IF IT IS AN INTEGER COLUMN? This is what you have understand. Note that this is not just an integer column. It is the PRIMARY KEY; The clustered key. What happened was, SQL Server updated the Value column as an in-place update. It does not require additional space. However SQL Server does not perform in-place update for key columns. It performs delete-plus-insert operation for key column updates. It requires space for the INSERT. That is the reason for the page split. Understand that this behavior is not only for integer, it is for all key columns.

I will make another post on in-place update and delete-plus-insert operation.