Estimating the Size of an Index

If you’re creating a new index in SQL Server, it is good practice to make sure you have enough space on your drive to do so. You’ll not only need the space to hold it, but double the space as SQL Server builds it on temporary disk space. (https://msdn.microsoft.com/en-us/library/ms191183.aspx) So if your index is going to be 1 GB, then you will need up to 2 GB to make it. By the way, when rebuilding, you’ll need triple the space, as SQL Server also retains the old index while it creates a new one.

While figuring out the size of an existing index is easy, estimating the space of an index that you haven’t created yet is not so obvious.

This script will make it easy.

-- Each column must be listed in the parens.
-- SUM(LEN(columnName))
-- FROM must refer to the table where the columns are
SELECT
(
 SUM(LEN('Col1')) +
 SUM(LEN('Col2')) +
 SUM(LEN('Col3'))
)
 / 1073741824.0 AS [SizeInGB] -- This converts bytes to GB
FROM TableName

The output will be the size of the index, in GB. Creating the index will need that much space in tempdb and that much space in your MDF or NDF file.