How much space does an empty table use in Microsoft SQL Server 2008 R2?
sql-server, sql-server-2008-r2
Solution
When you create table it will be zero length. When you delete from table it will not free all used space. When you truncate table it will free all used space.
CREATE TABLE Test(ID int, Name nchar(4000))
GO
EXEC sp_spaceused N'Test'
INSERT INTO Test(ID, Name) VALUES(1, 'a')
EXEC sp_spaceused N'Test'
DELETE FROM dbo.Test
EXEC sp_spaceused N'Test'
TRUNCATE TABLE dbo.Test
EXEC sp_spaceused N'Test'
DROP TABLE Test
Output:
name rows reserved data index_size unused
Test 0 0 KB 0 KB 0 KB 0 KB
Test 1 16 KB 8 KB 8 KB 0 KB
Test 0 16 KB 8 KB 8 KB 0 KB
Test 0 0 KB 0 KB 0 KB 0 KB
You can read this:
https://dba.stackexchange.com/questions/9141/sql-server-sp-spaceused-gives-zero-rows-but-big-datasize-for-cleaned-table
Problem
Nobody wants to simply delete records, so my colleague and I discussed whether it would be better to append a "deleted" flag (datatype `bit`), or to create a duplicate of the table and move "deleted" records into the duplicate. Since we expect a low amount of delete actions p.a. but a high amount of records in our data, the simple bit might grow quite a lot, which is why we discussed the duplicate table. Hence the question: How much space does an empty table use in SQL Server 2008 R2?