When I ran:
SP_Spaceused TEST. I got this output which is very strange for 20918 rows.
Output:
name rows reserved data index_size unused
TEST 20918 6240816 KB 6235480 KB 2304 KB 3032 KB
I Try to gess The reason.
I copy These new records to another table.
select * into TEST1 from TEST
But The result was more strange : on new table the size of these record was
name rows reserved data index_size unused
TEST1 20918 5704 KB 5456 KB 8 KB 240 KB
I tried rebuilding the Index on TEST table, but it was not helpful.
Can someone please explain what could be the reason for this?
ALTER TABLE dbo.TEST REBUILD;
(rebuilding the table, not rebuilding an index since that is not where your size is being reported)? Creating a clustered index as Tibor suggested should also resolve the problem. And you'll only have to do that once. If you leave it as a heap, you'll likely get into the same state again. – Aaron Bertrand Aug 19 '20 at 15:52