1

I have a table which is predominantly filled with NULLS and takes up 10 GB which was shocking since probably 90 to 95% of the table is NULLS. I copied this table over to a new database on the same SQL 2012 instance using Select * Into.... but the copied table takes up about 70 MB.

Checking them against each other, they seem to have the same data, as expected so it's not like data is missing.

Is the disparity in size a result of not generating a script to copy indexes, triggers, keys, etc?

Is there a way to optimize the table to reduce space?

Thank you,

Improve this question
4
  • You are indeed missing indexes,triggers,keys but, more important, deleted records do not free space … If you deleted lost of records from your original table, than the description you gave is quit normal. see also: dba.stackexchange.com/questions/86295/releasing-free-space
    – Luuk
    Commented Dec 8, 2019 at 15:01
  • What is the reserved and unused space of the original table as reported by EXEC sp_spaceused 'dbo.YourTable'?
    – Dan Guzman
    Commented Dec 8, 2019 at 15:03
  • @DanGuzman I had checked the space allocation using reports for the database at the time and "Data" portion was 99.96% of reserved.
    – Sam
    Commented Dec 8, 2019 at 17:22
  • 1. Was your table initially filled with data, and then it was updated to NULL? Update action, as far as I understand inner workings of SQL Server would not de-allocate pages. 2. What is index fill factor?
    – Alex
    Commented Dec 8, 2019 at 21:38

2 Answers 2

Reset to default
2

One aspect which I don't think was addressed so far is if your source table is a heap table. SQL server is very bad at re-using space for heaps, and this is one (not the only) reason for not using heaps in the first place. You make the table a non-heap table by having a clustered index on the table.

I.e., this is most likely not at all about nulls, but rather about heaps (or possibly could also be non-deallocated LOB storage).

Improve this answer
1

Is the disparity in size a result of not generating a script to copy indexes, triggers, keys, etc?

Yes it can be due to missing indexes on target table (the one that is 70 MB)
Plus it can be due to heavy fragmentation (99%) of all indexes of the source table (the one that is 10 GB), while target table's fragmentation level is very low and it is compact

Is there a way to optimize the table to reduce space?

You can rebuild source table (10 GB) by running below command

ALTER INDEX ALL on [TableName] REBUILD

That will fix fragmentation issues (if there are any) on source table

If your SQL Server Version and Edition supports Data Compression, you can also try use data compression which will save space greatly

ALTER INDEX ALL on [TableName] REBUILD with (data_compression = page, online = on)

Finally if columns contain a lot of NULLs, you can consider using sparse columns

https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-sparse-columns?view=sql-server-ver15

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.