Home / server / Questions / 96539
Accepted
user25414
Asked: 2009-12-22 18:48:51 +0800 CST

SQL Server 2K: The calculation of index size

  • 772

Based on the info using sp_spaceused

rows           reserved    data         index_size  unused

7703020     497944 KB   497968 KB     16 KB       -40 KB

I create an noncluster index with a column of numeric(18, 0) that is 9 bytes in size; then the index_size turns into 153064 KB.

How is the # calculated?

Thanks.

sql-server

1 Answers

  1. Best Answer

    That looks like you don't have ANY indexes on that table, at all, for the index size to only be 16kb.

    After creating your index, each row is consuming a tad over 20 bytes in index space. For 7.7 million rows that seems fairly normal.

    For detailed information on how to estimate the index size, see this article here.

    Man, I would hate to be running any queries over almost 8 million rows with no indexes...

    • 0