Return to Answer

It depends on the size of the values in the column you are adding. As David suggests the most accurate way of knowing is to create an index in a dev or test environment and see what effect it has there.

You can estimate though. If the added column is 8 bytes long (a datetime column for instance) and there are 100M rows, then you can expect it to add approximately 800,000,000 bytes to the index's leaf pages. If it is a variable width column then you need to estimate from likely data lengths, or if you can run a query against the production DB you can read it from real data using SELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.

This only accounts for the extra data added to the leaf pages in the index, but it should be accurate enough as an estimate as that will be bar far the largest factor. There will be a little extra in non-leaf pages for instance.

Also the above does not take compression into account, if that is enabled for your index. Compressed data can be much more difficult to model, so in that case you are back to testing by creating the index on realistic data as the only really accurate way to go.

Without knowing your table/index definitions it is not possible to give a more precise answer.

It depends on the size of the values in the column you are adding. As David suggests the most accurate way of knowing is to create an index in a dev or test environment and see what effect it has there.

You can estimate though. If the added column is 8 bytes long (a datetime column for instance) and there are 100M rows, then you can expect it to add approximately 800,000,000 bytes to the index's leaf pages. If it is a variable width column then you need to estimate from likely data lengths, or if you can run a query against the production DB you can read it from real data using SELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.

This only accounts for the extra data added to the leaf pages in the index, but it should be accurate enough as an estimate as that will be by far the largest factor. There will be a little extra space taken by non-leaf pages too.

Also the above does not take compression into account, if that is enabled for your index. Compressed data can be much more difficult to model, so in that case you are back to testing by creating the index on realistic data as the only really accurate way to go.

Without knowing your table/index definitions it is not possible to give a more precise answer.

It depends on the size of the values in the column you are adding. As David suggests the most accurate way of knowing is to create an index in a dev or test environment and see what effect it has there.

You can estimate though. If the added column is 8 bytes long (a datetime column for instance) and there are 100M rows, then you can expect it to add approximately 800,000,000 bytes to the index. If it is a variable width column then you need to estimate from likely data lengths, or if you can run a query against the production DB you can read it from real data using SELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.

This only accounts for the extra data added to the leaf pages in the index, but it should be accurate enough as an estimate. There will be a little extra in branch pages, and if it is a particularly wide index already there might be more still if the change is enough to significantly alter the number of index rows fit in each page^[†].

^{[†] to give an extreme example: if the non-clustered index currently is/averages 800 bytes (including the clustered index columns) per row then 10 index rows will fit on each page, int(8060/800)=10, adding a datetime column means only 9 will, int(8060/808)=9, which is 10% difference in the number of pages required and over many millions of rows that could be notiable.}

It depends on the size of the values in the column you are adding. As David suggests the most accurate way of knowing is to create an index in a dev or test environment and see what effect it has there.

You can estimate though. If the added column is 8 bytes long (a datetime column for instance) and there are 100M rows, then you can expect it to add approximately 800,000,000 bytes to the index's leaf pages. If it is a variable width column then you need to estimate from likely data lengths, or if you can run a query against the production DB you can read it from real data using SELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.

This only accounts for the extra data added to the leaf pages in the index, but it should be accurate enough as an estimate as that will be bar far the largest factor. There will be a little extra in non-leaf pages for instance.

Also the above does not take compression into account, if that is enabled for your index. Compressed data can be much more difficult to model, so in that case you are back to testing by creating the index on realistic data as the only really accurate way to go.

Without knowing your table/index definitions it is not possible to give a more precise answer.

It depends on the size of the values in the column you are adding. As David suggests the most accurate way of knowing is to create an index in a dev or test environment and see what affect it has there.

You can estimate though. If the added column is 8 bytes long (a datetime column for instance) and there are 100M rows, then you can expect it to add approxumately 800,000,000 bytes to the index. If it is a variable width column then you need to estimate from likely data lengths, or if you can run a query against the production DB you can read it from real data using SELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.

This only accounts for the extra data added to the leaf pages in the index, there will be a little extra in branch pages, and if it is a particularly wide index already there might be more still if the change is enough to significantly alter the number of rows fit in each page. But it should be accurate enough as an estimate.

It depends on the size of the values in the column you are adding. As David suggests the most accurate way of knowing is to create an index in a dev or test environment and see what effect it has there.

You can estimate though. If the added column is 8 bytes long (a datetime column for instance) and there are 100M rows, then you can expect it to add approximately 800,000,000 bytes to the index. If it is a variable width column then you need to estimate from likely data lengths, or if you can run a query against the production DB you can read it from real data using SELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.

This only accounts for the extra data added to the leaf pages in the index, but it should be accurate enough as an estimate. There will be a little extra in branch pages, and if it is a particularly wide index already there might be more still if the change is enough to significantly alter the number of index rows fit in each page^[†].

^{[†] to give an extreme example: if the non-clustered index currently is/averages 800 bytes (including the clustered index columns) per row then 10 index rows will fit on each page, int(8060/800)=10, adding a datetime column means only 9 will, int(8060/808)=9, which is 10% difference in the number of pages required and over many millions of rows that could be notiable.}