- If you do not need the table anymore
for sure, drop it.
- If you do not need all of the data in
the table, but you want to keep the table structure including constraints
and indexes, use Truncate. The truncate command works at the page level,
not at the row level as the Delete command
- If you may need the data at a later
time, use Delete. But be sure to have a clustered index on the table
before the deletion. The clustered index will minimize the number of pages
left in the system after the deletion. But keep in mind that, even
some pages keep in the system after deletion, these pages can be re-used
by SQL Server, you typically do not need to do
anything to vacuum them.
--Demo: Difference between Delete and Truncate
and how a clustered index can help the space relieved.
--Create a heap table
USE
[AdventureWorks2008R2]
GO
CREATE TABLE
[Sales].[SalesOrderDetail_hash]
(
[SalesOrderID]
[int] NOT NULL ,
[SalesOrderDetailID] [int] ,
[CarrierTrackingNumber] [nvarchar](25) NULL ,
[OrderQty]
[smallint] NOT NULL ,
[ProductID]
[int] NOT NULL ,
[SpecialOfferID]
[int] NOT NULL ,
[UnitPrice]
[money] NOT NULL ,
[UnitPriceDiscount] [money] NOT NULL ,
[LineTotal] NUMERIC(38, 6) ,
[rowguid]
[uniqueidentifier] ,
[ModifiedDate]
[datetime]
)
ON [PRIMARY]
GO
--populate data to the heap table
INSERT INTO
[Sales].[SalesOrderDetail_hash]
SELECT *
FROM [Sales].[SalesOrderDetail]
-- create a clustered index table
CREATE TABLE
[Sales].[SalesOrderDetail_C]
(
[SalesOrderID]
[int] NOT NULL ,
[SalesOrderDetailID] [int] ,
[CarrierTrackingNumber] [nvarchar](25) NULL ,
[OrderQty]
[smallint] NOT NULL ,
[ProductID]
[int] NOT NULL ,
[SpecialOfferID]
[int] NOT NULL ,
[UnitPrice]
[money] NOT NULL ,
[UnitPriceDiscount] [money] NOT NULL ,
[LineTotal] NUMERIC(38, 6) ,
[rowguid]
[uniqueidentifier] ,
[ModifiedDate]
[datetime] ,
CONSTRAINT
[PK_SalesOrderDetailC_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC ) ON
[PRIMARY]
)
ON [PRIMARY]
GO
--populate data to the clustered index table
INSERT INTO
[Sales].[SalesOrderDetail_C]
SELECT *
FROM [Sales].[SalesOrderDetail]
go
-- create a non-clustered index table
CREATE TABLE
[Sales].[SalesOrderDetail_N]
(
[SalesOrderID]
[int] NOT NULL ,
[SalesOrderDetailID] [int] ,
[CarrierTrackingNumber] [nvarchar](25) NULL ,
[OrderQty]
[smallint] NOT NULL ,
[ProductID]
[int] NOT NULL ,
[SpecialOfferID]
[int] NOT NULL ,
[UnitPrice]
[money] NOT NULL ,
[UnitPriceDiscount] [money] NOT NULL ,
[LineTotal] NUMERIC(38, 6) ,
[rowguid]
[uniqueidentifier] ,
[ModifiedDate]
[datetime] ,
CONSTRAINT
[PK_SalesOrderDetailN_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC ) ON
[PRIMARY]
)
ON [PRIMARY]
GO
--populate data to the non-clustered index table
INSERT INTO
[Sales].[SalesOrderDetail_N]
SELECT *
FROM [Sales].[SalesOrderDetail]
Go
--Delete the three tables
DELETE [Sales].[SalesOrderDetail_hash]
GO
DELETE [Sales].[SalesOrderDetail_C]
GO
DELETE [Sales].[SalesOrderDetail_N]
GO
--82 pages left after the deleting the heap table
SELECT *
FROM sys.Dm_db_index_physical_stats(DB_ID('Adventureworks2008R2'),
OBJECT_ID('[Sales].[SalesOrderDetail_hash]'),
NULL, NULL, NULL)
--79 (=66+13) pages left after the deleting the
non-clustered index table
SELECT *
FROM sys.Dm_db_index_physical_stats(DB_ID('Adventureworks2008R2'),
OBJECT_ID('[Sales].[SalesOrderDetail_N]'),
NULL, NULL, NULL)
--Only 1 page left after the deleting the
clustered index table
SELECT *
FROM sys.Dm_db_index_physical_stats(DB_ID('Adventureworks2008R2'),
OBJECT_ID('[Sales].[SalesOrderDetail_C]'),
NULL, NULL, NULL)
--How about truncating the tables?
TRUNCATE TABLE
[Sales].[SalesOrderDetail_hash]
--No pages left at all if truncating the table
SELECT *
FROM sys.Dm_db_index_physical_stats(DB_ID('Adventureworks2008R2'),
OBJECT_ID('[Sales].[SalesOrderDetail_hash]'),
NULL, NULL, NULL)
