I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database:
SELECT t.name AS TableName, p.rows, (sum(a.total_pages) * 8) as reserved, (sum(a.data_pages) * 8) as data, N'Not Needed' as index_size, (sum(a.total_pages) * 8) - (sum(a.used_pages) * 8) as unused FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.name = N'XYZ' GROUP BY t.name, p.rows ORDER BY 1 Desc;
It gives me correct results for calculating number of rows, reserved space, and unused space. However, when I compare its output with the output from running
sp_spaceused stored procedure, I observe a different value for space used by data:
How can I fix it?