How much space does an empty table use in Microsoft SQL Server 2008 R2?

Question

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?

There is a SQL selection to show the used space from all tables: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database — Maximilian Ast, Feb 25 '15 at 12:47
Your question appears to have no relation to your problem. Do you mean "how much space does my table which holds soft deleted rows take up"? Otherwise, why would the space an empty table takes up even be interesting? Obviously it's insignificant if you only have one. — Jeroen Mostert, Feb 25 '15 at 12:49
@JeroenMostert 1 to 8 Bit fields in a table actually take up [one byte on disk](https://msdn.microsoft.com/en-us/library/ms177603.aspx). They're stored packed like that. If he already has a bit field, then this extra field actually costs no disk space. The thing is: *that's all documented*, and I assume he knows this. Now they're trying to get an idea of the disk overhead of the alternative. — Bacon Bits, Feb 25 '15 at 13:03
@BaconBits: then the question would indeed be "how much space does my table which holds soft deleted rows take up (compared to using a bit field)". I'm asking to clarify precisely because the literal answer to the literal question ("how much space does an empty table take up") is then irrelevant. Since that's the answer you gave, though, I'm not sure why you're telling *me* what the OP wants. :-) — Jeroen Mostert, Feb 25 '15 at 13:06
@BaconBits: ah, I think I see what you're getting at now: in both cases, the total amount of rows stays the same, and so the difference between the two solutions is the difference between the space taken up by all the bits, versus the space taken up by the soft-delete table metadata. I wouldn't even have thought about a constant overhead that small... — Jeroen Mostert, Feb 25 '15 at 13:15
As long as we're getting precise, though, what you *actually* want to know in that case is how much the *non-constant* overhead is. For the bit field, that's either 0 or 1 byte per row, depending on the current row layout. For the separate table, it's the overhead of row metadata per row, plus any index metadata. Basically, the separate table will always take up more space; row metadata is always more than a single byte. Even so, the difference should be insignificant compared to your actual data; space should not be the deciding concern here. — Jeroen Mostert, Feb 25 '15 at 14:00

score 3 · Accepted Answer · edited Apr 13 '17 at 12:42

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

score 2 · Answer 2 · answered Feb 25 '15 at 12:45

To view how much space a table uses in a SQL Server Database:

In SQL Server Management Studio, select the table from the Object Explorer, and access the Object Explorer Details from the View Menu. (keyword shortcut: F7)

Here is a screenshot displaying metadata from all the tables in a database. enter image description here

score 0 · Answer 3 · edited May 23 '17 at 12:09

I would expect one page at the bare minimum (8 kilobytes). It should be easy enough to test. It may be more if your table contains LOBs.

If you want to know with any overhead, you might need to figure it manually. Create a database, disable autogrowth, fill it so that it's full, try to create a table (hopefully it fails), get the database size including free space, then enable autogrowth, then create a table, then check the size again including free space.

I expect the answer in both cases is, "So little that we'd have to be considering floppy disk as a potential storage medium for it to matter." That said, 8 kilobytes is ~64,000 bits, but keep in mind how bits are stored.

How much space does an empty table use in Microsoft SQL Server 2008 R2?

3 Answers3