Suggest Index Column Order

You know which columns you want in an index, but in what order? This stored procedure takes a table and a comma-separated column list (using the SplitArray function), counts the distinct values in each column, and generates a CREATE INDEX statement with the columns ordered by selectivity - most selective first by default, or least first if you prefer. Each column is annotated with its distinct count so you can judge the recommendation.

Example: EXEC dbo.SuggestIndexColumnOrder @SchemaName = 'dbo', @TableName = 'Orders', @Columns = 'CustomerID, OrderDate, Status'

CREATE PROCEDURE [dbo].[SuggestIndexColumnOrder]
(
	@SchemaName sysname,
	@TableName sysname,
	@Columns varchar(max),
	@MostSelectiveFirst bit = 1
) AS
BEGIN--PROCEDURE
	SET NOCOUNT ON;
	
	DECLARE @ColumnName sysname
	DECLARE @TypeName sysname
	DECLARE @DistinctCount bigint
	DECLARE @PrevDistinctCount bigint
	DECLARE @tSQL varchar(max)
	
	SELECT
		Value AS ColumnName,
		Cast(0 as BigInt) AS DistinctCount
	INTO
		#IdxColumns
	FROM
		dbo.SplitArray(@Columns, ',')
	
	DECLARE IdxColLoop CURSOR FAST_FORWARD FOR
		SELECT
			ColumnName,
			SysT.name as TypeName
		FROM
			sys.objects AS SysO
		INNER JOIN sys.schemas as SysS
			ON SysS.[schema_id] = SysO.[schema_id]
		INNER JOIN sys.columns as SysC
			ON SysC.[object_id] = SysO.[object_id]
		INNER JOIN #IdxColumns as IdxColumns
			ON IdxColumns.ColumnName = SysC.name
		INNER JOIN sys.types as SysT
			ON SysT.system_type_id = SysC.system_type_id
		WHERE
			SysO.name = @TableName
			AND SysS.name = @SchemaName
	OPEN IdxColLoop
	
	FETCH FROM IdxColLoop INTO @ColumnName, @TypeName
	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		--------------------------------------------------------------------------------------------------------------
		SET @tSQL =
			'UPDATE #IdxColumns'
			+ ' SET DistinctCount = (SELECT COUNT(DISTINCT '
			+ 'ISNULL([' + @ColumnName + '], '
			+ CASE 
				WHEN @TypeName IN ('uniqueidentifier')
				THEN '''00000000-0000-0000-0000-000000000000'''
				WHEN @TypeName IN ('text', 'ntext')
				THEN '''''
				WHEN @TypeName IN ('datetime', 'smalldatetime')
				THEN '''1/1/1900'''
				WHEN @TypeName IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric', 'smallmoney', 'bigint')
				THEN '0'
				WHEN @TypeName IN ('image', 'binary', 'timestamp')
				THEN '0'
				WHEN @TypeName IN ('varbinary', 'varchar', 'char', 'nvarchar', 'nchar', 'xml', 'sysname')
				THEN '''<NULL>'''
			END				
			+ '))'
			+ ' FROM [' + @SchemaName + '].[' + @TableName + '])'
			+ ' WHERE ColumnName = ''' + @ColumnName + ''''
		EXEC(@tSQL)
		--------------------------------------------------------------------------------------------------------------
		FETCH FROM IdxColLoop INTO @ColumnName, @TypeName
	END--WHILE
	CLOSE IdxColLoop DEALLOCATE IdxColLoop
	
	IF @MostSelectiveFirst = 1
	BEGIN--IF
		DECLARE IdxColLoop CURSOR FAST_FORWARD FOR
			SELECT
				ColumnName,
				DistinctCount
			FROM
				#IdxColumns
			ORDER BY
				DistinctCount DESC
	END ELSE BEGIN--IF
		DECLARE IdxColLoop CURSOR FAST_FORWARD FOR
			SELECT
				ColumnName,
				DistinctCount
			FROM
				#IdxColumns
			ORDER BY
				DistinctCount ASC
	END--IF
	OPEN IdxColLoop
	
	SET @tSQL = 'CREATE /*UNIQUE*/ /*NONCLUSTERED*/ INDEX [IX_<INDEX_NAME>] ON [' + @SchemaName + '].[' + @TableName + ']' + CHAR(13) + CHAR(10)
	SET @tSQL = @tSQL + '('

	FETCH FROM IdxColLoop INTO @ColumnName, @DistinctCount
	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		--------------------------------------------------------------------------------------------------------------
		
		SET @PrevDistinctCount = @DistinctCount
		SET @tSQL = @tSQL + CHAR(13) + CHAR(10) + Char(9) + '[' + @ColumnName + '] ASC'
		--------------------------------------------------------------------------------------------------------------
		FETCH FROM IdxColLoop INTO @ColumnName, @DistinctCount
		IF(@@FETCH_STATUS = 0)
		BEGIN--IF
			SET @tSQL = @tSQL + ','
		END--IF
		
		SET @tSQL = @tSQL + ' --' + Convert(VarChar(50), @PrevDistinctCount)

	END--WHILE
	CLOSE IdxColLoop DEALLOCATE IdxColLoop
	SET @tSQL = @tSQL + CHAR(13) + CHAR(10) + ')'

	PRINT @tSQL
	
	DROP TABLE #IdxColumns

END--PROCEDURE
GO