Scripts from Index Defrag DBA Minute

Here's the scripts from last week's DBA Minute on Index Fragmentation.

set nocount on;
use WideWorldImporters;
go

if OBJECT_id('dbo.TblHeap') is not null drop table dbo.TblHeap;
if OBJECT_id('dbo.TblHeapWithPK') is not null drop table dbo.TblHeapWithPK;
go

select sysdatetime() as [StartAt];
go

CREATE TABLE dbo.TblHeap (
TblHeapid BIGINT IDENTITY NOT NULL,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why
);

CREATE TABLE dbo.TblHeapWithPK(
TblHeapid BIGINT IDENTITY primary key,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why
);
GO

select sysdatetime() as [TablesBuilt];
go

--Insert 10,000 rows.
DECLARE @i INT = 1;

WHILE @i <= 10000
BEGIN
 INSERT dbo.TblHeap (col1) SELECT REPLICATE('ASDF',200) ;
 INSERT dbo.TblHeapWithPK (col1) SELECT REPLICATE('ASDF',200) ;
 SET @i = @i + 1;
END
GO

select sysdatetime() as [TblHeap built];
go

-- what's in the tables?
select top 10 * from dbo.TblHeap;
select top 10 * from dbo.TblHeapWithPK;
go

-- frag level?
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

-- force page splits
update dbo.TblHeap set col1 = REPLICATE('x',1024);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',1024);
update dbo.TblHeap set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeap set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
go

delete from dbo.TblHeap where (TblHeapid % 5 ) <> 0;
delete from dbo.TblHeapWithPK where (TblHeapid % 5 ) <> 0;
go

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

ALTER INDEX ALL ON dbo.TblHeap REORGANIZE --WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
ALTER INDEX ALL ON dbo.TblHeapwithPK REORGANIZE --WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go