3

I believe SQL Server would create a new column under the hood and copy the original data to the newly created column. And the original space for the old column cannot be reclaimed before the table is rebuilt.

But I can't come up with a general picture in my head about what exactly is going on.

Say I have a simple table defintion:

CREATE TABLE ExampleTbl
(
 ID INT
 ,Bool BIT
)

And a certain page is completely filled with ExampleTbl's data rows. What happens if I change Bool column data type to int?

Since the page is full, does it mean the the newly created column will have to be stored in some other data page? I originally thought it should be in the same page because SQL Server will always try to use in-row allocation when the data row fits into a page(less then 8000 bytes).

But then it may involve a lot of moving around of the data rows to allocate space so the new column can be inserted into the data page, and some rows will have to be popped out the page and stored in another one, if SQL Server needs to stick to in-row allocation. But this behavior is obviously detrimental to system performance.

So exactly what happens behind the scenes when I change a column data type to one with a wider domain of values?

Thanks!

3

Changing a column data type to a wider type will require rows to be moved to other pages once a page is full. This behavior is similar to DML updates of variable length columns to a wider value. This can be observed with this script that uses the undocumented sys.dm_db_database_page_allocations TVF available in SQL Server 2012 and later:

CREATE TABLE ExampleTbl
(
  ID INT
 ,Bool BIT
);
WITH 
    t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1000 AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
INSERT INTO ExampleTbl WITH(TABLOCKX)
SELECT num, 1
FROM t1000
WHERE num <= 578;

--single page
DECLARE 
      @database_id int = DB_ID()
    , @object_id int = OBJECT_ID(N'dbo.ExampleTbl')
    , @allocated_page_file_id int
    , @allocated_page_page_id int;
SELECT *
FROM sys.dm_db_database_page_allocations(@database_id, @object_id, 0, 1, 'DETAILED')
WHERE 
    page_type_desc = 'DATA_PAGE';
GO

ALTER TABLE dbo.ExampleTbl
    ALTER COLUMN Bool INT;
GO

--3 pages, with 2 100% full and last 50% full
DECLARE 
      @database_id int = DB_ID()
    , @object_id int = OBJECT_ID(N'dbo.ExampleTbl')
    , @allocated_page_file_id int
    , @allocated_page_page_id int;
SELECT *
FROM sys.dm_db_database_page_allocations(@database_id, @object_id, 0, 1, 'DETAILED')
WHERE 
    page_type_desc = 'DATA_PAGE';
GO

Heaps are particularly nasty when DML/DDL increases row length because the original row is retained with a forwarding pointer. Consequently, more space will be required that the equivalent change against a table with clustered index.

  • @Thanks Dan. Need some time to understand the script. But in general SQL Server will have to 'make room' for the new column so it would fit into the data row storage structure, even if it means a lot of moving of data rows? Also what does the forwarding pointer refer to? Thanks again – John Smith Sr. Jul 17 '17 at 3:40
  • 1
    Forwarding pointer means that prior to it appearance you had your row resided in page 10 (for example), then the change was made to this record so it dose not fit in this page anymore, so the server moves this new-sized row to another page 100(example) and on the page 10 instead of this row it puts the forwarding pointer that points to the page 100. And now every time it should retrieve this row it first goes to the page 10 and then follows the pointer and finds the row on the page 100 – sepupic Jul 17 '17 at 9:54
  • This is made for 1) not creating forwarding "chains" when the row change its size more than 1 time 2) for not updating all non-clustered indexes that point to that row – sepupic Jul 17 '17 at 9:56
  • 1
    @JohnSmithSr, yes SQL Server will be able to complete the schema change even when data must be moved to accommodate the larger row size, The same change with a clustered primary key on ID results in 2 pages used rather than 3 because forwarding pointers are not needed with clustered indexes. – Dan Guzman Jul 17 '17 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.