USE [AdventureWorks2014]
GO
/********** Part -1 - Create Tables ***********************/
--Drop the table [DBO].[Address_GUID] if already exists
IF EXISTS ( SELECT	1 FROM	SYSOBJECTS WHERE	ID = OBJECT_ID(N'[DBO].[Address_GUID]') AND [TYPE] = 'U')
BEGIN
    DROP TABLE [DBO].[Address_GUID];
END
GO
-- Create the table [DBO].[Address_GUID]
-- Crearting a UNIQUE CLUSTERED index [CL_Address_GUID_AddressID] on [AddressID]
CREATE TABLE [DBO].[Address_GUID](
	[AddressID]			[UNIQUEIDENTIFIER] NOT NULL,
	[AddressLine1]		[NVARCHAR](60) NOT NULL,
	[City]				[NVARCHAR](30) NOT NULL,
	[StateProvinceID]	[INT] NOT NULL,
	[ModifiedDate]		[DATETIME] NOT NULL DEFAULT(GETDATE()),
 CONSTRAINT [CL_Address_GUID_AddressID] UNIQUE CLUSTERED 
(
	[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--Drop the table [DBO].[Address_GUID_SEQ] if already exists
IF EXISTS ( SELECT	1 FROM	SYSOBJECTS WHERE	ID = OBJECT_ID(N'[DBO].[Address_GUID_SEQ]') AND [TYPE] = 'U')
BEGIN
    DROP TABLE [DBO].[Address_GUID_SEQ];
END
GO
-- Create the table [DBO].[Address_GUID]
-- Crearting a UNIQUE CLUSTERED index [CL_Address_GUID_AddressID] on [AddressID]
-- Generating the UNIQUE ID using NEWSEQUENTIALID()
CREATE TABLE [DBO].[Address_GUID_SEQ](
	[AddressID]			[UNIQUEIDENTIFIER] NOT NULL DEFAULT(NEWSEQUENTIALID()),
	[AddressLine1]		[NVARCHAR](60) NOT NULL,
	[City]				[NVARCHAR](30) NOT NULL,
	[StateProvinceID]	[INT] NOT NULL,
	[ModifiedDate]		[DATETIME] NOT NULL DEFAULT(GETDATE()),
 CONSTRAINT [CL_Address_GUID_SEQ_AddressID] UNIQUE CLUSTERED 
(
	[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*************************** Part -2 - INSERT DATA  *****************************/
SET NOCOUNT ON;
GO
	-- Insert 10000 rows into [Address_GUID] with random GUD
	INSERT INTO [Address_GUID] (AddressID,AddressLine1,City,StateProvinceID)
	VALUES (NEWID(),'9539 Glenside Dr','Bothell',79)
	GO 10000
GO
	-- Insert 10000 rows into [Address_GUID_SEQ] with sequential GUID
	INSERT INTO [Address_GUID_SEQ] (AddressLine1,City,StateProvinceID)
	VALUES ('9539 Glenside Dr','Bothell',79)
	GO 10000
GO

/******************* Part -3 - Index Fragmentation ***********************/
SELECT	dbschemas.[name]						AS 'Schema',
		dbtables.[name]							AS 'Table',
		dbindexes.[name]						AS 'Index',
		indexstats.avg_fragmentation_in_percent	AS 'Fragmentation_%',
		indexstats.page_count					AS 'Page_Count'
FROM	SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
		INNER JOIN SYS.TABLES dbtables ON dbtables.[object_id] = indexstats.[object_id]
		INNER JOIN SYS.SCHEMAS dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
		INNER JOIN SYS.INDEXES dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
								AND indexstats.index_id = dbindexes.index_id
WHERE	[indexstats].database_id = DB_ID() AND 
		[dbindexes].[name] IN ('CL_Address_GUID_AddressID','CL_Address_GUID_SEQ_AddressID')
ORDER BY indexstats.avg_fragmentation_in_percent DESC