Instead of Delete, use TRUNCATE statement to delete row from table..it will release the data from the table & reduce the table size..
check below sample..
--run each query to different session
--simply run query to each sql server tab window
--create one table
create table idn
(
id int identity,name varchar(100)
)
GO
--insert 5000 records
insert into idn
select 'yogesh'
go 5000
GO
--check table size
sp_spaceused 'idn'
Go
--delte records & data
truncate table idn
--check table size
sp_spaceused 'idn'
prathika
Member
65 Points
112 Posts
how to synchronize table size in sqlserver2005
Feb 28, 2012 06:40 AM|LINK
hi friends,
In sqlserver Database Table we have 1,00,000 rows.When we delete all rows the size of the table is decreased or not???
If we want to decrease the size of the table is there any way to synchronize table size or any other ways??
Please give your feedback for the above incident.
sqLserver2005
yrb.yogi
Star
14460 Points
2402 Posts
Re: how to synchronize table size in sqlserver2005
Feb 28, 2012 07:18 AM|LINK
Instead of Delete, use TRUNCATE statement to delete row from table..it will release the data from the table & reduce the table size..
check below sample..
sqLserver2005
.Net All About