Data Architecture, Database Design

Intelligent Index Recommendations from Missing Index DMVs & Existing Indexes for SQL Server

When a query is executed in SQL Server, the query optimizer attempts to generate an optimal query execution plan. In the process of deciding on the plan, the query processor also determines what it considers to be any “missing index”.

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a sub-optimal query plan, but still stores information about these “missing indexes”. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented.

Developers can learn about these missing indexes from the Database Engine Tuning Advisor (DTA), the Missing Index Dynamic Management Views (DMVs), or an execution plan displayed in SQL Server Management Studio.

One of the problems I find quite troublesome with the missing indexes as determined by the query optimizer is that no consideration is given to existing indexes and how they might be modified (dropped and re-created) in order to produce a more optimal execution plan. Furthermore, no consideration is given to previously identified, yet still missing indexes to determine if there is a missing index which is actually a consolidation of multiple missing indexes, also with consideration being given to existing indexes.

A related and similar problem exists with existing indexes. More often than not, indexes are added to a database, based upon observed query performance problems,  again with no consideration is given to existing indexes and how they might be modified in order to produce a more optimal execution plan. As a result, it is possible to have database which is over-indexed, especially from the perspective of OLTP workloads.

In small databases these issues are not so daunting, as the amount of manual effort to reconcile missing indexes against one another as well as existing indexes may be acceptable. However, in larger, more complex databases, and in environments where there is a lack of strong and practical SQL Server expertise, this exercise becomes considerably more daunting and time-consuming, and as a result may go without proper consideration and effort, often resulting in databases which are inadequately, or worse, over, indexed.

As a result, I have developed what I call “intelligent index recommendations” which address the shortcomings outlined above. This blog post will describe the process in detail, starting with leveraging the Missing Index DMVs within SQL Server in order to generate missing indexes which are ‘optimized’ when considered with all missing indexes and existing indexes for a given table.

The code that follows is a stored procedure that must be installed wherever the use of intelligent index recommendations functionality is desired. I chose to download the AdventureWorks2014 database  for demonstration and test purposes.

The procedure has a parameter, @OptimizeExistingIndexes BIT = 0. Setting this parameter to 1 will cause the procedure to not only evaluate missing indexes from the DMVs but also evaluate existing indexes.

The procedure has a parameter, @OutputScript BIT = 0. Setting this parameter to 1 will cause the procedure to output any generated DML used to apply the intelligent index recommendations to the ‘Messages’ tab in the Results window.

The procedure has a parameter, @Debug BIT = 1. Setting this parameter to 1 will cause the procedure to output debug information to the ‘Results’ tab in the results window.

The procedure has a parameter, @Verbose BIT = 0. Setting this parameter to 1 will cause the procedure to output more debug information to the ‘Results’ tab in the Results window.

Restoring a database from a backup does not populate the DMVs, so the procedure has a parameter, @Emulate BIT = 0, which is used for demonstration and test purposes. Setting this parameter to 1 will cause the procedure to emulate or ‘spoof’ missing index information.

The procedure contains comments which show the verbose debug information generated when the procedure is called with @Debug = 1, @Verbose = 1, @Emulate = 1 with the emulated missing indexes provided when the procedure is created.

As an aside, this was not an easy procedure to develop. The matching logic necessary to produce the intelligent index recommendations is quite complex, and the code is not particularly easy to understand or follow. I welcome suggestions on how to improve the readability of the code or ways in which to further extend the functionality.

Please Note: This code was last updated on November 7, 2015 and all known defects have been resolved and full support for optimization of existing indexes is now available.

Installation Requirements: This code must be installed in every database where it’s use is desired. You must create as schema named “Utility” in the database where it’s use is desired. This procedure will create a table in the same schema which is used to keep track of the executions of this procedure along with the recommendations generated with each execution.

The Transact-SQL code follows:

USE [AdventureWorks2014]
GO
/****** Object:  StoredProcedure [Utility].[IntelligentIndexRecommendation_Create]    Script Date: 6/27/2015 3:23:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE
	[Utility].[IntelligentIndexRecommendation_Create]
	(
	@FilterBySchemaName SYSNAME = NULL,
	@FilterByTableName SYSNAME = NULL,
	@OptimizeMissingIndexes BIT = 0,
	@OptimizeExistingIndexes BIT = 0,
	@RenameExistingIndexes BIT = 0,
	@PrimaryKeyNamePrefix VARCHAR(10) = 'pk',
	@IndexNamePrefix VARCHAR(10) = 'idx',
	@OutputScript BIT = 0,
	@Debug BIT = 1,
	@Verbose BIT = 1,
	@Emulate BIT = 0
	) 

AS

BEGIN

SET NOCOUNT ON

DECLARE
	@ID INT = 0,
	@DatabaseName AS SYSNAME,
	@SchemaName AS SYSNAME,
	@TableName AS VARCHAR(384),
	@IndexColumnNames AS VARCHAR(8000),
	@IncludeColumnNames AS VARCHAR(8000),
	@ExecutionID INT = 0,
	@RowNumber INT = 0,
	@SQLStatement VARCHAR(8000)
               
/*

*/

BEGIN	-- Determine the new Execution ID to be associated with this execution of this procedure

-- Does the table used to hold all of the recommendations generated by an execution of this procedure exist?
-- If so, determine the ExecutionID to be associated with the current execution of this procedure

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'Utility' AND TABLE_NAME = N'IntelligentIndexRecommendation')
BEGIN

	SELECT
		@ExecutionID = COALESCE(MAX(ExecutionID), 0) + 1
	FROM
		Utility.IntelligentIndexRecommendation
        
END

END

/*

*/
         
BEGIN	-- Create and populate the #IndexesForAnalysis temporary table with a list of distinct missing indexes as gathered by the query optimizer.

-- Create the IndexesForAnalysis temporary table

CREATE TABLE
	#IndexesForAnalysis
	(
	ID INT NOT NULL IDENTITY(1, 1),
	IndexHandle UNIQUEIDENTIFIER,
	DatabaseName SYSNAME NOT NULL,
	SchemaName SYSNAME NOT NULL,
	TableName VARCHAR(384) NOT NULL,
	IndexColumnNames NVARCHAR(MAX) NOT NULL,
	IncludeColumnNames NVARCHAR(MAX),
	ImprovementMeasure DECIMAL(20,8) NOT NULL,
	WasConsolidated BIT DEFAULT(0)
	)

-- Populate the #IndexesForAnalysis temporary table with a list of distinct missing indexes for the database this procedure is executing in as gathered by the query optimizer

;
WITH

-- Define a CTE to be used to emulate missing index definitions rather than use those actually in the DMVs

_EmulationData (IndexHandle, DatabaseName, SchemaName, TableName, IndexColumnNames,IncludeColumnNames, ImprovementMeasure)
AS
(
--	IndexHandle	DatbaseName			SchemaName			TableName						IndexComlumnNames				IncludeColumnNames				ImprovementMeasure
--	-----------	---------------------------	---------------------------	---------------------------------------------------	-------------------------------------------	-------------------------------------------	-------------------	

SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Person]',		'[LastName], ',					NULL,						0					UNION 
SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Person]',		'[LastName], ',					'[Title], [FirstName], [PersonType]',		0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Person]',		'[Title], ',					'[LastName], [FirstName]',			0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Person]',		'[Title], [LastName], ',			'[FirstName], [MiddleName]',			0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Person]',		'[PersonType], [LastName], [FirstName], ',	'[Title]',					0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Person]',		'[LastName], [FirstName], ',			'[Title], [PersonType]',			0					UNION

SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Address]',		'[City], ',					'[StateProvinceID]',				0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Address]',		'[PostalCode], ',				'[City], [AddressLine1], [AddressLine2]',	0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[Person]',			'[AdventureWorks2014].[Person].[Address]',			'[PostalCode], [City], ',		'[Title], [PersonType]',			0					UNION

SELECT	NEWID(),	'[AdventureWorks2014]',		'[HumanResources]',		'[AdventureWorks2014].[HumanResources].[Employee]',	'[JobTitle], ',					'[MaritalStatus], [BirthDate]',			0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[HumanResources]',		'[AdventureWorks2014].[HumanResources].[Employee]',	'[JobTitle], ',					'[Gender]',					0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[HumanResources]',		'[AdventureWorks2014].[HumanResources].[Employee]',	'[Gender], ',					'[JobTitle]',					0					UNION
SELECT	NEWID(),	'[AdventureWorks2014]',		'[HumanResources]',		'[AdventureWorks2014].[HumanResources].[Employee]',	'[LoginID], ',					'[Title]',					0 
),

_ExistingIndexIndexColumnNames 
AS
(
SELECT
	Data.Object_ID,
	Data.Index_ID,
	SUBSTRING
		(

		-- Produce a resultset containing the a comma separated list of unique, alphabetically sorted Index column names associated with any existing index

		(
		SELECT
			', ' + QUOTENAME([Column].Name) AS [text()]
		FROM
			Sys.Indexes AS [Index]  

			INNER JOIN 
				Sys.Index_Columns AS IndexColumn
			ON 
				[Index].[object_id] = [IndexColumn].[object_id] 
				AND 
				[Index].index_id = [IndexColumn].index_id

			INNER JOIN 
				sys.columns AS [Column]
			ON 
				IndexColumn.[object_id] = [Column].[object_id]
				AND
				IndexColumn.column_id = [Column].column_id
				
		WHERE
			[Index].Object_ID = Data.Object_ID
			AND
			[Index].Index_ID = Data.Index_ID
			AND
			COALESCE([IndexColumn].is_included_column, 0) = 0
		ORDER BY
			IndexColumn.key_ordinal                                                                                           
		FOR XML PATH('')
		),
		3, 
		8000
		) AS IndexColumnNames
FROM
	Sys.Indexes AS Data 

	INNER JOIN
		Sys.objects AS [Object]
	ON
		Data.Object_ID = [Object].Object_ID

WHERE
	[Object].Type = 'U'
	AND
	Data.Type IN (1, 2)
	AND
	COALESCE(Data.is_hypothetical, 0) = 0
	AND
	COALESCE(Data.Has_Filter, 0) = 0
),

_ExistingIndexIncludeColumnNames 
AS
(
SELECT
	Data.Object_ID,
	Data.Index_ID,
	SUBSTRING
		(

		-- Produce a resultset containing the a comma separated list of unique, alphabetically sorted Include column names associated with any existing index

		(
		SELECT
			', ' + QUOTENAME([Column].Name) AS [text()]
		FROM
			Sys.Indexes AS [Index]  

			INNER JOIN 
				Sys.Index_Columns AS IndexColumn
			ON 
				[Index].[object_id] = [IndexColumn].[object_id] 
				AND 
				[Index].index_id = [IndexColumn].index_id

			INNER JOIN 
				sys.columns AS [Column]
			ON 
				IndexColumn.[object_id] = [Column].[object_id]
				AND
				IndexColumn.column_id = [Column].column_id

		WHERE
			[Index].Object_ID = Data.Object_ID
			AND
			[Index].Index_ID = Data.Index_ID
			AND
			COALESCE([IndexColumn].is_included_column, 0) = 1
		ORDER BY
			[Column].Name                                                                                           
		FOR XML PATH('')
		),
		3, 
		8000
		) AS IncludeColumnNames
FROM
	Sys.Indexes AS Data 

	INNER JOIN
		Sys.objects AS [Object]
	ON
		Data.Object_ID = [Object].Object_ID

WHERE
	[Object].Type = 'U'
	AND
	Data.Type IN (1, 2)
	AND
	COALESCE(Data.is_hypothetical, 0) = 0
	AND
	COALESCE(Data.Has_Filter, 0) = 0
)

