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
  • 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: https://dba.stackexchange.com/questions/86295/releasing-free-space – Luuk Dec 08 '19 at 15:01
  • What is the reserved and unused space of the original table as reported by EXEC sp_spaceused 'dbo.YourTable'? – Dan Guzman Dec 08 '19 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 Dec 08 '19 at 17:22
  • 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 Dec 08 '19 at 21:38