/*
	Author	:	Brahmanand Shukla (SQLServerCarpenter.com)
	Date	:	27-May-2022
	Purpose	:	This procedure will list down all the indexes (Clustered as well Nonclustered Indexes) whose first key column having poor cardinality.
				The cardinality is accepted as an input to the procedure. 

				Based on the input cardinality, the procedure fetches the indexes whose first key column having cardinality lower than what is supplied in the ascending order of their respective cardinality.
				If cardinality is not supplied: 
					* The default input cardinality value is NULL; 
					* It means fetch all the ineffctive indexes with Cardinality lower than 40 (this can be changed, if needed) in the ascending order of their cardinality value.

				Indexes may be an overhead if created on column(s) with poor cardinality.
				It is now up to you to decide which one makes sense, and which doesn’t. 
				Take the decision accordingly and delete the ones that do not make sense.  
				This procedure may be helpful in performance tuning assignments.

				Lower the value of cardinality means "Poor Cardinality" and indexes having such column as the first key column would be most likely an "Ineffective Index". 
				Higher the value of cardinality means "Good Cardinality" and indexes having such column as the first key column would be most likely an "Effective index".

	Example: 

	EXEC usp_SQLServerCarpenter_Tools_Estimate_Ineffective_Indexes

	EXEC usp_SQLServerCarpenter_Tools_Estimate_Ineffective_Indexes
		@Cardinality		=	100

	*** Notes: ***
	Before you create this procedure and start using it, make sure usp_SQLServerCarpenter_Tools_Estimate_Cardinality is created, ran, and the tbl_Cardinality table has data in it.
	https://sqlservercarpenter.com/2022/07/11/usp_sqlservercarpenter_tools_estimate_cardinality/
*/
CREATE OR ALTER PROCEDURE usp_SQLServerCarpenter_Tools_Estimate_Ineffective_Indexes
(
	@Cardinality		TINYINT = NULL	/*	It can be any value between 0 to 100. Default is NULL */
)
AS
	BEGIN
		SET NOCOUNT ON;

		SELECT S.name AS [SchemaName]
			, T.name AS [TableName]
			, I.type_desc AS [IndexType]
			, I.name AS [IndexName]
			, C.name AS [ColumnName]
			, D.Cardinality
			, ROW_NUMBER() OVER(ORDER BY D.Cardinality ASC) AS [IneffectivenessRank]
		FROM sys.indexes I
			INNER JOIN sys.index_columns IC
				ON IC.object_id = I.object_id
				AND IC.index_id = I.index_id
			INNER JOIN sys.tables T
				ON T.object_id = I.object_id
			INNER JOIN sys.schemas S
				ON S.schema_id = T.schema_id
			INNER JOIN sys.columns C
				ON C.object_id = IC.object_id
				AND C.column_id = IC.column_id
			INNER JOIN tbl_Cardinality D
				ON D.ObjectID = T.object_id
				AND PARSENAME(D.ColumnName, 1) = C.name
		WHERE I.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
			AND I.is_hypothetical = 0
			AND IC.is_included_column = 0
			AND IC.key_ordinal = 1
			AND ((@Cardinality IS NULL AND D.Cardinality IS NOT NULL AND D.Cardinality < 40) 
				OR (@Cardinality IS NOT NULL AND D.Cardinality IS NOT NULL AND D.Cardinality < @Cardinality))
	END