What happens when you change a heap into a clustered table in SQL Server?

Question

As I understand, a heap is an unordered object. When you insert a record, SQL Server use IAM page to get the pages that belong to this heap, and use PFS page to find a particular one which has enough space to accommodate this record and insert into it.

When you create a clustered index on it, it becomes a clustered table and the clustered index itself becomes the table. But as the clustered index and the original heap are two different structures, does SQL Server create a new structure (the clustered index) and moves everything from the help to the new structure and then drops the heap?

There are a lot of stuffs that can be defined on a table, like triggers, constrains, permissions etc. If my assumption is true, that means SQL Server also moves all these stuff to the new structure. I didn't find any related information in the documentation. Is my understanding correct?

Answer 1

Yes, when you create a clustered index on a heap the rows are all sorted moved to the new clustered index. Any non-clustered indexes are rebuilt with the new clustered index key as the row locator.

It's the same table, though, so triggers, constraints, etc don't have to change.

The reverse is not true, however. When you drop a clustered index on a table, the leaf-level pages of the clustered index are left in-place and they become the new heap. Nonclustered indexes are still rebuilt as the row locator switches from the CI key to the rowid (file:page:slot).

Answer 2

There are a lot of stuffs that can be defined on a table, like triggers, constrains, permissions etc. If my assumption is true, that means SQL Server also moves all these stuff to the new structure. I didn't find any related information in the documentation. Is my understanding correct?

Correct, everything is moved to the new structure

object_id of the table stays the same, and permissions are also "moved" automatically (in fact they are not moved but stay in place since object_id stays the same)

Use below T-SQL to test:

-- use any db to test   
use TestDB


-- create a Heap   
create table TestHeap (ID int identity, tName varchar(10))  

insert into TestHeap (tName)   
values ('aaaaa'),('bbbbb'),('ccccc')


-- check the object_id and type_desc   
select * from sys.indexes where [object_id] = object_id('TestHeap')     


-- create a test user   
create user TestHeapUser without login

grant select on TestHeap to TestHeapUser


-- select from table as test user    
execute as user = 'TestHeapUser'
select * from TestHeap
revert


-- let's turn the table into clustered index   
create clustered index CIX_ID on TestHeap (ID)


-- see if object_id changed (it is not), and type_desc turned into "CLUSTERED"   
select * from sys.indexes where [object_id] = object_id('TestHeap')     


-- test permissions again  (object_id has not changed so Test user still should have permissions!)   
execute as user = 'TestHeapUser'
select * from TestHeap
revert


-- lets turn table into Heap again   
drop  index CIX_ID on TestHeap

-- test permissions again  (object_id has not changed so Test user still should have permissions!)   
execute as user = 'TestHeapUser'
select * from TestHeap
revert