SQL Server Index Size Estimator

Purpose

Estimates the storage footprint of a proposed nonclustered index before creating it. Useful during index design to understand the storage trade-off, particularly on large tables where an incorrectly sized index can consume tens of gigabytes unexpectedly.

The script takes the proposed index column list (key columns and INCLUDE columns), maps each column to its byte width using sys.columns and sys.types, adds index row overhead, and projects the total size against the table’s current row count adjusted for the fill factor.

Two result sets are returned:

1. The estimated per-row byte size for the index entry
2. The total estimated index size in MB against the current row count

Supported Platforms and Versions

SQL Server 2012 and later (on-premises and SQL Server on Azure VMs). Azure SQL Database is supported.

Required Permissions

SELECT on sys.columns, sys.types, sys.tables, and sys.partitions. These are available to any user with VIEW DATABASE STATE or db_datareader on the system views.

Preconditions

• The target table must already exist in the database.
• Statistics should be reasonably current so sys.partitions.rows reflects the actual row count. Run UPDATE STATISTICS on the table if the last update is stale.
• The script uses max_length for fixed-length types and variable-length types. For varchar/nvarchar, this reflects the column definition maximum, not the average actual length. The estimate will be conservative (high) for columns that are typically much shorter than their maximum.

Risk Level

Low. Read-only. No index is created. No changes to schema or data.

Script

-- ============================================================
-- SQL Server Index Size Estimator
-- Permissions : VIEW DATABASE STATE (or db_datareader on system views)
-- Risk        : Read-only — estimates only, no index is created
-- ============================================================

-- === CONFIGURATION ===
DECLARE @fill_factor int    = 90;           -- fill factor percentage (default: 90)
DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

-- === INDEX DEFINITION ===
-- List all key columns (is_included = 0) and INCLUDE columns (is_included = 1).
-- Match column names exactly as they appear in sys.columns.
DECLARE @IndexColumns TABLE
(
    column_name sysname,
    is_included bit
);

INSERT INTO @IndexColumns (column_name, is_included)
VALUES
    (N'OrderDate',   0),    -- key column
    (N'CustomerID',  0),    -- key column
    (N'Status',      1);    -- INCLUDE column

-- === RESULT 1: Estimated per-row index size ===
WITH col_sizes AS
(
    SELECT
        c.object_id,
        SUM(
            CASE
                WHEN ty.name IN ('varchar', 'char')              THEN c.max_length
                WHEN ty.name IN ('nvarchar', 'nchar')            THEN c.max_length * 2
                WHEN ty.name = 'int'                             THEN 4
                WHEN ty.name = 'bigint'                          THEN 8
                WHEN ty.name = 'smallint'                        THEN 2
                WHEN ty.name = 'tinyint'                         THEN 1
                WHEN ty.name IN ('datetime', 'smalldatetime')    THEN 8
                WHEN ty.name = 'datetime2'                       THEN 8
                WHEN ty.name = 'uniqueidentifier'                THEN 16
                ELSE c.max_length
            END
        ) AS row_bytes
    FROM sys.columns AS c
    JOIN sys.types AS ty
        ON c.user_type_id = ty.user_type_id
    JOIN @IndexColumns AS ic
        ON ic.column_name = c.name
    WHERE c.object_id = OBJECT_ID(QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name))
    GROUP BY c.object_id
)
SELECT
    row_bytes + 7 AS estimated_index_row_bytes  -- +7 bytes: index row overhead
FROM col_sizes;

-- === RESULT 2: Estimated total index size ===
SELECT
    t.name                                          AS table_name,
    SUM(p.rows)                                     AS row_count,
    (cs.row_bytes + 7)                              AS row_size_bytes,
    CEILING(
        (cs.row_bytes + 7) * SUM(p.rows)
        / (@fill_factor / 100.0)
    ) / 1024.0 / 1024.0                             AS estimated_index_size_mb
FROM sys.tables AS t
JOIN sys.partitions AS p
    ON p.object_id = t.object_id
CROSS APPLY
(
    SELECT SUM(col_bytes) AS row_bytes
    FROM
    (
        SELECT
            CASE
                WHEN ty.name IN ('varchar', 'char')              THEN c.max_length
                WHEN ty.name IN ('nvarchar', 'nchar')            THEN c.max_length * 2
                WHEN ty.name = 'int'                             THEN 4
                WHEN ty.name = 'bigint'                          THEN 8
                WHEN ty.name = 'smallint'                        THEN 2
                WHEN ty.name = 'tinyint'                         THEN 1
                WHEN ty.name IN ('datetime', 'smalldatetime')    THEN 8
                WHEN ty.name = 'datetime2'                       THEN 8
                WHEN ty.name = 'uniqueidentifier'                THEN 16
                ELSE c.max_length
            END AS col_bytes
        FROM sys.columns AS c
        JOIN sys.types AS ty
            ON c.user_type_id = ty.user_type_id
        JOIN @IndexColumns AS ic
            ON ic.column_name = c.name
        WHERE c.object_id = t.object_id
    ) AS d
) AS cs
WHERE t.name = @table_name
  AND p.index_id IN (0, 1)    -- heap (0) or clustered index (1) to avoid double-counting
GROUP BY t.name, cs.row_bytes;

Safe Execution Guidance

1. Set @schema_name and @table_name to the target table.
2. Set @fill_factor to the value you intend to use when creating the index (default 90).
3. Populate @IndexColumns with the exact column names as they appear in the table. Key columns first (is_included = 0), then INCLUDE columns (is_included = 1). The script does not use is_included in the size calculation — all columns contribute to the row size regardless — but documenting intent is good practice.
4. Run in the context of the database containing the table.
5. The estimate is based on maximum column widths for variable-length types. For tables where varchar columns are substantially shorter on average than their defined maximum, the actual index size will be smaller.

Expected Output

Result 1 — single row:

Result 2 — single row:

Limitations

• Variable-length columns: varchar, nvarchar, and varbinary estimates use max_length (the column definition maximum). Average actual data may be much smaller. For more accurate estimates on varchar-heavy indexes, replace c.max_length with an average from DBCC SHOW_STATISTICS or a manual AVG(LEN(column)) query.
• Overhead not included: The estimate covers the index row data and a 7-byte row header. It does not account for page headers, B-tree navigation pages, or the clustered index key appended to every nonclustered index entry (typically 4–16 bytes depending on key type).
• Row count currency: sys.partitions.rows reflects the last statistics update, not the live row count. The estimate will drift on tables with significant inserts or deletes since the last UPDATE STATISTICS.

Rollback Steps

Not applicable — read-only script.

Official References

• Estimate the Size of a Nonclustered Index
• sys.columns
• sys.partitions