Link Search Menu Expand Document


sp_estindex constellation logo


In complex environments, sometimes the best ways to create indexes aren’t the most obvious. Table size, underlying statistics, missing index recommendations, fill factors, and uniqueness are just some of the factors that need to be considered. But these can be difficult to aggregate and experiment with since index creation has a very real cost of time and compute power in large databases.

To make index planning easier, sp_estindex gives you statistics on how an index would look without having to actually create it!


@SchemaNameSYSNAME(128)noTarget schema of the index’s table. Default is ‘dbo’.
@TableNameSYSNAME(128)noTarget table for the index. Default is current database.
@DatabaseNameSYSNAME(128)noTarget database of the index’s table.
@IndexColumnsNVARCHAR(2048)noComma separated list of key columns.
@IncludeColumnsNVARCHAR(2048)noOptional comma separated list of include columns.
@IsUniqueBITnoWhether or not the index is UNIQUE. Default is 0.
@FilterNVARCHAR(2048)noOptional filter for the index.
@FillFactorTINYINTnoOptional fill factor for the index. Default is 100.
@VarcharFillPercentTINYINTnoOptional estimated fill percent of data in variable length columns. Default is 100.
@VerboseBITnoShow intermediate variables used in size calculations. Default is 0.
@SqlMajorVersionTINYINTnoFor unit testing only.


EXEC [dbo].[sp_estindex] @SchemaName = 'dbo', @tableName = 'Marathon', @IndexColumns = 'racer_id, finish_time';
EXEC [dbo].[sp_estindex] @tableName = 'Marathon', @IndexColumns = 'racer_id, finish_time', @Filter = 'WHERE racer_id IS NOT NULL', @FillFactor = 90;


For [Sales].[Invoices] in WorldWideImporters:

Note: There is no missing index match in this example, so the penultimate result set is empty.

sp_estindex output