Space used by Primary Key Constraint (Index) vs Data space used by table itself

• etl2016

  Default port

  Points: 1459

  More actions

  May 14, 2019 at 8:09 pm

  #3642093

  Hi,

  I have a table which has 3 Indexes.    These are  (a) the PK   (b) a UNIQUE Non-Clustered Index (c) finally, a Non-Clustered Index.

  I am examining the space used the table itself (sp_spaceused) versus the space used by each of these 3 Indexes (sys.allocation_units)

  What I find is, the Data space used by the table is nearly the same as the   Primary Key size, given the fact that PK being a UNIQUE CLUSTERED Index, is the physical arrangement of the data, so it makes sense them being the same. Said otherwise, Size of the table and the Size of PK index are two different labels pointing at the same physical storage space.

  So, when one asks what is the space used by the indexes of a table, we actually mean, what is the space consumed by indexes other than PK.  Is this correct?

  thank you

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  May 14, 2019 at 9:18 pm

  #3642119

  A clustered index is the table itself.  The PK may or may not be clustered.

  So, technically speaking, "index space" in your context would mean non-clustered indexes, which may or may not include the PK index.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.