UG81CzaV

1use db_workspace
2go
3
4--create a table with an id and some string , so that 2 records will be stored in a page
5drop table if exists dbo.pageSplit;
6create table dbo.pageSplit
7(
8	id int not null,
9	myChar char(3500) not null,
10	constraint pk_pageSplit_id PRIMARY KEY CLUSTERED(id)
11)
12go
13
14--see information on the table with index_physical_stats
15select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
16	avg_page_space_used_in_percent 
17	--,  * 
18from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
19where index_level=0
20
21
22-- insert one record with id =3 , in a transaction , and see the amount of log information is generated
23begin transaction
24	insert into dbo.pageSplit(id,myChar)
25	values(3,REPLICATE('3',3500));
26
27	select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID('db_workspace')
28	--3796
29commit transaction
30
31--see information on the table after value 3 inserted
32select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
33	avg_page_space_used_in_percent 
34	--,  * 
35from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
36where index_level=0
37
38
39
40
41/*****  step 2 : insert value 2 , should be located before value 3 *****/
42
43-- insert one record with id =2 , in a transaction , and see the amount of log information is generated
44begin transaction
45	insert into dbo.pageSplit(id,myChar)
46	values(2,REPLICATE('2',3500));
47
48	select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID('db_workspace')
49	--3756
50commit transaction
51
52--see information on the table after value 2 inserted
53select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
54	avg_page_space_used_in_percent 
55	--,  * 
56from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
57where index_level=0
58
59
60
61
62/*****  step 3 : insert value 1 , should be located before value 2 *****/
63-- insert one record with id =1 , in a transaction , and see the amount of log information is generated
64begin transaction
65	insert into dbo.pageSplit(id,myChar)
66	values(1,REPLICATE('1',3500));
67
68	select database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_id = DB_ID('db_workspace')
69	--5784
70commit transaction
71
72--see information on the table after value 2 inserted
73select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
74	avg_page_space_used_in_percent 
75	--,  * 
76from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
77where index_level=0
78
79--see information on the table after value 1 inserted
80select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
81	avg_page_space_used_in_percent 
82	--,  * 
83from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),OBJECT_ID('dbo.pageSplit'),1,NULL,'DETAILED')
84where index_level=0