INSERT INTO
	#IndexesForAnalysis
	(
	IndexHandle,
	DatabaseName,
	SchemaName,
	TableName,
	IndexColumnNames,
	IncludeColumnNames,
	ImprovementMeasure
	)
SELECT
	NEWID(),
	QUOTENAME(DB_NAME()) AS DatabaseName,
	QUOTENAME(OBJECT_SCHEMA_NAME([Object].[object_id])) AS SchemaName,
	MID.Statement,
	CAST(ISNULL(MID.equality_columns, '') + (CASE WHEN MID.equality_columns IS NOT NULL AND MID.inequality_columns IS NOT NULL THEN ', ' ELSE '' END) + ISNULL(MID.inequality_columns, '') + ', ' AS NVARCHAR(MAX)),
	CAST(MID.included_columns AS NVARCHAR(MAX)),
	MIGS.avg_total_user_cost * (MIGS.avg_user_impact / 100.0) * (MIGS.user_seeks + MIGS.user_scans) 
FROM

	Master.sys.dm_db_missing_index_details AS MID
	
	INNER JOIN
		Sys.objects AS [Object]
	ON
		MID.Object_ID = [Object].Object_ID
		             
	INNER JOIN
		sys.dm_db_missing_index_groups AS MIG
	ON
		MID.index_handle = MIG.index_handle

	INNER JOIN
		sys.dm_db_missing_index_group_stats AS MIGS
	ON
		MIG.index_group_handle = MIGS.group_handle

WHERE
	@Emulate = 0
	AND
	@OptimizeMissingIndexes = 1
	AND
	LEFT(Statement, CHARINDEX('.', Statement) - 1) = '[' + DB_NAME() + ']'
	AND
	OBJECT_SCHEMA_NAME([Object].[object_id]) = COALESCE(@FilterBySchemaName, OBJECT_SCHEMA_NAME([Object].[object_id]))
	AND
	OBJECT_NAME([Object].[object_id]) = COALESCE(@FilterByTableName, OBJECT_NAME([Object].[object_id]))

UNION ALL

SELECT
	NEWID(),
	QUOTENAME(DB_NAME()) AS DatabaseName,
	QUOTENAME(OBJECT_SCHEMA_NAME([Index].[object_id])) AS SchemaName,
	QUOTENAME(DB_NAME()) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME([Index].[object_id])) + '.' + QUOTENAME(OBJECT_NAME([Index].[object_id])) AS TableName,
	IndexColumns.IndexColumnNames,
	IndexIncludeColumns.IncludeColumnNames,
	0 AS ImprovementMeasure
FROM
    Sys.Indexes AS [Index] 
	
	INNER JOIN
		Sys.objects AS [Object]
	ON
		[Index].Object_ID = [Object].Object_ID

	INNER JOIN
		_ExistingIndexIndexColumnNames AS IndexColumns
	ON
		[Index].Object_ID = IndexColumns.Object_ID
		AND
		[Index].Index_ID = IndexColumns.Index_ID

	LEFT JOIN
		_ExistingIndexIncludeColumnNames AS IndexIncludeColumns
	ON
		[Index].Object_ID = IndexIncludeColumns.Object_ID	
		AND
		[Index].Index_ID = IndexIncludeColumns.Index_ID	

WHERE
	1 = 1 -- This functionality is still under development 
	AND
	@Emulate = 0
	AND
	@OptimizeExistingIndexes = 1
	AND
	[Object].Type = 'U'
	AND
	[Index].Type IN (1, 2)
	AND
	COALESCE([Index].is_hypothetical, 0) = 0
	AND
	COALESCE([Index].Has_Filter, 0) = 0
	AND
	OBJECT_SCHEMA_NAME([Object].[object_id]) = COALESCE(@FilterBySchemaName, OBJECT_SCHEMA_NAME([Object].[object_id]))
	AND
	OBJECT_NAME([Index].[object_id]) = COALESCE(@FilterByTableName,OBJECT_NAME([Index].[object_id]))

UNION ALL

SELECT
	*
FROM
	_EmulationData
WHERE
	@Emulate = 1
	
IF (@Debug = 1)
BEGIN

	SELECT '#IndexesForAnalysis'

	SELECT * FROM #IndexesForAnalysis

END

END

/*

*/

BEGIN	-- Consolidate the optimizer defined missing indexes so that an row is inserted into the #IndexesForAnalysis temporary table for each missing Index which is a subset of another missing Index

-- Loop through each of the missing indexes which has not been consolidated and has a non-NULL index handle (denoting 
-- it was an actual recommended missing index) in the #IndexesForAnalysis temporary table 

