I have a table used for staging during ETL. It is empty most of the time, but on occasion, during data loading, it can get quite large.
Even after all records are deleted, the table is still very large & this is making table scans very slow regardless of how many rows it has.
Table is a heap, no pk but it does have 2 foreign key's.
Because of these, truncate cant be used when emptying the table. They were added a long time ago to prevent poor data quality causing problems.
I can resolve this now by creating a clustered index on the table and deleting it, which rebuilds the table but i dont want to have to do this on a semi-regular basis.
Is my only option to delete the FK's and do a truncate to prevent it getting too large ?
What is sql servers behavior regarding deallocating space from a table after records are deleted vs truncated?
FYI - i am using this query to get the table size:
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
t.NAME NOT LIKE 'dt%'
and t.name = 'mytableName'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
t.Name, s.Name, p.Rows