Delete with TABLOCK
-
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 this?
I have tried checking threw DBCC command, but it is showing the space is allocated.
Can anyone prove for me please.
🙂
-
SQL* (1/27/2013)
How can we prove this?I have tried checking threw DBCC command, but it is showing the space is allocated.
Can anyone prove for me please.
What has to be proved ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
SQL* (1/27/2013)
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 this?
I have tried checking threw DBCC command, but it is showing the space is allocated.
Can anyone prove for me please.
Could you please make it more clear? What is the problem here anyway?
Sujeet Singh -
Divine Flame (1/28/2013)
SQL* (1/27/2013)
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 this?
I have tried checking threw DBCC command, but it is showing the space is allocated.
Can anyone prove for me please.
Could you please make it more clear? What is the problem here anyway?
From the above statement from MSDN, if we use TABLOCK in the DELETE statement then the allocated pages will be deallocated.
I have checked whether there is any available space/allocated for the table, by using sp_Spaceused it is showing the allocated pages as it is. So the above statement is not true, right? But it is from MSDN so we can't tell this is wrong statement.
For the TRUNCATE TABLE statement, it is deallocating the pages.
🙂
-
SQL* (1/28/2013)
From the above statement from MSDN, if we use TABLOCK in the DELETE statement then the allocated pages will be deallocated.If the table is a heap.
So the above statement is not true, right?
It's true.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
SQL* (1/28/2013)
From the above statement from MSDN, if we use TABLOCK in the DELETE statement then the allocated pages will be deallocated.
I have checked whether there is any available space/allocated for the table, by using sp_Spaceused it is showing the allocated pages as it is. So the above statement is not true, right? But it is from MSDN so we can't tell this is wrong statement.
For the TRUNCATE TABLE statement, it is deallocating the pages.
If the table is heap ( i.e. it does not have clustered index) & you use TABLOCK when deleting, the pages will be deallocated. It can be shown using undocumented command DBCC IND:
USE AdventureWorks
GO
IF OBJECT_ID('TheTable') IS NOT NULL
BEGIN
DROP TABLE TheTable;
END
GO
CREATE TABLE TheTable
(
ID INT IDENTITY(1,1),
Data VARCHAR(2000)
)
GO
INSERT INTO TheTable (Data) VALUES (REPLICATE('SomeData',150))
GO 1000
GO
--Check the number of pages allocated to the table
GO
DBCC IND ('AdventureWorks','TheTable',-1) -- 175 pages
GO
DELETE FROM TheTable WITH (TABLOCK)
GO
DBCC IND ('AdventureWorks','TheTable',-1) -- Only 1 page (IAM page) available & all other pages deallocated
--after deleting with TABLOCK
In the above example if you delete the data without using TABLOCK the pages will not be deallocated (that also can be checked using the same DBCC IND).
Sujeet Singh -
Thank you.
🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply