Is Table Space = Data Space + Index Space?

• curious_sqldba

  SSC-Dedicated

  Points: 36502

  More actions

  March 16, 2011 at 12:08 pm

  #157487

  Is space occupied by table = Data Space + Index Space? Is deleting the index only way to reduce Index Space?

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  March 16, 2011 at 12:37 pm

  #1299583

  To better understand the space taken by indexes, and thier usefulness in finding data in a table may I suggest your read this article:

  http://www.simple-talk.com/sql/performance/lipoaspiration-in-your-sql-server-database/?utm_source=simpletalk&utm_medium=email-main&utm_content=Lipaspiration-20110307&utm_campaign=SQL

  If by "data space" you mean the space taken by the actual values in a column, as the old saying goes "that depends". If for example a column will allow nulls and a non null value is entered into those columns, space in addition to the actual value is required. Tolearn more about this refere to the QOD on March 7, 2011 or use this link

  http://www.sqlservercentral.com/questions/Database+Design/72317/

  or read this article:

  http://sqlskills.com/BLOGS/KIMBERLY/category/SQL-Server-2005.aspx

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  March 16, 2011 at 12:47 pm

  #1299590

  Good article recommendation.

  Table Space is largely index space + data space. But deleting an index is not necessarily the solution if you want to free space. What is the root issue and your end-goal?

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events