# Sql-server – Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

size;sql serversql-server-2005table

We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB. However the total used space has increased by around 400 KB. I believe there is additional index size to be considered. However it still should not show this big difference. What possibly should we look at to understand this difference and what is happening?

This is happening with couple of our tables which only do inserts related to data collected from a third party system. It does not do any updates on any rows. Also we have an index on the table on the primary key which is an ID (number) field.

1. Check that if you have indexes (clustered/nonclustered, full text). Use sp_spaceused 'your_table_name' to check that you can actually discard indexes.
2. Check what type of table is used. In your example of 0.2KB=205 Bytes you will have 38 rows per data page if your table is heap and 39 rows per data page if it is clustered table.

IF EXISTS (SELECT * FROM sys.tables
WHERE name = 'sparse_pages')
DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT --IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Heap. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO

IF EXISTS (SELECT * FROM sys.tables
WHERE name = 'sparse_pages')
DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Clustered Index. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO

1. Check for random inserts/updates/deletes in your table. This may be an issue as free space is not reclaimed back. The free space may be wasted during page splits too.

Please see below the example with average row size 205 Bytes and 200 rows (just like in your case). Table data size is 1.57 MB:

IF EXISTS (SELECT * FROM sys.tables
WHERE name = 'sparse_pages')
DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO


Enter the data

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 8700))
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 200

DELETE FROM sparse_pages
WHERE LEN(Filler)>300
GO
-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO