zK3L59dF

1use db_workspace
2go
3
4--create a table with an id and some string , so that 6 records will be stored in a page
5drop table if exists dbo.fillFact_Full;
6create table dbo.fillFact_Full
7(
8	id int not null,
9	myChar char(985) not null,
10	myVarChar varchar(500) null	
11)
12GO
13CREATE UNIQUE CLUSTERED INDEX idx_fillFact_Full_id ON dbo.fillFact_Full
14	(id);
15GO
16drop table if exists dbo.fillFact_60;
17create table dbo.fillFact_60
18(
19	id int not null,
20	myChar char(985) not null,
21	myVarChar varchar(500) null	
22)
23GO
24CREATE UNIQUE CLUSTERED INDEX idx_fillFact_60_id ON dbo.fillFact_60
25	(id);
26GO
27
28
29-- insert records
30insert into dbo.fillFact_Full(id,myChar,myVarChar)
31select number * 3 , LEFT(REPLICATE(CAST(number AS VARCHAR(50)),985),985),NULL
32from master..spt_values as v
33where v.type ='P'
34and v.number < 20
35and v.number > 0 ;
36
37insert into dbo.fillFact_60(id,myChar,myVarChar)
38select number * 3 , LEFT(REPLICATE(CAST(number AS VARCHAR(50)),985),985),NULL
39from master..spt_values as v
40where v.type ='P'
41and v.number < 20
42and v.number > 0 ;
43
44select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
45	avg_page_space_used_in_percent 
46	--,  * 
47from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),NULL,NULL,NULL,'DETAILED')as ips
48where index_level=0
49	and  ( 
50			ips.object_id = object_id('dbo.fillFact_60') 
51			or 
52			ips.object_id = object_id('dbo.fillFact_FULL') 
53		 )
54
55/****end of setup ***/
56
57
58
59/* rebuild index and set the new fill factor */
60ALTER INDEX  idx_fillFact_60_id ON dbo.fillFact_60
61	REBUILD 
62	WITH (FILLFACTOR =60);
63GO
64ALTER INDEX  idx_fillFact_FULL_id ON dbo.fillFact_FULL
65	REBUILD 
66	WITH (FILLFACTOR =100);
67GO
68select page_count , avg_fragmentation_in_percent , record_count , avg_record_size_in_bytes ,
69	avg_page_space_used_in_percent 
70	--,  * 
71from sys.dm_db_index_physical_stats(DB_ID('db_workspace'),NULL,NULL,NULL,'DETAILED')as ips
72where index_level=0
73	and  ( 
74			ips.object_id = object_id('dbo.fillFact_60') 
75			or 
76			ips.object_id = object_id('dbo.fillFact_FULL') 
77		 )
78
79-- space used for it
80Exec sp_spaceused 'dbo.fillFact_FULL'
81Exec sp_spaceused 'dbo.fillFact_60'
82
83-- count pages when doing a select 
84set statistics io on
85	select * from dbo.fillFact_Full
86	select * from dbo.fillFact_60
87set statistics io off
88
89--see the location of each record:
90select flc.*,f.* from dbo.fillFact_Full as f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as flc
91select flc.*,f.* from dbo.fillFact_60 as f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)as flc
92
93--update some records with id = 9 ,12
94--for id = 9,12 , page = 392
95update dbo.fillFact_Full
96set myVarChar = LEFT(REPLICATE('update:' + CAST(id AS VARCHAR(50)) ,500),500)
97where id  IN( 9 ,12)
98
99--for id = 9,12 , page = 400
100update dbo.fillFact_60
101set myVarChar = LEFT(REPLICATE('update:'  + CAST(id AS VARCHAR(50)) ,500),500)
102where id  IN( 9 ,12)
103
104--see the location of each record:
105select flc.*,f.* from dbo.fillFact_Full as f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as flc
106select flc.*,f.* from dbo.fillFact_60 as f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)as flc
107
108
109
110-- now insert new record in page 424 , id 27 -> 48
111insert into dbo.fillFact_Full(id,myChar,myVarChar)
112values (35,LEFT(REPLICATE('update:' + CAST(35 AS VARCHAR(50)) ,985),985),NULL)
113	,(37,LEFT(REPLICATE('update:' + CAST(37 AS VARCHAR(50)) ,985),985),NULL)
114
115--page 409 id = 33 -> 45
116insert into dbo.fillFact_60(id,myChar,myVarChar)
117values (35,LEFT(REPLICATE('update:' + CAST(35 AS VARCHAR(50)) ,985),985),NULL)
118	,(37,LEFT(REPLICATE('update:' + CAST(37 AS VARCHAR(50)) ,985),985),NULL)
119
120select flc.*,f.* from dbo.fillFact_Full as f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as flc
121select flc.*,f.* from dbo.fillFact_60 as f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)as flc