February 11, 2009 at 1:57 am
Dear all,
After delete a table's records, will DELETE also deallocate used space to SQL Server, especially those large tables ?
Of course, I know TRUNCATE TABLE will do that, but please tell me whether DELETE will also do that (no matter it is a heap, clustered index or non-clustered index tables).
One more question, Oracle needs to run Table Reorganization, does SQL Server also have similar task ?
February 11, 2009 at 2:05 am
As far as I know, DELETE frees up the pages, but it doesn't reduce the .mdf file size. It will still remain as available space in file.
Regards,
Nitin
February 12, 2009 at 5:00 am
If all the rows on a page are deleted by the DELETE command the page will be marked as empty. If all the pages in an extent are empty, that extent will be release for re-use by other SQL tables.
SQL Server uses the ALTER INDEX ... REBUILD statement to globally reorganise space in a table. There is a ALTER TABLE ... REBUILD statement in SQL 2008. These are is similar to (but not the same!) as a Oracle or DB2 reorganise.
ALTER INDEX ... REBUILD is the new form of the REBUILD INDEX command and includes more options. With both of these facilities, all rows will get moved so they use the minimum number of pages and the pages take the minimum number of extents, allowing for the FREE SPACE paramaters. Any space released can be used by other SQL Serve tables.
The database file size on disk is not reduced by these operations. You can shrink the database file size with the SHRINKFILE comand. It is bad practice to shrink the database file size if you expect it to grow again within three months.
Repeated shrink and growth of database files will harm your performance.
Repeated ALTER INDEX ... REBUILD without shrinking your database file will maintain or improve your performance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 12, 2009 at 5:20 am
Actually as far as I know when you delete records even if the page is empty, it will still belong to the table. Truncate table will release the pages. I think that the script bellow proves that:
set nocount on
go
create table MyTable (filler char(6000))
go
insert into MyTable (filler) values ('aaa')
go 1000
exec sp_spaceused MyTable
go
delete MyTable
go
exec sp_spaceused MyTable, 'true'
go
truncate table MyTable
exec sp_spaceused MyTable, 'true'
go
drop table MyTable
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 12, 2009 at 9:18 pm
Thanks for your replies.
October 21, 2010 at 12:48 am
Adi Cohn-120898 (2/12/2009)
Actually as far as I know when you delete records even if the page is empty, it will still belong to the table. Truncate table will release the pages. I think that the script bellow proves that:
set nocount on
go
create table MyTable (filler char(6000))
go
insert into MyTable (filler) values ('aaa')
go 1000
exec sp_spaceused MyTable
go
delete MyTable
go
exec sp_spaceused MyTable, 'true'
go
truncate table MyTable
exec sp_spaceused MyTable, 'true'
go
drop table MyTable
Adi
Hi,
Its possible to deallocate the data page by using the tablelock hints in delete statement ,but it will deallocate only the data pages of the table not all the pages.
delete MyTable with (TABLOCK)
go
exec sp_spaceused MyTable, 'true'
Only trucate table will do the deallocate of all the pages of the table.
January 25, 2013 at 1:31 am
Hi,
From the MSDN we have a below statements,
"To delete rows in a heap and deallocate pages, use one of the following methods.
Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take a shared lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hint (Transact-SQL).
Use TRUNCATE TABLE if all rows are to be deleted from the table.
Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources."
How can we prove each other?
i have tried using sp_spaceused/dbcc ind but not able to prove the above statements. can anybody help on this please.
🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply