Disk Used by Tables

Question

Disk Used by Tables

surma.sql

SSCommitted

Points: 1552
More actions
September 24, 2012 at 7:58 am

#281997

Hi all
Please help me to understand why there is space difference in tables.
please find an attachment.
In tblUsrActivityLog table has 24.5 Millions rows and
tblUserActivityLogArchive table has 25 millions rowa
then why tblUsrActivityLog had disk space 56 GB and tblUserActivityLogArchive had 19 GB.
The no of rows are same, then why its differant in space.
Please Help me to understand.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

compression, sparse columns, different columns have data populated (e.g 9 out of 10 columns are null in archive, but 0 out of 10 columns are null in the main table)

surma.sql SSCommitted Points: 1552 More actions · Answer 2

anthony.green (9/24/2012)
compression, sparse columns, different columns have data populated (e.g 9 out of 10 columns are null in archive, but 0 out of 10 columns are null in the main table)

Ok

that means Archive table has null or 0 values so the data size is less

and main table has values so its more in size

right?

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

in theory yea but you will need to check the data yourself, otherwise your looking likely that the table is compressed

Lowell SSC Guru Points: 323504 More actions · Answer 4

also, I believe deleted data does not release space until the clustered index is rebuilt...so if you had a 100 meg table, and deleted half of it, it will still be 100 meg until the index rebuild; also don't forget that indexes take up space also, it's possible that lots of indexes could be larger than the data itself.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!