Fragmentation

What is Fragmentation?

It is a general term used to describe various effects that can occur in indexes because of data modifications. There are two general types of fragmentation Internal and External.

Internal Fragmentation:

Internal Fragmentation often called physical fragmentation or page density.
Internal fragmentation is wasted space on index pages, both at leaf and non leaf levels.
 It occurs because of

•  Page splits leaving empty space on the page that was split and newly allocated page
• Delete operations that leave pages less than full

Internal Fragmentation means the index is taking more space than necessary leading to increased disk space
usage, more pages to read to process the data and more memory used to hold the pages in buffer pool.

External Fragmentation : It is where the pages or extents comprising the leaf level of a clustered or non clustered index are not in an efficient order.
Efficient order means where the logical order of the pages and extents is the same as the physical order of the pages and extents with in the data files.
It occurs:
Due to page splits and reduces the efficiency of ordered scans of part of a clustered or non clustered index

Difference Between Delete & Truncate Commands

• TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command
• You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE
• A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command
• TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter
• Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the de-allocation of the data pages in which the data exists. 
• TRUNCATE is faster than DELETE 
• Reason:When you type DELETE.all the data get copied into the Rollback Table space first.then delete operation get performed.That's why when you type ROLLBACK after deleting a table you can get back the data(The system get it for you from the Rollback Table space).All this process take time.But when you type TRUNCATE it removes data directly without copying it into the Rollback Table space.That's why TRUNCATE is faster.Once you Truncate you can't get back the data.
  
  
  Misconception : You Cannot Rollback  data when Using TRUNCATE Command.
  DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.
  
  

  USE tempdb
  GO-- Create Test Table
  CREATE TABLE TruncateTest (ID INT)
  INSERT INTO TruncateTest (ID)
  SELECT 1
  UNION ALL
  SELECT 2
  UNION ALL
  SELECT 3
  GO-- Check the data before truncate
  SELECT * FROM TruncateTest
  GO-- Begin Transaction
  BEGIN TRAN
  -- Truncate Table
  TRUNCATE TABLE TruncateTest
  GO-- Check the data after truncate
  SELECT * FROM TruncateTest
  GO-- Rollback Transaction
  ROLLBACK TRAN
  GO-- Check the data after Rollback
  SELECT * FROM TruncateTest
  GO-- Clean up
  DROP TABLE TruncateTest
  GO

  Misconception :It is a common mistake to think that TRUNCATE is not logged 
  This is wrong.Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast.
  The deallocation of the data pages is recorded in the log file,but the extents have been marked as empty for reuse. Therefore, BOL refers to TRUNCATE operations as "minimally logged" operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.