use db_workspace
go

--create a table with an id and some string , so that 2 records will be stored in a page
drop table if exists dbo.pageSplit;
create table dbo.pageSplit
(
	id int not null,
	myChar char(3500) not null,
	constraint pk_pageSplit_id PRIMARY KEY CLUSTERED(id)
)
go

--see information on the table with index_physical_stats
select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
	avg_page_space_used_in_percent
	--,  *
from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
where index_level=0


-- insert one record with id =3 , in a transaction , and see the amount of log information is generated
begin transaction
	insert into dbo.pageSplit(id,myChar)
	values(3,REPLICATE('3',3500));

	select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID('db_workspace')
	--3796
commit transaction

--see information on the table after value 3 inserted
select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
	avg_page_space_used_in_percent
	--,  *
from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
where index_level=0




/*****  step 2 : insert value 2 , should be located before value 3 *****/

-- insert one record with id =2 , in a transaction , and see the amount of log information is generated
begin transaction
	insert into dbo.pageSplit(id,myChar)
	values(2,REPLICATE('2',3500));

	select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID('db_workspace')
	--3756
commit transaction

--see information on the table after value 2 inserted
select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
	avg_page_space_used_in_percent
	--,  *
from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
where index_level=0




/*****  step 3 : insert value 1 , should be located before value 2 *****/
-- insert one record with id =1 , in a transaction , and see the amount of log information is generated
begin transaction
	insert into dbo.pageSplit(id,myChar)
	values(1,REPLICATE('1',3500));

	select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID('db_workspace')
	--5784
commit transaction

--see information on the table after value 2 inserted
select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
	avg_page_space_used_in_percent
	--,  *
from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
where index_level=0

--see information on the table after value 1 inserted
select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
	avg_page_space_used_in_percent
	--,  *
from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
where index_level=0