WHILE 
	EXISTS(SELECT * FROM #IndexesForAnalysis WHERE ID > @ID AND WasConsolidated = 0 AND IndexHandle IS NOT NULL)
BEGIN

	SELECT TOP 1
		@ID = IndexesForAnalysis.ID,
		@DatabaseName = IndexesForAnalysis.DatabaseName,
		@SchemaName = IndexesForAnalysis.SchemaName,
		@TableName = IndexesForAnalysis.TableName,
		@IndexColumnNames = IndexesForAnalysis.IndexColumnNames,
		@IncludeColumnNames = IndexesForAnalysis.IncludeColumnNames
	FROM
		#IndexesForAnalysis AS IndexesForAnalysis
	WHERE
		IndexesForAnalysis.ID > @ID
		AND
		IndexesForAnalysis.WasConsolidated = 0
		AND
		IndexesForAnalysis.IndexHandle IS NOT NULL
	ORDER BY
		IndexesForAnalysis.ID

	;	
	WITH

	-- Define a common table expression (CTE) that produces a comma seperated list of unique, alphabetically sorted Include columm names associated with any missing index
	-- which is a subset of the missing index associated with the "current" row from the #IndexesForAnalysis temporary table 

	_IncludeColumnNames 
	AS
	(
	SELECT
		SUBSTRING
			(

			-- Produce a resultset containing the a comma separated list of unique, alphabetically sorted Include column names associated with any missing index
			-- which is a subset of the missing index associated with the "current" row from the #IndexesForAnalysis temporary table.

			(
			SELECT
				', ' + IncludeColumnName AS [text()]
			FROM
				(
				SELECT DISTINCT
					LTRIM(xTable.xColumn.value('.', 'VARCHAR(MAX)')) AS IncludeColumnName
				FROM
					(
					SELECT
						CAST('<r>' + REPLACE(Data.IncludeColumnNames, ', ', '</r><r>') + '</r>' AS XML) AS IncludeColumnNames
					FROM 

						-- Produce a derived table containing the Include column names from all rows in the temporary table that represent an optimizer defined
						-- missing index which is a subset of the missing index identified on the "current" row of the #IndexesForAnalysis temporary table.
						 
						(
						SELECT
							IndexesForAnalysis.IncludeColumnNames
						FROM
							#IndexesForAnalysis AS IndexesForAnalysis
						WHERE
							IndexesForAnalysis.TableName = @TableName
							AND
							LEFT(@IndexColumnNames, DATALENGTH(@IndexColumnNames) - 2) = LEFT(IndexesForAnalysis.IndexColumnNames, DATALENGTH(@IndexColumnNames) - 2)           
							AND
							IndexesForAnalysis.IndexHandle IS NOT NULL    
							AND
							IndexesForAnalysis.IncludeColumnNames IS NOT NULL         
						) AS Data
					) AS Data
                                                                                                            
					CROSS APPLY
						Data.IncludeColumnNames.nodes('//r') AS xTable(xColumn)
                                                                                               
				) AS Data
                                                                                                               
			WHERE
				NOT EXISTS
					(
					SELECT
						*
					FROM
						#IndexesForAnalysis AS IndexesForAnalysis
					WHERE
						IndexesForAnalysis.TableName = @TableName
						AND
						LEFT(@IndexColumnNames, DATALENGTH(@IndexColumnNames) - 2) = LEFT(IndexesForAnalysis.IndexColumnNames, DATALENGTH(@IndexColumnNames) - 2)          
						AND
						IndexesForAnalysis.IndexHandle IS NOT NULL               
						AND
						CHARINDEX(Data.IncludeColumnName, IndexesForAnalysis.IndexColumnNames) != 0
					)
                                                                             
			ORDER BY
				IncludeColumnName
                                                                                           
			FOR XML PATH('')
			),
			3, 
			8000
			) AS IncludeColumnNames
	) 
  
	-- Insert new rows which represents all the missing indexes that are a subset of the missing index associated with the "current" row
	-- from the #IndexesForAnalysis temporary table along with a common set of Include columns determined from all missing indexes 
	-- which are a subset of the missing index associated with the "current" row. As a result of this insert, every missing index which is a 
	-- subset of the missing index associated with the "current" row will have a new record inserted into the #IndexesForAnalysis 
	-- temporary table, and each row will have the same Include column names and a NULL Index Handle.
	
	INSERT INTO
		#IndexesForAnalysis
		(
		DatabaseName,
		SchemaName,
		TableName,
		IndexColumnNames,
		IncludeColumnNames,
		ImprovementMeasure
		)
	SELECT DISTINCT
		@DatabaseName,
		@SchemaName,
		TableName,
		CAST(MAX(LEFT(IndexesForAnalysis.IndexColumnNames, DATALENGTH(IndexesForAnalysis.IndexColumnNames) - 2)) AS NVARCHAR(MAX)),
		CAST(MAX(LEFT(Data.IncludeColumnNames, DATALENGTH(Data.IncludeColumnNames) - 2)) AS NVARCHAR(MAX)),
		MAX(IndexesForAnalysis.ImprovementMeasure) AS ImprovementMeasure
	FROM
		#IndexesForAnalysis AS IndexesForAnalysis          
	
		CROSS JOIN
			_IncludeColumnNames AS Data
	                                           
	WHERE
		IndexesForAnalysis.TableName = @TableName
		AND
		LEFT(@IndexColumnNames, DATALENGTH(@IndexColumnNames) - 2) = LEFT(IndexesForAnalysis.IndexColumnNames, DATALENGTH(@IndexColumnNames) - 2)              
		AND
		IndexesForAnalysis.IndexHandle IS NOT NULL                           

	GROUP BY
		IndexesForAnalysis.TableName
 
	-- Update the rows in the #IndexesForAnalysis temporary table which are a subset of Index column names associated with the "current" row 
	-- to indicate the rows have been consolidated in such a way as to all have the same Include column names. 

	UPDATE              
		#IndexesForAnalysis
	SET
		WasConsolidated = 1
	WHERE
		TableName = @TableName
		AND
		LEFT(@IndexColumnNames, DATALENGTH(@IndexColumnNames) - 2) = LEFT(#IndexesForAnalysis.IndexColumnNames, DATALENGTH(@IndexColumnNames) - 2)                                              
		AND
		IndexHandle IS NOT NULL 
		                                          
END

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IndexesForAnalysis'

	SELECT * FROM #IndexesForAnalysis

END

END

/*

*/

BEGIN	-- Create and populate the #IndexesForAnalysisColumn temporary table with the partially normalized list of Index columns associated with a missing index from the #IndexesForAnalysis temporary table

;
WITH

-- Define a CTE which converts the comma separated list of Index column names into XML special characters for 'carriage return' from non-consolidated missing 
-- indexes from the IndexesForAnalysis temporary table.

_DenormalizedIndexesForAnalysisColumn
AS
(
SELECT
	IndexesForAnalysis.ID AS IndexHandle,
	IndexesForAnalysis.TableName,
	CAST('<r>' + REPLACE(IndexesForAnalysis.IndexColumnNames, ', ', '</r><r>') + '</r>' AS XML) AS IndexColumnNames,
	IndexesForAnalysis.ImprovementMeasure
FROM
	#IndexesForAnalysis AS IndexesForAnalysis
WHERE
	IndexesForAnalysis.WasConsolidated = 0
	AND
	LEFT(IndexesForAnalysis.TableName, CHARINDEX('.', IndexesForAnalysis.TableName) - 1) = '[' + DB_NAME() + ']'
),
         
-- Define a CTE which partially normalizes the list of Index columns associated with an missing index from the #IndexesForAnalysis temporary table

_IndexesForAnalysisColumn
AS
(
SELECT
	DenormalizedIndexesForAnalysisColumn.IndexHandle,
	LEFT(DenormalizedIndexesForAnalysisColumn.TableName, CHARINDEX('.', DenormalizedIndexesForAnalysisColumn.TableName) - 1) AS DatabaseName,
	SUBSTRING(DenormalizedIndexesForAnalysisColumn.TableName, CHARINDEX('.', DenormalizedIndexesForAnalysisColumn.TableName) + 1, LEN(DenormalizedIndexesForAnalysisColumn.TableName) - CHARINDEX('.', REVERSE(DenormalizedIndexesForAnalysisColumn.TableName)) - CHARINDEX('.', DenormalizedIndexesForAnalysisColumn.TableName)) AS SchemaName,
	RIGHT(DenormalizedIndexesForAnalysisColumn.TableName, CHARINDEX('.', REVERSE(DenormalizedIndexesForAnalysisColumn.TableName)) - 1) AS TableName,
	LTRIM(xTable.xColumn.value('.', 'VARCHAR(MAX)')) AS IndexColumnName,
	ROW_NUMBER() OVER (PARTITION BY DenormalizedIndexesForAnalysisColumn.TableName, DenormalizedIndexesForAnalysisColumn.IndexHandle ORDER BY DenormalizedIndexesForAnalysisColumn.IndexHandle) AS OrdinalPosition,
	DenormalizedIndexesForAnalysisColumn.ImprovementMeasure
FROM
	_DenormalizedIndexesForAnalysisColumn AS DenormalizedIndexesForAnalysisColumn
								                             
	CROSS APPLY
		IndexColumnNames.nodes('//r') AS xTable(xColumn)

WHERE
	LTRIM(xTable.xColumn.value('.', 'VARCHAR(MAX)')) != ''

)

-- Create and populate the #IndexesForAnalysisColumn temporary table with the fully normalized list of Index columns associated with a missing index
-- from the #IndexesForAnalysis temporary table

SELECT
	*
INTO
	#IndexesForAnalysisColumn
FROM
	_IndexesForAnalysisColumn

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IndexesForAnalysisColumn'

	SELECT * FROM #IndexesForAnalysisColumn

END

END

/*
 
*/

BEGIN	-- Create and populate the #IndexesForAnalysisIncludeColumn temporary table with the fully normalized list of Index Include columns associated with a missing index from the #IndexesForAnalysis temporary table

;
WITH
    
-- Define a CTE which converts the comma separated list of Include column names into XML special characters for 'carriage return' from non-consolidated missing 
-- indexes from the IndexesForAnalysis temporary table.

_DenormalizedIndexesForAnalysisIncludeColumn
AS
(
SELECT
	IndexesForAnalysis.ID AS IndexHandle,
	IndexesForAnalysis.TableName,
	CAST('<r>' + REPLACE(IndexesForAnalysis.IncludeColumnNames, ', ', '</r><r>') + '</r>' AS XML) AS IncludeColumnNames
FROM
	#IndexesForAnalysis AS IndexesForAnalysis
WHERE
	IndexesForAnalysis.WasConsolidated = 0
	AND
	LEFT(IndexesForAnalysis.TableName, CHARINDEX('.', IndexesForAnalysis.TableName) - 1) = '[' + DB_NAME() + ']'
),
               
-- Define a CTE which partially normalizes the list of Include columns associated with a missing index from the #IndexesForAnalysis temporary table

_IndexesForAnalysisIncludeColumn
AS
(
SELECT
	DenormalizedIndexesForAnalysisIncludeColumn.IndexHandle,
	LEFT(DenormalizedIndexesForAnalysisIncludeColumn.TableName, CHARINDEX('.', DenormalizedIndexesForAnalysisIncludeColumn.TableName) - 1) AS DatabaseName,
	SUBSTRING(DenormalizedIndexesForAnalysisIncludeColumn.TableName, CHARINDEX('.', DenormalizedIndexesForAnalysisIncludeColumn.TableName) + 1, LEN(DenormalizedIndexesForAnalysisIncludeColumn.TableName) - CHARINDEX('.', REVERSE(DenormalizedIndexesForAnalysisIncludeColumn.TableName)) - CHARINDEX('.', DenormalizedIndexesForAnalysisIncludeColumn.TableName)) AS SchemaName,
	RIGHT(DenormalizedIndexesForAnalysisIncludeColumn.TableName, CHARINDEX('.', REVERSE(DenormalizedIndexesForAnalysisIncludeColumn.TableName)) - 1) AS TableName,
	LTRIM(xTable.xColumn.value('.', 'VARCHAR(MAX)')) AS IncludeColumnName
FROM
	_DenormalizedIndexesForAnalysisIncludeColumn AS DenormalizedIndexesForAnalysisIncludeColumn
                               
	CROSS APPLY
		IncludeColumnNames.nodes('//r') AS xTable(xColumn)
)

-- Create and populate the #IndexesForAnalysisIncludeColumn temporary table with the fully normalized list of Index Include columns associated with a missing index
-- from the #IndexesForAnalysis temporary table

SELECT
	*
INTO
	#IndexesForAnalysisIncludeColumn
FROM
	_IndexesForAnalysisIncludeColumn

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IndexesForAnalysisIncludeColumn'

	SELECT * FROM #IndexesForAnalysisIncludeColumn

END

END

/*

*/

BEGIN	-- Create and populate the #NormalizedIndexesForAnalysis temporary table with the normalized list of Index columns (along with the Schema, Table and Column IDs for such) associated with a missing index from the #IndexesForAnalysis temporary table   

;
WITH
    
-- Define a CTE which fully normalizes the list of Index columns associated with a missing index from the #IndexesForAnalysis temporary table.

_IntelligentIndex
AS
(
SELECT DISTINCT
	IndexesForAnalysisColumn.DatabaseName,
	IndexesForAnalysisColumn.SchemaName,
	[Schema].schema_id AS SchemaID,
	IndexesForAnalysisColumn.TableName,
	[Table].Object_ID AS TableID,
	IndexesForAnalysisColumn.IndexHandle,
	IndexesForAnalysisColumn.OrdinalPosition,
	IndexesForAnalysisColumn.IndexColumnName,
	[Column].column_id AS ColumnID,
	[ColumnType].Name AS ColumnTypeName,
	[Column].max_length AS ColumnMaxLength,
	IndexesForAnalysisColumn.ImprovementMeasure
FROM
	#IndexesForAnalysisColumn AS IndexesForAnalysisColumn

	INNER JOIN
		sys.Schemas AS [Schema]
	ON
		IndexesForAnalysisColumn.SchemaName = '[' + [Schema].name + ']'
                                               
	INNER JOIN
		Sys.objects AS [Table]
	ON
		IndexesForAnalysisColumn.TableName = '[' + [Table].name + ']'
		AND
		[Table].schema_id  = [Schema].schema_id

	INNER JOIN
		Sys.columns AS [Column]
	ON
		[Table].object_id = [Column].object_id        
	
	INNER JOIN
		Sys.Types AS ColumnType
	ON
		[Column].System_Type_ID = [ColumnType].System_Type_ID
			        
WHERE
	IndexesForAnalysisColumn.IndexColumnName = '[' + [Column].name + ']'
)

-- Create and populate the #NormalizedIndexesForAnalysis temporary table with the normalized list of Index columns (along with the Schema, Table and Column IDs for such) 
-- associated with a missing index from the #IndexesForAnalysis temporary table
       
SELECT
	*
INTO
	#IntelligentIndex
FROM
	_IntelligentIndex
 
CREATE NONCLUSTERED INDEX
	#ncidxIntelligentIndex
ON
	#IntelligentIndex
	(
	TableID,
	IndexHandle
	)

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IntelligentIndex'

	SELECT * FROM #IntelligentIndex

END

END

/*

*/

BEGIN	-- Create and populate the #IntelligentIndexWithExistingIndexMatch temporary table which combines the fully normalized list of missing Index Columns associated with a missing index from the #OptimizerDeterminedMissingIndexes temporary table with existing index columns in the same ordinal position as a columns on the missing index

;
WITH
 
-- Define a CTE which contains rows for the fully normalized list of missing Index Columns associated with a missing index from the #OptimizerDeterminedMissingIndexes temporary table with
-- existing index columns in the same ordinal position as a columns on the missing index
    
_IntelligentIndexWithExistingIndexMatch
AS
(
SELECT
	IntelligentIndex.DatabaseName,
	IntelligentIndex.SchemaName,
	IntelligentIndex.SchemaID,
	IntelligentIndex.TableName,
	IntelligentIndex.TableID,
	IntelligentIndex.ImprovementMeasure,
	IntelligentIndex.IndexHandle,
	IntelligentIndex.OrdinalPosition,
	IntelligentIndex.IndexColumnName,
	IntelligentIndex.ColumnID,
	IntelligentIndex.ColumnTypeName,
	IntelligentIndex.ColumnMaxLength,
	IndexColumn.is_descending_key As IsDescendingKey,
	[Index].Name AS IndexName,
	IndexColumn.Index_ID AS IndexID,
	(CASE WHEN [Index].type = 1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) AS IsClustered,
	[Index].Is_unique AS IsUnique,
	[Index].Is_Primary_Key AS IsPrimaryKey,
	[Index].Fill_Factor AS [FillFactor],
	[Index].Ignore_Dup_Key AS IgnoreDupKey,
	[Index].Is_Padded AS IsPadded,
	[Index].[Allow_Row_Locks] AS AllowRowLocks,
	[Index].[Allow_Page_Locks] AS AllowPageLocks, 
	IndexStatistics.No_Recompute AS NoRecomputeStatistics,
	[Filegroup].Name AS [Filegroup],
	CAST(1 AS BIT) AS IsSpecified
FROM
	#IntelligentIndex AS IntelligentIndex

	INNER JOIN
		sys.index_columns AS IndexColumn
	ON                        
		IntelligentIndex.TableID = IndexColumn.Object_ID 
		AND
		IntelligentIndex.ColumnID = IndexColumn.column_id 
		AND
		IntelligentIndex.OrdinalPosition = IndexColumn.key_ordinal
               
	INNER JOIN
		sys.indexes AS [Index]
	ON
		IndexColumn.Object_ID = [Index].Object_ID
		AND
		IndexColumn.Index_ID = [Index].Index_ID
  
	LEFT JOIN 
		Sys.Stats AS IndexStatistics 
	ON
		[Index].Object_ID = IndexStatistics.Object_ID
		AND
		[Index].Index_ID = IndexStatistics.Stats_ID

	LEFT JOIN
		Sys.Filegroups AS [Filegroup]
	ON
		[Index].Data_Space_ID = [Filegroup].Data_Space_ID
                  
WHERE
	[Index].is_hypothetical = 0
	AND
	[Index].has_filter = 0
	AND
	COALESCE(IndexColumn.is_included_column, 0) = 0
            
	AND

	(
	SELECT
		MAX(OrdinalPosition)
	FROM
		#IntelligentIndex AS RelatedIntelligentIndex
	WHERE
		RelatedIntelligentIndex.IndexHandle = IntelligentIndex.IndexHandle
	)
	>=
	(
	SELECT
		MAX(key_ordinal)
	FROM
		sys.index_columns AS RelatedIndexColumn
	WHERE
		RelatedIndexColumn.Object_ID = IndexColumn.Object_id
		AND
		RelatedIndexColumn.Index_ID = IndexColumn.index_id
	)

	AND    
	  
	NOT EXISTS -- The missing index cannot contain any columns that are not part of an existing index in the correct position
		(
		SELECT
			*
		FROM                          
			#IntelligentIndex AS RelatedIntelligentIndex
                                                               
			INNER JOIN
				sys.index_columns AS RelatedIndexColumn
			ON
				RelatedIndexColumn.Object_ID = RelatedIntelligentIndex.TableID
				AND
				RelatedIndexColumn.index_id = IndexColumn.index_id              
				AND
				RelatedIndexColumn.column_id = RelatedIntelligentIndex.ColumnID
                                                                               
		WHERE
			RelatedIntelligentIndex.TableID = IntelligentIndex.TableID
			AND
			RelatedIntelligentIndex.IndexHandle = IntelligentIndex.IndexHandle
			AND
			COALESCE(RelatedIndexColumn.is_included_column, 0) = 0
			AND
			RelatedIndexColumn.key_ordinal != RelatedIntelligentIndex.OrdinalPosition  
			AND
			RelatedIndexColumn.column_id != IndexColumn.Column_ID
		)

	AND

	NOT EXISTS
		(
		SELECT
			*
		FROM
			#IntelligentIndex AS RelatedIntelligentIndex
                                                               
			INNER JOIN
				sys.index_columns AS RelatedIndexColumn
			ON
				RelatedIndexColumn.Object_ID = RelatedIntelligentIndex.TableID
				AND
				RelatedIndexColumn.index_id = IndexColumn.index_id
				AND
				RelatedIndexColumn.key_ordinal = RelatedIntelligentIndex.OrdinalPosition  
                                                                               
		WHERE
			RelatedIntelligentIndex.TableID = IntelligentIndex.TableID
			AND
			RelatedIntelligentIndex.IndexHandle = IntelligentIndex.IndexHandle
			AND
			COALESCE(RelatedIndexColumn.is_included_column, 0) = 0
			AND
			RelatedIndexColumn.column_id != RelatedIntelligentIndex.ColumnID
			AND
			RelatedIndexColumn.column_id != IndexColumn.Column_ID
		)
                                                                              
)

-- Create and populate the #IntelligentIndexWithExistingIndexMatch temporary table which combines the fully normalized list of missing Index Columns associated with a missing index from
-- the #OptimizerDeterminedMissingIndexes temporary table with existing index columns in the same ordinal position as a columns on the missing index

SELECT
	*
INTO
	#IntelligentIndexWithExistingIndexMatch
FROM
	_IntelligentIndexWithExistingIndexMatch

CREATE NONCLUSTERED INDEX
	#ncidxIntelligentIndexWithExistingIndexMatch
ON
	#IntelligentIndexWithExistingIndexMatch
	(
	TableID,
	IndexHandle
	)

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IntelligentIndexWithExistingIndexMatch'

	SELECT 
		* 
	FROM 
		#IntelligentIndexWithExistingIndexMatch
	ORDER BY
		SchemaName,
		TableName,
		IndexHandle,
		OrdinalPosition

END

END

/*

*/

BEGIN	-- Create and populate the #IntelligentIndexWithoutExistingIndexMatch temporary table which is the fully normalized list of missing Index Columns associated with a missing index from the #OptimizerDeterminedMissingIndexes temporary table without existing index columns in the same ordinal position as a columns on the missing index

;
WITH
    
-- Define a CTE which contains the list of normalized optimizer defined missing index columns which are not already part of an existing index in the correct ordinal position.
    
_IntelligentIndexWithoutExistingIndexMatch
AS
(
SELECT
	IntelligentIndex.DatabaseName,
	IntelligentIndex.SchemaName,
	IntelligentIndex.SchemaID,
	IntelligentIndex.TableName,
	IntelligentIndex.TableID,
	IntelligentIndex.ImprovementMeasure,
	IntelligentIndex.IndexHandle,
	IntelligentIndex.OrdinalPosition,
	IntelligentIndex.IndexColumnName,
	IntelligentIndex.ColumnID,
	IntelligentIndex.ColumnTypeName,
	IntelligentIndex.ColumnMaxLength,
	CAST(NULL AS BIT) AS IsDescendingKey,
	CAST(NULL AS SYSNAME) AS IndexName,
	CAST(NULL AS INT) AS IndexID,
	CAST(NULL AS BIT) AS IsClustered,
	CAST(NULL AS BIT) AS IsUnique,
	CAST(NULL AS BIT) AS IsPrimaryKey,
	CAST(NULL AS TINYINT) AS [FillFactor],
	CAST(NULL AS BIT) AS IgnoreDupKey,
	CAST(NULL AS BIT) AS IsPadded,
	CAST(NULL AS BIT) AS AllowRowLocks,
	CAST(NULL AS BIT) AS AllowPageLocks,
	CAST(NULL AS BIT) AS NoRecomputeStatistics,
	CAST(NULL AS SYSNAME) AS [Filegroup],
	CAST(0 AS BIT) AS IsSpecified                                                  
FROM
	#IntelligentIndex AS IntelligentIndex
WHERE
	NOT EXISTS
		(
		SELECT
			*
		FROM
			#IntelligentIndexWithExistingIndexMatch AS RelatedIntelligentIndex
		WHERE
			RelatedIntelligentIndex.TableID = IntelligentIndex.TableID
			AND
			RelatedIntelligentIndex.IndexHandle = IntelligentIndex.IndexHandle
			AND
			RelatedIntelligentIndex.ColumnID = IntelligentIndex.ColumnID
		)                                             
)
	
-- Create and populate the #IntelligentIndexWithExistingIndexColumnoutMatches temporary table which is the fully normalized list of missing Index Columns associated with a missing index 
-- from the #OptimizerDeterminedMissingIndexes temporary table without existing index columns in the same ordinal position as a columns on the missing index

SELECT
	*
INTO
	#IntelligentIndexWithoutExistingIndexMatch
FROM
	_IntelligentIndexWithoutExistingIndexMatch
 
CREATE NONCLUSTERED INDEX
	#ncidxIntelligentIndexColumnWithoutExistingIndexMatch
ON
	#IntelligentIndexWithoutExistingIndexMatch
	(
	TableID,
	IndexHandle
	)

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IntelligentIndexWithoutExistingIndexMatch'

	SELECT * FROM #IntelligentIndexWithoutExistingIndexMatch

END

END

/*

*/

BEGIN	-- Create and populate the #IntelligentIndexMappedToExistingIndex temporary table which is the fully normalized list of missing Index Columns associated with a missing index from the #OptimizerDeterminedMissingIndexes temporary table  to existing index columns in the same ordinal position as a columns on the missing index

;
WITH
_IntelligentIndexMappedToExistingIndex
AS
(
SELECT
	*
FROM
	#IntelligentIndexWithExistingIndexMatch
                               
UNION ALL
               
SELECT
	*
FROM
	#IntelligentIndexWithoutExistingIndexMatch
)
 
-- Create and populate the #IntelligentIndexMappedToExistingIndex temporary table which is the fully normalized list of missing Index Columns associated with a missing index from 
-- the #OptimizerDeterminedMissingIndexes temporary table  to existing index columns in the same ordinal position as a columns on the missing index
          
SELECT
	*
INTO
	#IntelligentIndexMappedToExistingIndex
FROM
	_IntelligentIndexMappedToExistingIndex
 
CREATE NONCLUSTERED INDEX
	#ncidx_IntelligentIndexMappedToExistingIndex
ON
	#IntelligentIndexMappedToExistingIndex
	(
	TableID,
	IndexHandle
	)

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IntelligentIndexMappedToExistingIndex'

	SELECT * FROM #IntelligentIndexMappedToExistingIndex

END
         
END

/*

*/

BEGIN	-- Create and populate the #IntelligentIndexMappedToExistingIndexWithStatus temporary table which is the fully normalized list of missing Index Columns associated with a missing index from the #OptimizerDeterminedMissingIndexes temporary table  to existing index columns in the same ordinal position as a columns on the missing index

;
WITH
    
_IntelligentIndexColumnMappedToExistingIndexWithStatus
AS
(
SELECT DISTINCT
	IntelligentIndexMappedToExistingIndex.DatabaseName,
	IntelligentIndexMappedToExistingIndex.SchemaName,
	IntelligentIndexMappedToExistingIndex.SchemaID,
	IntelligentIndexMappedToExistingIndex.TableName,
	IntelligentIndexMappedToExistingIndex.TableID,
	IntelligentIndexMappedToExistingIndex.ImprovementMeasure,
	IntelligentIndexMappedToExistingIndex.IndexHandle,
	IntelligentIndexMappedToExistingIndex.OrdinalPosition,
	IntelligentIndexMappedToExistingIndex.IndexColumnName,
	IntelligentIndexMappedToExistingIndex.ColumnID,
	IntelligentIndexMappedToExistingIndex.ColumnTypeName,
	IntelligentIndexMappedToExistingIndex.ColumnMaxLength,
	IntelligentIndexMappedToExistingIndex.IsDescendingKey,
	IntelligentIndexMappedToExistingIndex.IndexName,
	IntelligentIndexMappedToExistingIndex.IndexID,
	IntelligentIndexMappedToExistingIndex.IsClustered,
	IntelligentIndexMappedToExistingIndex.IsUnique,
	IntelligentIndexMappedToExistingIndex.IsPrimaryKey,
	IntelligentIndexMappedToExistingIndex.[FillFactor],
	IntelligentIndexMappedToExistingIndex.IgnoreDupKey,
	IntelligentIndexMappedToExistingIndex.IsPadded,
	IntelligentIndexMappedToExistingIndex.AllowRowLocks,
	IntelligentIndexMappedToExistingIndex.AllowPageLocks,
	IntelligentIndexMappedToExistingIndex.NoRecomputeStatistics,
	IntelligentIndexMappedToExistingIndex.[Filegroup],
	IntelligentIndexMappedToExistingIndex.IsSpecified,

	-- What are the column names associated with the index in their correct order?
	CAST(IndexesForAnalysis.IndexColumnNames AS NVARCHAR(MAX)) AS IndexColumnNames
                                                                                                                                                                                                                               
FROM
	#IntelligentIndexMappedToExistingIndex AS IntelligentIndexMappedToExistingIndex

	INNER JOIN
		#IndexesForAnalysis AS IndexesForAnalysis
	ON
		IntelligentIndexMappedToExistingIndex.IndexHandle = IndexesForAnalysis.ID
)
                               
SELECT
	*
INTO
	#IntelligentIndexMappedToExistingIndexWithStatus
FROM
	_IntelligentIndexColumnMappedToExistingIndexWithStatus
 
CREATE NONCLUSTERED INDEX
	#ncidxIntelligentIndexColumnMappedToExistingIndexWithStatus
ON
	#IntelligentIndexMappedToExistingIndexWithStatus
	(
	TableID,
	IndexHandle
	)
 
UPDATE
	IntelligentIndexMappedToExistingIndexWithStatus
SET
	IndexID = RelatedIntelligentIndexMappedToExistingIndexWithStatus.IndexID,
	IndexName = RelatedIntelligentIndexMappedToExistingIndexWithStatus.IndexName,
	IsClustered = RelatedIntelligentIndexMappedToExistingIndexWithStatus.IsClustered,
	IsUnique = RelatedIntelligentIndexMappedToExistingIndexWithStatus.IsUnique,
	IsPrimaryKey = RelatedIntelligentIndexMappedToExistingIndexWithStatus.IsPrimaryKey,
	[FillFactor] = RelatedIntelligentIndexMappedToExistingIndexWithStatus.[FillFactor],
	IgnoreDupKey = RelatedIntelligentIndexMappedToExistingIndexWithStatus.IgnoreDupKey,
	IsPadded = RelatedIntelligentIndexMappedToExistingIndexWithStatus.IsPadded,
	AllowRowLocks = RelatedIntelligentIndexMappedToExistingIndexWithStatus.AllowRowLocks,
	AllowPageLocks = RelatedIntelligentIndexMappedToExistingIndexWithStatus.AllowPageLocks,
	NoRecomputeStatistics = RelatedIntelligentIndexMappedToExistingIndexWithStatus.NoRecomputeStatistics,
	[Filegroup] = RelatedIntelligentIndexMappedToExistingIndexWithStatus.[Filegroup]

FROM
	#IntelligentIndexMappedToExistingIndexWithStatus AS IntelligentIndexMappedToExistingIndexWithStatus

	INNER JOIN
		#IntelligentIndexMappedToExistingIndexWithStatus AS RelatedIntelligentIndexMappedToExistingIndexWithStatus
	ON
		IntelligentIndexMappedToExistingIndexWithStatus.TableID = RelatedIntelligentIndexMappedToExistingIndexWithStatus.TableID
		AND
		IntelligentIndexMappedToExistingIndexWithStatus.IndexHandle = RelatedIntelligentIndexMappedToExistingIndexWithStatus.IndexHandle
		AND
		IntelligentIndexMappedToExistingIndexWithStatus.OrdinalPosition > RelatedIntelligentIndexMappedToExistingIndexWithStatus.OrdinalPosition
                               
WHERE
	IntelligentIndexMappedToExistingIndexWithStatus.IndexID IS NULL
	AND
	RelatedIntelligentIndexMappedToExistingIndexWithStatus.IndexID IS NOT NULL

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IntelligentIndexMappedToExistingIndexWithStatus'

	SELECT * FROM #IntelligentIndexMappedToExistingIndexWithStatus

END
             
END

/*
 
*/

BEGIN	-- Create and populate the #IntelligentIndexMappedToExistingIndexWithStatusAndNames temporary table which is the fully normalized list of missing Index Columns associated with a missing index from the #OptimizerDeterminedMissingIndexes temporary table  to existing index columns in the same ordinal position as a columns on the missing index
           
;
WITH

-- Define a CTE which produce a comma separated list of Index column names for any existing index

_IndexColumnOrginal
AS
(
SELECT
	[Index].OBJECT_ID AS TableID,
	[Index].index_id AS IndexID,
	SUBSTRING
		(
		(
		SELECT
			', [' + IndexColumnName + ']' AS [text()]
		FROM
			(
			SELECT
				IndexColumn.index_id AS IndexID,
				[Column].name AS IndexColumnName,
				IndexColumn.key_ordinal AS OrdinalPosition
			FROM
                                                                                                                                                                                                                               
				Sys.index_columns AS IndexColumn
                                                                                                               
				INNER JOIN
					Sys.columns AS [Column]
				ON
					[Index].object_id = [Column].object_id
					AND
					IndexColumn.column_id = [Column].column_id
                                                                                                                               
			WHERE
				IndexColumn.is_included_column = 0
				AND
				IndexColumn.OBJECT_ID = [Index].OBJECT_ID
				AND
				IndexColumn.index_id = [Index].Index_ID
			) AS Data
                                                                                               
		ORDER BY
			IndexID,
			OrdinalPosition
                                                                               
		FOR XML PATH('')
                                                                               
		),
		3, 
		8000
		) AS IndexColumns
FROM
	Sys.indexes AS [Index]
),

-- Define a CTE which produce a comma separated list of Index Include column names for any existing index

_IncludeColumnOriginal
AS
(
SELECT
	[Index].OBJECT_ID AS TableID,
	[Index].index_id AS IndexID,
	SUBSTRING
		(
		(
		SELECT
			', [' + IncludeColumnName + ']' AS [text()]
		FROM
			(
			SELECT
				IndexColumn.OBJECT_ID AS TableID,
				IndexColumn.index_id AS IndexID,
				[Column].name AS IncludeColumnName
			FROM
                                                                                                                                                                                                                               
				Sys.index_columns AS IndexColumn
                                                                                                               
				INNER JOIN
					Sys.columns AS [Column]
			ON
				[Index].object_id = [Column].object_id
				AND
				IndexColumn.column_id = [Column].column_id
                                                                                                                               
			WHERE
				IndexColumn.is_included_column = 1
				AND
				IndexColumn.OBJECT_ID = [Index].OBJECT_ID
				AND
				IndexColumn.index_id = [Index].Index_ID
			) AS Data
      		                                   
		ORDER BY
			TableID,
			IndexID,
			IncludeColumnName
                                                                               
		FOR XML PATH('')
                                                                               
		),
		3, 
		8000) AS IncludeColumnNames
FROM
	Sys.indexes AS [Index]
),

-- Define a CTE which produce a comma separated list of missing Index Include column names for any missing index

_IncludeColumn 
AS
(
SELECT DISTINCT
	IntelligentIndexMappedToExistingIndexWithStatus.TableID,
	IntelligentIndexMappedToExistingIndexWithStatus.IndexHandle,
	SUBSTRING
		(
		(
		SELECT
			', ' + IncludeColumnName AS [text()]
		FROM
			(
			SELECT DISTINCT
				IndexHandle,
				IncludeColumnName
			FROM
				#IndexesForAnalysisIncludeColumn AS IndexesForAnalysisIncludeColumn
			WHERE
				IndexesForAnalysisIncludeColumn.IndexHandle = IntelligentIndexMappedToExistingIndexWithStatus.IndexHandle
                                                                                                                                                                                                                                                               
			UNION
                                                                                                                                                                                               
			SELECT DISTINCT
				IntelligentIndexMappedToExistingIndexWithStatus.IndexHandle,
				'[' + [Column].name + ']' AS IndexColumnName
			FROM
                                                                                                               
				Sys.index_columns AS IndexColumn
                                                                                                                               
				INNER JOIN
					Sys.columns AS [Column]
				ON
					IntelligentIndexMappedToExistingIndexWithStatus.TableID = [Column].object_id
					AND
					IndexColumn.column_id = [Column].column_id
                                                                                                                               
			WHERE
				IndexColumn.is_included_column = 1
				AND
				IndexColumn.OBJECT_ID = IntelligentIndexMappedToExistingIndexWithStatus.TableID
				AND
				IndexColumn.index_id = IntelligentIndexMappedToExistingIndexWithStatus.IndexID
			) AS Data
 
  		WHERE
			NOT EXISTS
				(
				SELECT
					*
				FROM
					#IntelligentIndexMappedToExistingIndexWithStatus AS IntelligentIndexMappedToExistingIndexWithStatus	
				WHERE
					IntelligentIndexMappedToExistingIndexWithStatus.IndexHandle = Data.IndexHandle
					AND
					IntelligentIndexMappedToExistingIndexWithStatus.IndexColumnName = Data.IncludeColumnName
				)
		                                                                                                                                                    
		ORDER BY
			IndexHandle,
			IncludeColumnName
                                                                               
		FOR XML PATH('')
                                                                               
		),
		3, 
		8000) AS IncludeColumnNames
FROM
	#IntelligentIndexMappedToExistingIndexWithStatus AS IntelligentIndexMappedToExistingIndexWithStatus
),

_IntelligentIndexMappedToExistingIndexWithStatusAndNames
AS
(
SELECT DISTINCT
	NormalizedIndexColumn.DatabaseName,
	NormalizedIndexColumn.SchemaName,
	NormalizedIndexColumn.SchemaID,
	NormalizedIndexColumn.TableName,
	NormalizedIndexColumn.TableID,
	NormalizedIndexColumn.ImprovementMeasure,
	NormalizedIndexColumn.IndexHandle,
	NormalizedIndexColumn.IndexName,
	NormalizedIndexColumn.IndexID,
	NormalizedIndexColumn.IsClustered,
	NormalizedIndexColumn.IsUnique,
	NormalizedIndexColumn.IsPrimaryKey,
	NormalizedIndexColumn.[FillFactor],
	NormalizedIndexColumn.IgnoreDupKey,
	NormalizedIndexColumn.IsPadded,
	NormalizedIndexColumn.AllowRowLocks,
	NormalizedIndexColumn.AllowPageLocks, 
	NormalizedIndexColumn.NoRecomputeStatistics,
	NormalizedIndexColumn.[Filegroup],
	IndexColumnOriginal.IndexColumns AS IndexColumnNamesOriginal,
	IncludeColumnOriginal.IncludeColumnNames AS IncludeColumnNamesOriginal,
	REPLACE(NormalizedIndexColumn.IndexColumnNames, ',', (CASE WHEN NormalizedIndexColumn.IsDescendingKey = 1 THEN ' DESC,' ELSE ',' END)) AS IndexColumnNames,
	IncludeColumn.IncludeColumnNames
FROM
	#IntelligentIndexMappedToExistingIndexWithStatus AS NormalizedIndexColumn
 
	LEFT JOIN
		_IndexColumnOrginal AS IndexColumnOriginal
	ON
		NormalizedIndexColumn.TableID = IndexColumnOriginal.TableID
		AND
		NormalizedIndexColumn.IndexID = IndexColumnOriginal.IndexID
 
	LEFT JOIN
		_IncludeColumnOriginal AS IncludeColumnOriginal
	ON
		NormalizedIndexColumn.TableID = IncludeColumnOriginal.TableID
		AND
		NormalizedIndexColumn.IndexID = IncludeColumnOriginal.IndexID
                                               
	LEFT JOIN
		_IncludeColumn AS IncludeColumn
	ON
		NormalizedIndexColumn.TableID = IncludeColumn.TableID
		AND
		NormalizedIndexColumn.IndexHandle = IncludeColumn.IndexHandle 
 
)
 
SELECT
	*
INTO
	#IntelligentIndexMappedToExistingIndexWithStatusAndNames
FROM
	_IntelligentIndexMappedToExistingIndexWithStatusAndNames

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IntelligentIndexMappedToExistingIndexWithStatusAndNames'

	SELECT * FROM #IntelligentIndexMappedToExistingIndexWithStatusAndNames

END

END

/*

*/

BEGIN	-- Create and populate the ##IntelligentIndexWithTableAndBaseIndexName temporary table 
;

WITH  
 
-- Define a CTE which provides the table name and base index name for every missing index

_IntelligentIndexWithTableAndBaseIndexName
AS
(
SELECT DISTINCT
	NormalizedIndex.DatabaseName,
	NormalizedIndex.SchemaName,
	QUOTENAME([Table].name) AS TableName,
	NormalizedIndexPrimaryKey.IsPrimaryKey AS TableHasPrimaryKey,
	NormalizedIndex.IndexName AS IndexNameOriginal,
	NormalizedIndex.IndexColumnNamesOriginal,
	NormalizedIndex.IncludeColumnNamesOriginal,
	NormalizedIndex.IsClustered AS IsClusteredOriginal,
	NormalizedIndex.IsUnique AS IsUniqueOriginal,
	NormalizedIndex.IsPrimaryKey AS IsPrimaryKeyOriginal,
	NormalizedIndex.[FillFactor] AS FillFactorOriginal,
	NormalizedIndex.IgnoreDupKey AS IgnoreDupKeyOriginal,
	NormalizedIndex.IsPadded AS IsPaddedOriginal,
	NormalizedIndex.AllowRowLocks AS AllowRowLocksOriginal,
	NormalizedIndex.AllowPageLocks AS AllowPageLocksOriginal,
	NormalizedIndex.NoRecomputeStatistics AS NoRecomputeStatisticsOriginal, 
	NormalizedIndex.Filegroup AS FilegroupOriginal, 
	                                             
	(
	CASE
		WHEN
			NormalizedIndex.IsPrimaryKey = 1
		THEN
			@PrimaryKeyNamePrefix
		ELSE
			@IndexNamePrefix
	END
	)
	+
	'_'
	+
	REPLACE(REPLACE([Table].Name, '[', ''), ']', '') AS IndexNameBase,
                               
	NormalizedIndex.IndexColumnNames,
	NormalizedIndex.IncludeColumnNames,
	MAX(NormalizedIndex.ImprovementMeasure) AS ImprovementMeasure
 
FROM
	#IntelligentIndexMappedToExistingIndexWithStatusAndNames AS NormalizedIndex
                               
	INNER JOIN
		sys.objects AS [Table]
	ON
		NormalizedIndex.TableID = [Table].object_id
               
	LEFT JOIN
		#IntelligentIndexMappedToExistingIndexWithStatusAndNames AS NormalizedIndexPrimaryKey
		ON
		NormalizedIndex.SchemaName = NormalizedIndexPrimaryKey.SchemaName
		AND
		NormalizedIndex.[TableID] = NormalizedIndexPrimaryKey.[TableID]
		AND
		NormalizedIndexPrimaryKey.IsPrimaryKey = 1
                                               
GROUP BY
	NormalizedIndex.DatabaseName,
	NormalizedIndex.SchemaName,
	[Table].name,
	NormalizedIndexPrimaryKey.IsPrimaryKey,
	NormalizedIndex.IndexName,
	NormalizedIndex.IndexColumnNamesOriginal,
	NormalizedIndex.IncludeColumnNamesOriginal,
	NormalizedIndex.IsClustered,
	NormalizedIndex.IsUnique,
	NormalizedIndex.IsPrimaryKey,
	NormalizedIndex.[FillFactor],
	NormalizedIndex.IgnoreDupKey,
	NormalizedIndex.IsPadded,
	NormalizedIndex.AllowRowLocks,
	NormalizedIndex.AllowPageLocks,
	NormalizedIndex.NoRecomputeStatistics,   
	NormalizedIndex.[Filegroup],
                               
	(
	CASE
		WHEN
			NormalizedIndex.IsPrimaryKey = 1
		THEN
			@PrimaryKeyNamePrefix
		ELSE
			@IndexNamePrefix
	END
	)
	+
	'_'
	+
	REPLACE(REPLACE([Table].name, '[', ''), ']', ''),
                                               
	NormalizedIndex.IndexColumnNames,
	NormalizedIndex.IncludeColumnNames
                                                                                                              
)

-- Create and populate the #IntelligentIndexWithTableAndBaseIndexName temporary table 

SELECT
	*
INTO
	#IntelligentIndexWithTableAndBaseIndexName
FROM
	_IntelligentIndexWithTableAndBaseIndexName

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IntelligentIndexWithTableAndBaseIndexName'

	SELECT * FROM #IntelligentIndexWithTableAndBaseIndexName

END

END

/*

*/

BEGIN	-- Create and populate the #IntelligentIndexRecommendationWithStatus temporary table 

;
WITH  

-- 

_IntelligentIndexRecommendationWithStatus
AS
(
SELECT
	SchemaName,
	TableName,
	IndexNameBase,
	IndexNameOriginal,
	IndexColumnNamesOriginal,
	IncludeColumnNamesOriginal,
	TableHasPrimaryKey,
	IsClusteredOriginal,
	IsUniqueOriginal,
	IsPrimaryKeyOriginal,
	FillFactorOriginal,
	IgnoreDupKeyOriginal,
	IsPaddedOriginal,
	AllowRowLocksOriginal,
	AllowPageLocksOriginal,
	NoRecomputeStatisticsOriginal,   
	FilegroupOriginal,

	-- IndexName is <PK, IX>_
	CAST(IndexNameBase + '_' + RIGHT('0000000000' + CONVERT(VARCHAR(10), ABS(CHECKSUM(IndexColumnNames + COALESCE(IncludeColumnNames, '')))), 10) AS SYSNAME) AS IndexName,
 
	LEFT(IndexColumnNames, DATALENGTH(IndexColumnNames) - 2) AS IndexColumnNames,
	IncludeColumnNames,
	ImprovementMeasure,

	(
	CASE
		WHEN
			(@RenameExistingIndexes = 1)
			AND
			(IndexNameOriginal IS NOT NULL)
			AND
			(IndexNameBase IS NOT NULL)
			AND
			IndexNameOriginal != IndexNameBase + '_' + RIGHT('0000000000' + CONVERT(VARCHAR(10), ABS(CHECKSUM(IndexColumnNames + COALESCE(IncludeColumnNames, '')))), 10)  
			AND
			(IndexColumnNamesOriginal = IndexColumnNames)
			AND
			(COALESCE(IncludeColumnNamesOriginal, '') = COALESCE(IncludeColumnNames, ''))
			AND
			(INDEXPROPERTY(OBJECT_ID(SchemaName + '.' + TableName), IndexNameBase + '_' + RIGHT('0000000000' + CONVERT(VARCHAR(10), ABS(CHECKSUM(IndexColumnNames + COALESCE(IncludeColumnNames, '')))), 10), 'IndexID') IS NULL)
		THEN
			CAST(1 AS BIT) 
		ELSE
			CAST(NULL AS BIT)
	END
	) AS RenameIndex,

	(
	CASE
		WHEN
			(IndexNameOriginal IS NOT NULL)
			AND
			(
			(IndexColumnNamesOriginal != IndexColumnNames)
			OR
			(DATALENGTH(COALESCE(IncludeColumnNamesOriginal, '')) != DATALENGTH(COALESCE(IncludeColumnNames, '')))
			OR
			(COALESCE(IncludeColumnNamesOriginal, '') != COALESCE(IncludeColumnNames, ''))
			)
			AND
			(IsPrimaryKeyOriginal = 0)
		THEN
			CAST(1 AS BIT)
		ELSE
			CAST(NULL AS BIT)
	END
	) AS DropIndex,

	(
	CASE
		WHEN
			(
			(IndexNameOriginal IS NULL)
			OR
			(IndexColumnNamesOriginal != IndexColumnNames)
			OR
			(DATALENGTH(COALESCE(IncludeColumnNamesOriginal, '')) != DATALENGTH(COALESCE(IncludeColumnNames, '')))
			OR
			(COALESCE(IncludeColumnNamesOriginal, '') != COALESCE(IncludeColumnNames, ''))
			)
			
			AND
			
			NOT EXISTS
				(
				SELECT
					*
				FROM
					#IntelligentIndexWithTableAndBaseIndexName AS X
				WHERE
					X.TableName = IntelligentIndexWithTableAndBaseIndexName.TableName
					AND
					X.IndexNameOriginal != IntelligentIndexWithTableAndBaseIndexName.IndexNameOriginal
					AND
					X.IndexColumnNames = IntelligentIndexWithTableAndBaseIndexName.IndexColumnNames 
					AND
					COALESCE(X.IncludeColumnNamesOriginal, '') = COALESCE(IntelligentIndexWithTableAndBaseIndexName.IncludeColumnNames, '')
				)
		THEN
			CAST(1 AS BIT)
		ELSE
			CAST(NULL AS BIT)
	END
	) AS CreateIndex
               
FROM
	#IntelligentIndexWithTableAndBaseIndexName AS IntelligentIndexWithTableAndBaseIndexName
)

-- Create and populate the #IntelligentIndexWithTableAndBaseIndexName temporary table 

SELECT
	*
INTO
	#IntelligentIndexRecommendationWithStatus
FROM
	_IntelligentIndexRecommendationWithStatus

-- Debugging Output

IF (@Debug = 1 AND @Verbose = 1)
BEGIN

	SELECT '#IntelligentIndexRecommendationWithStatus'

	SELECT * FROM #IntelligentIndexRecommendationWithStatus

END

END

/*

*/


BEGIN

-- Create and populate the #IntelligentIndexRecommendation temporary table 

SELECT
	SchemaName,
	TableName,
	IndexNameOriginal,
	IndexColumnNamesOriginal,
	IncludeColumnNamesOriginal,
	TableHasPrimaryKey,
	IsClusteredOriginal,
	IsUniqueOriginal,
	IsPrimaryKeyOriginal,
	FillFactorOriginal,
	IgnoreDupKeyOriginal,
	IsPaddedOriginal,
	AllowRowLocksOriginal,
	AllowPageLocksOriginal,
	NoRecomputeStatisticsOriginal,   
	FilegroupOriginal,
	IndexName,
	IndexColumnNames,
	IncludeColumnNames,
	ImprovementMeasure,
	RenameIndex,
	DropIndex,
	CreateIndex,

-- Build the SQL Statement to rename an existing index if necessary 

	(
	CASE
		WHEN
			RenameIndex = 1 
			AND
			IndexName != IndexNameOriginal
		THEN
	'USE [' + DB_NAME() + ']' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'GO' + CHAR(13) + CHAR(10)
		ELSE
			NULL
	END
	)
	+
	(
	CASE
		WHEN	
			RenameIndex = 1
			AND
			IndexName != IndexNameOriginal
		THEN
	CHAR(13) + CHAR(10) +
	'BEGIN TRY' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'EXEC' + CHAR(13) + CHAR(10) +
	CHAR(9) + 'sp_rename ' + CHAR(13) + CHAR(10) +
	CHAR(9) + CHAR(9) + 'N' + CHAR(39) + SchemaName + '.' + TableName + '.[' + IndexNameOriginal + ']' + CHAR(39) + ', ' + CHAR(13) + CHAR(10) +
	CHAR(9) + CHAR(9) + 'N' + CHAR(39) + IndexName + CHAR(39) + ', ' + CHAR(13) + CHAR(10) +
	CHAR(9) + CHAR(9) + 'N' + CHAR(39) + 'INDEX' + CHAR(39) + ';' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'PRINT ' + CHAR(39) + 'Rename Index ' + SchemaName + '.' + TableName + '.[' + IndexNameOriginal + '] to ' + IndexName + ' succeeded.' + CHAR(39) + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +                                
	'END TRY' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'BEGIN CATCH' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'PRINT ' + CHAR(39) + 'Rename Index ' + SchemaName + '.' + TableName + '.[' + IndexNameOriginal + '] to ' + IndexName + ' failed.' + CHAR(39) + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +                                
	'END CATCH' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'GO' + CHAR(13) + CHAR(10)
		ELSE
	''
	END
	)
	+
	CHAR(13) + CHAR(10)
	AS RenameIndexSQLStatement,

	-- Build the SQL Statement to drop an existing index if necessary 

	(
	CASE
		WHEN
			DropIndex = 1
		THEN
	'USE [' + DB_NAME() + ']' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'GO' + CHAR(13) + CHAR(10)
		ELSE
			NULL
	END
	)
	+
	(
	CASE
		WHEN
			DropIndex = 1
		THEN
	CHAR(13) + CHAR(10) +
	'BEGIN TRY' + CHAR(13) + CHAR(10) +    
	CHAR(13) + CHAR(10) +
	'DROP INDEX' + CHAR(13) + CHAR(10) +
	CHAR(9) + '[' + IndexNameOriginal + ']' + CHAR(13) + CHAR(10) +
	'ON' + CHAR(13) + CHAR(10) +
	CHAR(9) + SchemaName + '.' + TableName + ';' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'PRINT ' + CHAR(39) + 'Drop Index ' + SchemaName + '.' + TableName + '.[' + IndexNameOriginal + '] succeeded.' + CHAR(39) + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +                                
	'END TRY' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'BEGIN CATCH' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'PRINT ' + CHAR(39) + 'Drop Index ' + SchemaName + '.' + TableName + '.[' + IndexNameOriginal + '] failed.' + CHAR(39) + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +                
	'END CATCH' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'GO' + CHAR(13) + CHAR(10)
		ELSE
	''
	END
	)
	+
	CHAR(13) + CHAR(10)
	AS DropIndexSQLStatement,

	-- Build the SQL Statement to create a new index if necessary

	(
	CASE
		WHEN
			CreateIndex = 1
		THEN
	'USE [' + DB_NAME() + ']' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'GO' + CHAR(13) + CHAR(10)
		ELSE
			NULL
	END
	)
	+
	(
	CASE
		WHEN
			CreateIndex = 1
		THEN
	CHAR(13) + CHAR(10) +
	'BEGIN TRY' + CHAR(13) + CHAR(10) +    
	CHAR(13) + CHAR(10) +
	'CREATE NONCLUSTERED INDEX'+ CHAR(13) + CHAR(10) +
	CHAR(9) + '[' + IndexName + ']' + CHAR(13) + CHAR(10) +
	'ON' + CHAR(13) + CHAR(10) +
	CHAR(9) + SchemaName + '.' + TableName + CHAR(13) + CHAR(10) +
	CHAR(9) + '(' + CHAR(13) + CHAR(10) +
 -- CHAR(9) + LEFT(REPLACE(IndexColumnNames, '], ', '],' + CHAR(13) + CHAR(10) +  CHAR(9)), LEN(REPLACE(IndexColumnNames, '], ', '],' + CHAR(13) + CHAR(10) +  CHAR(9))) - 4) +
	CHAR(9) + REPLACE(IndexColumnNames, '], ', '],' + CHAR(13) + CHAR(10) +  CHAR(9)) +
	CHAR(13) + CHAR(10) +
	CHAR(9) + ')' + CHAR(13) + CHAR(10) +
	+
			(
			CASE
				WHEN
					IncludeColumnNames IS NOT NULL
				THEN
	CHAR(9) + 'INCLUDE' + CHAR(13) + CHAR(10) +
	CHAR(9) + '(' + CHAR(13) + CHAR(10) +
	CHAR(9) + REPLACE(IncludeColumnNames, '], ', '],' + CHAR(13) + CHAR(10) +  CHAR(9)) +
	CHAR(13) + CHAR(10) +
	CHAR(9) + ')' + CHAR(13) + CHAR(10)
			ELSE
	''
			END
			)
	+
	'WITH' + CHAR(13) + CHAR(10) +
	CHAR(9) + '(' + CHAR(13) + CHAR(10) +
	CHAR(9) + 'FILLFACTOR = ' + CONVERT(VARCHAR, (CASE WHEN FillFactorOriginal IS NULL THEN 100 WHEN FillFactorOriginal = 0 THEN 100 ELSE FillFactorOriginal END)) + ',' + CHAR(13) + CHAR(10) +
	CHAR(9) + 'SORT_IN_TEMPDB = OFF,' + CHAR(13) + CHAR(10) + 
	CHAR(9) + 'IGNORE_DUP_KEY = ' + (CASE WHEN IgnoreDupKeyOriginal IS NULL THEN 'OFF' WHEN IgnoreDupKeyOriginal = 1 THEN 'ON' ELSE 'OFF' END) + ',' + CHAR(13) + CHAR(10) +
	CHAR(9) + 'PAD_INDEX = ' + (CASE WHEN IsPaddedOriginal IS NULL THEN 'OFF' WHEN IsPaddedOriginal = 1 THEN 'ON' ELSE 'OFF' END) + ',' + CHAR(13) + CHAR(10) +
	CHAR(9) + 'ALLOW_PAGE_LOCKS = ' + (CASE WHEN AllowPageLocksOriginal IS NULL THEN 'OFF' WHEN AllowPageLocksOriginal = 1 THEN 'ON' ELSE 'OFF' END) + ',' + CHAR(13) + CHAR(10) +
	CHAR(9) + 'ALLOW_ROW_LOCKS = ' + (CASE WHEN AllowRowLocksOriginal IS NULL THEN 'OFF' WHEN AllowRowLocksOriginal = 1 THEN 'ON' ELSE 'OFF' END) + ',' + CHAR(13) + CHAR(10) +
	CHAR(9) + 'STATISTICS_NORECOMPUTE = ' + (CASE WHEN NoRecomputeStatisticsOriginal IS NULL THEN 'OFF' WHEN NoRecomputeStatisticsOriginal = 1 THEN 'ON' ELSE 'OFF' END) + ',' + CHAR(13) + CHAR(10) +
    CHAR(9) + 'ONLINE = OFF' + CHAR(13) + CHAR(10) +
	CHAR(9) + ')' + CHAR(13) + CHAR(10) +
	'ON' + CHAR(13) + CHAR(10) +
	CHAR(9) +  '[' + (CASE WHEN FilegroupOriginal IS NULL THEN (SELECT Name FROM Sys.Filegroups WHERE is_default = 1) ELSE FilegroupOriginal END) + '];' + CHAR(13) + CHAR(10) + 
	CHAR(13) + CHAR(10) +
	'PRINT ' + CHAR(39) + 'Create Index ' + SchemaName + '.' + TableName + '.[' + IndexName + '] succeeded.' + CHAR(39) + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +                                
	'END TRY' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'BEGIN CATCH' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'PRINT ' + CHAR(39) + 'Create Index ' + SchemaName + '.' + TableName + '.[' + IndexName + '] failed.' + CHAR(39) + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +                
	'END CATCH' + CHAR(13) + CHAR(10) +
	CHAR(13) + CHAR(10) +
	'GO' + CHAR(13) + CHAR(10)
		ELSE
	''
	END
	)
	+
	CHAR(13) + CHAR(10)
	AS CreateIndexSQLStatement,

	@ExecutionID AS ExecutionID,
	GETDATE() AS ExecutionDate
INTO
	#IntelligentIndexRecommendation
FROM
	#IntelligentIndexRecommendationWithStatus
WHERE
	1 = 1

-- Debugging Output

IF (@Debug = 1)
BEGIN

	SELECT '#IntelligentIndexRecommendation'

	SELECT
		SchemaName,
		TableName,
		IndexNameOriginal,
		IndexColumnNamesOriginal,
		IncludeColumnNamesOriginal,
		TableHasPrimaryKey,
		IsClusteredOriginal,
		IsUniqueOriginal,
		IsPrimaryKeyOriginal,
		FillFactorOriginal,
		IgnoreDupKeyOriginal,
		IsPaddedOriginal,
		AllowRowLocksOriginal,
		AllowPageLocksOriginal,
		NoRecomputeStatisticsOriginal,  
		FilegroupOriginal, 
		IndexName,
		IndexColumnNames,
		IncludeColumnNames,
		ImprovementMeasure,
		RenameIndex,
		DropIndex,
		CreateIndex,
	--	SQLStatement,
		ExecutionID,
		ExecutionDate
	FROM
		#IntelligentIndexRecommendation

END

-- Create the table if it does not already exist.

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'Utility' AND TABLE_NAME = N'IntelligentIndexRecommendation')
BEGIN

	SELECT
		*
	INTO
		Utility.IntelligentIndexRecommendation
	FROM
		#IntelligentIndexRecommendation
	WHERE
		1 = 2
		
END

END

/*

*/

-- Output the recommendations to the table which keeps a record of the recommendations.

INSERT INTO
	Utility.IntelligentIndexRecommendation
SELECT
	*
FROM
	#IntelligentIndexRecommendation

-- Debugging Output

IF (@Debug = 1  AND @Verbose = 1)
BEGIN

	SELECT 'IntelligentIndexRecomendation for this Execution in TableName, IndexColumnNames, IncludeColumnNames ORder'

	SELECT
		SchemaName,
		TableName,
		IndexNameOriginal,
		IndexColumnNamesOriginal,
		IncludeColumnNamesOriginal,
		TableHasPrimaryKey,
		IsClusteredOriginal,
		IsUniqueOriginal,
		IsPrimaryKeyOriginal,
		FillFactorOriginal,
		IgnoreDupKeyOriginal,
		IsPaddedOriginal,
		AllowPageLocksOriginal,
		AllowRowLocksOriginal,
		NoRecomputeStatisticsOriginal,   
		FilegroupOriginal, 
		IndexName,
		IndexColumnNames,
		IncludeColumnNames,
		ImprovementMeasure,
		RenameIndex,
		DropIndex,
		CreateIndex,
	--	SQLStatement,
		ExecutionID,
		ExecutionDate
	FROM
		Utility.IntelligentIndexRecommendation
	WHERE
		ExecutionID = @ExecutionID
	ORDER BY
		TableName,
		IndexColumnNames,
		IncludeColumnNames

END

-- Output the recommendations in the form of Rename, Drop and Create statements which could be executed to the Messages tab.

IF (@OutputScript = 1)
BEGIN
 
	SELECT 'Please see Messages Window for T-SQL Batch Statements'
 
-- Rename Index

	SELECT
		@RowNumber = 0

	PRINT '-- Begin Rename Indexes' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

	WHILE
		EXISTS
			(
			SELECT
				*
			FROM
				(
				SELECT
					ROW_NUMBER() OVER (ORDER BY TableName, IndexNameOriginal) AS RowNumber
				FROM
					Utility.IntelligentIndexRecommendation
				WHERE
					ExecutionID = @ExecutionID
					AND
					RenameIndexSQLStatement IS NOT NULL
				) AS Data
			WHERE
				RowNumber > @RowNumber
			)
	BEGIN
 
		SELECT TOP 1
			@RowNumber = RowNumber,
			@SQLStatement = RenameIndexSQLStatement
		FROM
			(   
			SELECT
				ROW_NUMBER() OVER (ORDER BY TableName,  IndexNameOriginal) AS RowNumber,
				*
			FROM
				Utility.IntelligentIndexRecommendation
			WHERE
				ExecutionID = @ExecutionID
				AND
				RenameIndexSQLStatement IS NOT NULL
			) AS Data

		WHERE
			RowNumber > @RowNumber
                               
		PRINT
			@SQLStatement
                                               
	END

	PRINT '-- End Rename Indexes' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

-- Drop Index

	SELECT
		@RowNumber = 0

	PRINT '-- Begin Drop Indexes' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

	WHILE
		EXISTS
			(
			SELECT
				*
			FROM
				(
				SELECT
					ROW_NUMBER() OVER (ORDER BY TableName, IndexNameOriginal) AS RowNumber
				FROM
					Utility.IntelligentIndexRecommendation
				WHERE
					ExecutionID = @ExecutionID
					AND
					DropIndexSQLStatement IS NOT NULL
				) AS Data
			WHERE
				RowNumber > @RowNumber
			)
	BEGIN
 
		SELECT TOP 1
			@RowNumber = RowNumber,
			@SQLStatement = DropIndexSQLStatement
		FROM
			(   
			SELECT
				ROW_NUMBER() OVER (ORDER BY TableName, IndexNameOriginal) AS RowNumber,
				*
			FROM
				Utility.IntelligentIndexRecommendation
			WHERE
				ExecutionID = @ExecutionID
				AND
				DropIndexSQLStatement IS NOT NULL
			) AS Data

		WHERE
			RowNumber > @RowNumber
                               
		PRINT
			@SQLStatement
                                               
	END 

	PRINT '-- End Drop Indexes' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

-- Create Index

	SELECT
		@RowNumber = 0

	PRINT '-- Begin Create Indexes' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

	WHILE
		EXISTS
			(
			SELECT
				*
			FROM
				(
				SELECT
					DENSE_RANK() OVER (ORDER BY TableName, IsPrimaryKeyOriginal DESC, IndexName) AS RowNumber
				FROM
					Utility.IntelligentIndexRecommendation
				WHERE
					ExecutionID = @ExecutionID
					AND
					CreateIndexSQLStatement IS NOT NULL
				) AS Data
			WHERE
				RowNumber > @RowNumber
			)
	BEGIN
 
		SELECT TOP 1
			@RowNumber = RowNumber,
			@SQLStatement = CreateIndexSQLStatement
		FROM
			(   
			SELECT
				DENSE_RANK() OVER (ORDER BY TableName, IsPrimaryKeyOriginal DESC, IndexName) AS RowNumber,
				*
			FROM
				Utility.IntelligentIndexRecommendation
			WHERE
				ExecutionID = @ExecutionID
				AND
				CreateIndexSQLStatement IS NOT NULL
			) AS Data

		WHERE
			RowNumber > @RowNumber
                               
		PRINT
			@SQLStatement
                                       
	END

	PRINT '-- End Create Indexes' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

END
 
/*
 
*/
 
DROP TABLE #IndexesForAnalysis

DROP TABLE #IndexesForAnalysisColumn

DROP TABLE #IndexesForAnalysisIncludeColumn
 
DROP TABLE #IntelligentIndex
 
DROP TABLE #IntelligentIndexWithExistingIndexMatch

DROP TABLE #IntelligentIndexWithoutExistingIndexMatch

DROP TABLE #IntelligentIndexMappedToExistingIndex
 
DROP TABLE #IntelligentIndexMappedToExistingIndexWithStatus
 
DROP TABLE #IntelligentIndexMappedToExistingIndexWithStatusAndNames

DROP TABLE #IntelligentIndexWithTableAndBaseIndexName

DROP TABLE #IntelligentIndexRecommendationWithStatus

DROP TABLE #IntelligentIndexRecommendation
 
RETuRN

END

Data Warehousing

Dimensional Storage & Modeling for Volatile Attribute Taxonomies

There are line of business scenarios where dimensional attribute taxonomies within data warehouses (DW) are volatile. That is, the attributes themselves, not their values, are subject to change. From a classical relational dimensional modeling approach, this means the schema of the entities representing the dimensions of a DW are not fixed. Attributes and their relationship with other attributes (hierarchies) are subject to being added or removed.

This situation, which is common within the apparel industry, makes maintaining traditional “horizontal” dimensional data models (as the primary data storage and management repository) tedious and problematic at best, especially from the perspective of the Extract-Load-Transform-Load (ELTL) processes which are critical to the function of the DW.

So what’s the solution? I’m a staunch advocate of an Entity-Attribute-Value (EAV) or “vertical” data model. This can is used to describe entities where the number of attributes that can be used to describe such is potentially vast, indeterminate and volatile, but the number that will actually apply to a given entity is relatively modest.

Many will object to an EAV approach , immediately  classifying such a data model as an anti-pattern which can lead to longer development times, poor use of database resources and more complex queries when compared to a horizontal dimensional modeling approach. In my opinion, this is uniformed, assumptive and myopic thinking. An EAV data model does not intrinsically mean an architecture is absent the use of a horizontal data model. Rather, a well rationalized use of EAV implements both vertical and horizontal data models, perhaps organized into separate “namespaces” or “schemas” with the DW.

EAV data models are, by convention, rich in “metadata”. It is this metadata which is crucial to overcoming most all objections to the EAV approach. With proper consideration given to the metadata requirements, there should exist sufficient information in the vertical schema to:

  • develop a standard dimensional attribute ELTL process that eliminates the need for traditional, tightly-coupled ELTL implementations which are dimension specific
  • use code / object generation techniques to dynamically “pivot” the data within each type of entity described into it’s horizontal equivalent
  • support the concept of “slowly-changing dimension schemas” and produce temporally unique versions of horizontal dimensional perspectives

Whether the resultset produced from a given pivot needs to be persisted (in the form of a table or indexed  view) or remains dynamic (in the form of a non-indexed view) is largely a function of the complexity of the pivoting query and it’s performance.  To be certain, implementing the code / object generator is no trivial task. However, if implemented properly, the flexibility to support both persisted and dynamic horizontal dimensional perspectives can be readily achieved. Furthermore, if a persisted tabular perspective is required, it can be created as a final stage of dimensional attributes ELTL associated with a given type of entity.

It is this pivoting methodology and support that turns EAV data modeling from an anti-pattern into a robust, flexible and schema-independent solution. Horizontal dimensional perspectives are at the core of high performance, logical and ‘business-friendly’ dimensional modeling championed by Ralph Kimball.

I believe this architecture presents a compelling alternative to deal with scenarios becoming more commonplace, especially as the need to integrate un- and semi-structured data (i.e. social media data feeds) into today’s business analytics environments.

If you’d like to further explore this topic, please share your comments. If there’s sufficient interest, I’ll do a deeper-dive into some EAV implementation specifics.

Project Management, Software Development

Vanquishing The Enemy Within

Does this scenario sound all too familiar? As programmers, many of us occasionally suffer from a “god complex,” leading us to believe we can resurrect a project, bringing it back like the mythical Phoenix from the ashes, or lay our hands on a project gone bad and save it from ultimate failure. Some of us might be able to do these things routinely and be handsomely rewarded for it. But the long-term result can prove less than miraculous: a continued mismanagement of the software development life cycle, perpetuating the habits that produce miserable programmers and poor software.

We work in a trade like no other in history. We work in the ethereal. Our creations have no manifestation in the physical realm. The medium we toil with is without parallel. We are artists, perhaps some of the most talented artists ever. And artists become so absorbed in their creative process that they become oblivious to everything else around them. So we become our own worst enemies.

We moan about long hours, overwork, burnout, and the stress in our lives that comes from participating in the projects we deem mismanaged by those we perceive incompetent and unthinking. And sadly, more often than not, we do nothing constructive and concrete to improve our plight. That’s not because we don’t know how, but instead because we are so absorbed in creation that we can’t see the true source of our ills.

I have spent hours reading the project management and software development treatises of gurus such as Jim McCarthy and Steve McConnell. As a result, I’ve concluded that we as programmers manifest and facilitate the practices leading to our misery and poor-quality software through neglect and dereliction of our responsibilities.

Management, in the vast majority of cases, is not in a position to do the work for which we are hired. We are supposed to be the experts. But we do little to assert our expertise or behave as if we had it. So we bemoan our suffering.

Generally speaking, management is not sadistic in wielding authority, nor do they want to see projects fail. They’re not ignorant. What we call ignorance is often our perception of another’s lack of knowledge. Yet, those perceived ignorant often don’t know what they don’t know, and don’t know that they need to know it. And we encourage them to continue their ill-informed behavior by accepting their untenable project schedules and inadequate specifications.

How do we change? I don’t have all the answers, but I’m reminded of Midnight Madness at VBITS’97 San Francisco, when respected software development innovator and “father of Visual Basic” Alan Cooper said, “We [in this room] hold the means of production right here… .” while raising his hands before him. His premise was clear: We should neither fear for our jobs nor feel compelled to yield to the extortive techniques of management when we’re faced with projects doomed to misery or failure. And that we can begin by learning to “just say no.”

That’s just the beginning. We must dedicate a significant portion of our energies and expertise to cooperatively educate management in the processes of a successful software development effort. If management doesn’t want to cooperate, look for another employer. I know that sounds harsh. It is. But you need to stand your ground.

We as programmers must accept that the power to change our outcomes and reshape our lives is ours alone. We must exercise that power, because it is our responsibility as professionals, not only to our craft, our projects, and our employers, but to ourselves. The future of the industry and our quality of life, individually and collectively as programmers, depends on it.

Vanquishing The Enemy Within