USE master;
GO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'IndexTest')
	DROP DATABASE [IndexTest];
CREATE DATABASE [IndexTest];
USE [IndexTest]


CREATE TABLE [Table1] (id INT IDENTITY,  name CHAR (6000));
set nocount on;
GO
INSERT INTO [Table1] (name) select cast(NEWID() as varchar(100));
GO 10000


CREATE TABLE [Table2] (id INT IDENTITY,  name varchar(6000));
CREATE CLUSTERED INDEX [Table2Cluster] ON [Table2] ([id] ASC);
GO
INSERT INTO [Table2] (name) select cast(NEWID() as varchar(100));
GO 10000




DBCC SHOWCONTIG('[Table2]');
SELECT CAST(CONVERT(decimal(12,2),ROUND(t1.size/128.000,2)) as varchar(10)) as [FILESIZEINMB] ,
       CAST(CONVERT(decimal(12,2),ROUND(fileproperty(t1.name,'SpaceUsed')/128.000,2)) as varchar(10)) as [SPACEUSEDINMB],
       CAST(CONVERT(decimal(12,2),ROUND((t1.size-fileproperty(t1.name,'SpaceUsed'))/128.000,2)) as varchar(10)) as [FREESPACEINMB],
       CAST(t1.name as varchar(16)) as [name]
FROM dbo.sysfiles t1;

PRINT 'Dropping table'
DROP TABLE [Table1];

PRINT 'WAITFOR 10 seconds'
WAITFOR DELAY '00:00:10';  --sleep for 10 seconds to allow the deallocated space to be freed up

DBCC SHOWCONTIG('[Table2]');
SELECT CAST(CONVERT(decimal(12,2),ROUND(t1.size/128.000,2)) as varchar(10)) as [FILESIZEINMB] ,
       CAST(CONVERT(decimal(12,2),ROUND(fileproperty(t1.name,'SpaceUsed')/128.000,2)) as varchar(10)) as [SPACEUSEDINMB],
       CAST(CONVERT(decimal(12,2),ROUND((t1.size-fileproperty(t1.name,'SpaceUsed'))/128.000,2)) as varchar(10)) as [FREESPACEINMB],
       CAST(t1.name as varchar(16)) as [name]
FROM dbo.sysfiles t1;


PRINT 'Shrinking Database'
DBCC SHRINKDATABASE ('IndexTest', 5);

DBCC SHOWCONTIG('[Table2]');
SELECT CAST(CONVERT(decimal(12,2),ROUND(t1.size/128.000,2)) as varchar(10)) as [FILESIZEINMB] ,
       CAST(CONVERT(decimal(12,2),ROUND(fileproperty(t1.name,'SpaceUsed')/128.000,2)) as varchar(10)) as [SPACEUSEDINMB],
       CAST(CONVERT(decimal(12,2),ROUND((t1.size-fileproperty(t1.name,'SpaceUsed'))/128.000,2)) as varchar(10)) as [FREESPACEINMB],
       CAST(t1.name as varchar(16)) as [name]
FROM dbo.sysfiles t1;



PRINT 'REORGANIZE'
ALTER INDEX Table2Cluster ON [IndexTest].[dbo].[Table2] REORGANIZE;


DBCC SHOWCONTIG('[Table2]');
SELECT CAST(CONVERT(decimal(12,2),ROUND(t1.size/128.000,2)) as varchar(10)) as [FILESIZEINMB] ,
       CAST(CONVERT(decimal(12,2),ROUND(fileproperty(t1.name,'SpaceUsed')/128.000,2)) as varchar(10)) as [SPACEUSEDINMB],
       CAST(CONVERT(decimal(12,2),ROUND((t1.size-fileproperty(t1.name,'SpaceUsed'))/128.000,2)) as varchar(10)) as [FREESPACEINMB],
       CAST(t1.name as varchar(16)) as [name]
FROM dbo.sysfiles t1;


PRINT 'REBUILD'
ALTER INDEX All ON [IndexTest].[dbo].[Table2] REBUILD WITH (FILLFACTOR = 95);


DBCC SHOWCONTIG('[Table2]');
SELECT CAST(CONVERT(decimal(12,2),ROUND(t1.size/128.000,2)) as varchar(10)) as [FILESIZEINMB] ,
       CAST(CONVERT(decimal(12,2),ROUND(fileproperty(t1.name,'SpaceUsed')/128.000,2)) as varchar(10)) as [SPACEUSEDINMB],
       CAST(CONVERT(decimal(12,2),ROUND((t1.size-fileproperty(t1.name,'SpaceUsed'))/128.000,2)) as varchar(10)) as [FREESPACEINMB],
       CAST(t1.name as varchar(16)) as [name]
FROM dbo.sysfiles t1;