DBCC SHRINKDATABASE, Will it cause Fragmentation in Index Practical Example Series 3

Create database TestShrink
on Primary
(
    Name = '    ',
    filename = 'D:\TestShrink_Data.MDF'
)
log on
(
    Name = 'TestShrink_Log',
    FileName = 'D:\TestShrink_Log.ldf'
)
go

Use TestShrink
go

Create table HugeTable (A Char(8000))
go

insert into HugeTable values('a')
go

Insert into HugeTable
Select * from HugeTable
go 18 --This is kewl because it runs this batch for 18 times filling up table as fast as it could :). I like this one a lot.

Create table SmallTable ( C char(900))
go

insert into SmallTable values('A')
go

Before Populating:

declare @dbid int, @objid int
select @dbid = DB_ID('TestShrink'),@objid = OBJECT_ID('SmallTable')
dbcc ind(@dbid,@objid,2)
go

 

Populating Data:

insert into SmallTable
select * from SmallTable
go 12

declare @dbid int, @objid int
select @dbid = DB_ID('TestShrink'),@objid = OBJECT_ID('SmallTable')
dbcc ind(@dbid,@objid,2)
go

Get Fragmentation Information:

select * from sys.dm_db_index_physical_stats(9,2121058592,2,1,DEFAULT)

Reindexing table to remove fragmentation:

dbcc dbreindex('SmallTable')

declare @dbid int, @objid int
select @dbid = DB_ID('TestShrink'),@objid = OBJECT_ID('SmallTable')
dbcc ind(@dbid,@objid,2)
go

select * from sys.dm_db_index_physical_stats(9,2121058592,2,1,DEFAULT)

Truncate table HugeTable (To create empty space at the end of the file)

sp_spaceused

DBCC SHRINKDATABASE('TestShrink',50,NOTRUNCATE)

Rechecking Fragmentation:

declare @dbid int, @objid int
select @dbid = DB_ID('TestShrink'),@objid = OBJECT_ID('SmallTable')
dbcc ind(@dbid,@objid,2)

select * from sys.dm_db_index_physical_stats(9,2121058592,2,1,DEFAULT)

Too tired taking screen shots :) will summarize why Shrink Database will impact fragmentation.. For any question please fee free to send mail to gurucb@hotmail.com.