-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- 
-- For more scripts and sample code, check out 
-- 	http://www.SQLSkills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Use a "TestDB" Database with at Least 3-8 MB Free Space.
-- USE TestFGDB
-- go
SET STATISTICS IO OFF
-- Turn Graphical Showplan ON (Ctrl+K)
SET NOCOUNT ON
go
IF OBJECTPROPERTY(object_id('dbo.DemoTableHeap'), 'IsUserTable') = 1
	DROP TABLE dbo.DemoTableHeap
go
CREATE TABLE dbo.DemoTableHeap
(
	col1	int	 		identity(100,10),
	col2	datetime 	CONSTRAINT DemoTableHeapCol2Default 
							DEFAULT current_timestamp,
	col3	datetime 	CONSTRAINT DemoTableHeapCol3Default 
							DEFAULT getdate(),
	col4	char(30) 	CONSTRAINT DemoTableHeapCol4Default 
							DEFAULT suser_name(),
	col5	char(30)  	CONSTRAINT DemoTableHeapCol5Default 
							DEFAULT user_name(),
	col6	char(100)  	CONSTRAINT DemoTableHeapCol6Default 
							DEFAULT 'Long text value of "Now is the time for all good men to come to the aid of their country."',
	col7	varchar(200)  	CONSTRAINT DemoTableHeapCol7Default 
							DEFAULT 'short value'
)
go
DECLARE @EndTime	datetime
SELECT @EndTime = dateadd(ss, 60, getdate())
WHILE getdate() <= @EndTime
BEGIN
	INSERT dbo.DemoTableHeap DEFAULT VALUES
END
EXEC sp_spaceused 'dbo.DemoTableHeap', true
go
-- Record the RowCount = 190395           
-- Figure out the number of pages by dividing "data" by 8
-- SELECT 40088/8 = 5011
-- This SHOULD MATCH the I/Os shown by SELECT COUNT(*)

SET STATISTICS IO ON
go
SELECT COUNT(*) AS 'RowCount' 
FROM dbo.DemoTableHeap
go

-- Modify roughly 15% - keep track of the rows affected
UPDATE dbo.DemotableHeap
	SET col7 = 'This is a test to create some fragmentation. The previously small column is now filled to capacity. This is a test to create some fragmentation. The previously small column is now filled to capacity.'
	where col1 % 7 = 0
go
select @@rowcount

-- Keep track of Rows Affected Here = 27199

-- Check the space again and calculate # of Pages
SET STATISTICS IO OFF
go
EXEC sp_spaceused 'dbo.DemoTableHeap', true
go

-- Figure out the number of pages by dividing "data" by 8
-- SELECT 44952/8 = 5619  -- 17787
-- This SHOULD MATCH the I/Os shown by SELECT COUNT(*)

-- Does this match the I/Os shown by SELECT COUNT(*)
-- ??
SET STATISTICS IO ON
go
SELECT COUNT(*) AS 'RowCount' FROM dbo.DemoTableHeap
go

-- What does is equal?
-- Pages + Rows Modified that caused Forwarding Pointers
-- Do the math Total I/Os - Pages in Table = ???
-- SELECT 17787 - 5619 = 2523
-- So what does that number represent?

-- Forwarding Pointers!
-- A Forwarding Pointer is ALWAYS honored. This is a good thing for most operations
-- however, it is seemingly wrong for a table scan.


-- To confirm (this command ONLY works in SQL Server 2005)
SELECT 'SAMPLED' AS Type, * 
	FROM sys.dm_db_index_physical_stats(db_id(), object_id('TestFGDB.dbo.DemoTableHeap'), DEFAULT, DEFAULT, 'SAMPLED')
UNION ALL
SELECT 'LIMITED', * 
	FROM sys.dm_db_index_physical_stats(db_id(), object_id('TestFGDB.dbo.DemoTableHeap'), DEFAULT, DEFAULT, 'LIMITED')
	-- Limited doesn't return
UNION ALL
SELECT 'DETAILED', * 
	FROM sys.dm_db_index_physical_stats(db_id(), object_id('TestFGDB.dbo.DemoTableHeap'), DEFAULT, DEFAULT, 'DETAILED')
go

SELECT p.* 
FROM sys.partitions AS p
WHERE [object_id] = object_id('DemoTableHeap')