programming4us
programming4us
DATABASE

SQL Server 2008 : Performing Nonlogged DML Operations

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
1/19/2011 11:19:17 AM
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:

  • TRUNCATE TABLE.

  • WRITETEXT and UPDATETEXT. These statements are deprecated and should not be used. Use column. Write instead. You learned about the column.

  • SELECT INTO.

Configuring & Implementing...: Effect of the Database Recovery Model on Bulk-Logged Operations

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:

  • Simple recovery model

  • Full recovery model

  • Bulk-Logged recovery model

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.

Head of the Class...: TRUNCATE TABLE vs DELETE statements

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 INTOINSERTTRUNCATE TABLEDELETE
Milliseconds61089301376

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.

Other  
  •  SQL Server 2008 : Using the OUTPUT Clause with the MERGE Statement
  •  SQL Server 2008 : Returning Data from DML Operations Using the OUTPUT Clause
  •  SQL Server 2008: Working with System Databases
  •  SQL Server 2008 : Using @@IDENTITY and NEWID Functions in DML Statements
  •  SQL Server 2008 : Using Advanced Functionality with DML - Introduction
  •  Defensive Database Programming with SQL Server: The Ticket-Tracking System (part 2) - Removing the performance hit of ON UPDATE CASCADE
  •  Defensive Database Programming with SQL Server: The Ticket-Tracking System (part 1) - Enforcing business rules using constraints only
  •  SQL Server 2008 : Working with DML Queries - Using the MERGE Statement
  •  Defensive Database Programming with SQL Server : Client-side Error Handling
  •  SQL Server 2008 : Working with DML Queries - Using the DELETE Statement
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us