Most DML operations are logged operations. When an
operation is logged, data about the operation is stored in the SQL
Server transaction log. The transaction log files can be backed up and
replayed into an earlier database backup. Although the log replay
functionality is slow, it allows you to restore the database to the
point in time when the database file itself was lost.
For performance
reasons, some operations that affect SQL Server data are nonlogged, or
minimally logged. This means that the information about these operations
is not fully recorded in the SQL Server transaction log. Nonlogged
operations offer much better performance than logged operations.
However, if a nonlogged operation occurred after the database has been
backed up, you will not be able to replay the logs into the database
after you have restored the database from backup.
Warning
Remember that it is
highly recommended to back up the database after performing any
nonlogged operations. This will ensure that you are able to recover as
much database data as possible.
The following DML operations are either nonlogged or minimally logged:
|
The
database recovery model determines how transaction logs are used by SQL
Server for a specified database. Your choice of recovery model affects
which operations are performed as nonlogged, and whether the database
can be recovered to a point in time. Three recovery models are available
for SQL Server 2008 databases:
When the database recovery
model is set to Simple, log files are reused as soon as they become
full. This means that very little space is consumed by the transaction
logs, and you don’t need to worry about log file management. However,
when a database is set to a Simple recovery model, and the database file
is lost, you will not be able to recover any changes made after the
last full backup. You will also not be able to recover to a point in
time, as transaction details are stored in transaction logs that have
been overwritten in this case.
The Full recovery model
could be said to be the opposite of the Simple recovery model.
Transaction logs are kept and all transactions without exception are
written to the logs. This includes nonlogged operations like TRUNCATE TABLE, SELECT...INTO,
and so on. Although you lose performance advantages of nonlogged
operations with this recovery model, all data is recoverable provided
transaction logs are intact. You can also restore to a point-in-time if
necessary.
The Bulk-Logged
recovery model is similar to the Full recovery model, except that
nonlogged operations are performed as nonlogged. This provides a
performance advantage for Bulk-Logged operations. However, if a
Bulk-Logged operation has occurred since the last full backup, you will
not be able to recover any changes made since the last full backup. The
Bulk-Logged recovery model does not support point-in-time recovery.
In
production environments the full database recovery model generally is
used, because it ensures maximum recoverability. However, if the
administrator wishes to perform a high performance nonlogged operation,
he would temporarily switch the recovery model to Bulk-Logged, perform
the operation, switch the recovery model back to Full, and perform a
full backup. The Full recovery model is the default when creating
databases in SQL Server.
|
Let’s examine the TRUNCATE TABLE statement. The TRUNCATE TABLE statement deletes all data in a given table. Its result is equivalent to a DELETE statement with no WHERE or FROM clause. The syntax for this statement is simple, as follows:
TRUNCATE TABLE [database_name].[schema_name].table_name
The TRUNCATE TABLE
statement cannot be used on tables that are referenced by a FOREIGN KEY
constraint, unless the FOREIGN KEY constraint is self-referencing. The TRUNCATE TABLE statement cannot be used on a table that is a part of an indexed view. You cannot use the TRUNCATE TABLE statement on tables that are published by transactional or merge replication. The TRUNCATE TABLE statement will not activate triggers, as triggers rely on transaction logs, and TRUNCATE TABLE is a nonlogged operation.
|
Why should you use a TRUNCATE TABLE statement at all, if you can accomplish the same task using the DELETE statement? The TRUNCATE TABLE statement is nonlogged, and therefore offers much better performance than using the DELETE statement. Additionally, TRUNCATE TABLE has the following advantages:
The TRUNCATE TABLE
statement uses much less transaction log space. The DELETE statement
logs information about every row that is affected in the transaction
log. When deleting from a table with millions of rows, this is both
time- and disk-space-consuming. The DELETE statement holds a lock on each individual row it is deleting. The TRUNCATE TABLE
statement locks only the table and each data page. This offers better
performance, as locking is one of the most time-consuming SQL Server
activities. The
DELETE statement will leave empty data pages for the table and its
indexes. If you wish to shrink the database by deleting data in a large
table, the DELETE statement may prove counterproductive. The TRUNCATE TABLE statement, on the other hand, is guaranteed to leave zero data pages behind.
In summary, TRUNCATE TABLE
is a more efficient statement. However, in a production database you
must back up the database as soon as you have committed a nonlogged
operation like the TRUNCATE TABLE statement, to reduce the risk of data loss due to a disaster.
|
In Example 1 we will use SELECT...INTO and TRUNCATE TABLE nonlogged operations. In Table 1 we will compare the time consumed by the INSERT, SELECT...INTO, DELETE, and TRUNCATE TABLE statements.
Example 1. Using Nonlogged Operations
USE AdventureWorks
GO
DECLARE @StartTime datetime = GetDate()
SELECT * INTO AllPersonalRecords
FROM Person.Contact
SELECT DATEDIFF(millisecond, @StartTime, GetDate()) as [Time consumed by
SELECT INTO]
GO
-- Results:
-- Time consumed by SELECT INTO
-- ----------------------------
-- 610
SELECT COUNT(*) FROM AllPersonalRecords
--Results: 19982 rows
DECLARE @StartTime datetime = GetDate()
TRUNCATE TABLE AllPersonalRecords
SELECT DATEDIFF(millisecond, @StartTime, GetDate()) as [Time consumed by
Truncate]
GO
-- Results:
-- Time consumed by Truncate
-- -------------------------
-- 0
SET IDENTITY_INSERT dbo.AllPersonalRecords ON
GO
DECLARE @StartTime datetime = GetDate()
INSERT AllPersonalRecords (ContactID, NameStyle, Title, FirstName,
MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone,
PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate)
SELECT * FROM Person.Contact
SELECT DATEDIFF(millisecond, @StartTime, GetDate()) as [Time consumed
by INSERT]
GO
-- Results:
-- Time consumed by INSERT
-- -----------------------
-- 893
DECLARE @StartTime datetime = GetDate()
DELETE AllPersonalRecords
SELECT DATEDIFF(millisecond, @StartTime, GetDate()) as [Time consumed
by DELETE]
--Results:
-- Time consumed by DELETE
-- -----------------------
-- 1376
|
Table 1. Time Consumed by DML Statements Comparison (19,982 Rows)| | SELECT INTO | INSERT | TRUNCATE TABLE | DELETE |
|---|
| Milliseconds | 610 | 893 | 0 | 1376 |
Tip
Remember that the TRUNCATE TABLE statement is the most efficient way to empty a table. However, the TRUNCATE TABLE statement will not work if a table is a target of a FOREIGN KEY constraint.
|
