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,

Sam
  • 21
  • 2

2 Answers2

2

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

Aleksey Vitsko
  • 6,148
  • 5
  • 39
  • 70
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).

Tibor Karaszi
  • 18,269
  • 2
  • 17
  • 30