Difference between DELETE and TRUNCATE command in SQL

     Difference between DELETE and TRUNCATE command

The most important and the most favorite question of the interviewers, that i had came across, is 'what's the difference between delete and truncate command?'. It is very important to know the difference between the two when you are working on a database.

Few important difference are as follows:

1. TRUNCATE:
   • TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
   • TRUNCATE removes the data by de-allocating the data pages used to store the table's data, and only the page de-allocations are recorded in the transaction log.
   • TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
   • You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
   • TRUNCATE cannot be rolled back.
   • TRUNCATE is DDL Command.
   • TRUNCATE Resets identity of the table
   • Cannot use WHERE conditions
2. DELETE:
   • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
   • If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
   • DELETE Can be used with or without a WHERE clause
   • DELETE Activates Triggers.
   • DELETE can be rolled back.
   • DELETE is DML Command.
   • DELETE does not reset identity of the table.

Recently, I came across a database. The size of the MDF file of that database was around 300 GB and when i took the backup of the database the backup file was of only 90 GB. I was confused and consult with the team that was working on that database to know there basic practice of working on DB. I found that they always use 'DELETE' command to remove data from a table. As mentioned above, DELETE command always stores an entry for each row in transaction log and hence,  this discrepancy was observed.

It is always recommended that you use TRUNCATE over DELETE if you want to delete all the records from the table.

One more impotant difference is: 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.

Indexes in SQL

                                            Indexes in SQL

Index is a database object which help to fetch/retrieve data faster from SQL server. It is basically use to optimize query performance.

An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

                                      Query to create index:
                                                       Create Index IndexName
                                                       On TableName (ColumnName1,ColumnName2,....)

                                      Query  to rename index:
                                                       sp_rename 'IndexName','New_IndexName'

 Two main types of indexes are Clustered and Non-Clustered index

Clustered Index : A clustered index determines the order in which the rows of a table are stored on disk. If a table has a clustered index, then the rows of that table will be stored on disk in the same exact order as the clustered index.There can be only one clustered index created on a table.

Non-Clustered Index: A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes. A single table can have up-to 256 non-clustered index.

                                   

                                   Difference between Clustered and Non-Clustered Index

• A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.
• A table can have multiple non-clustered indexes. But, a table can have only one clustered index.
• Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the non-clustered index actually stores the row-level data in it’s leaf nodes.

Different types of statement in SQL

Different types of statement in SQL

SQL statements can be classified into four types as follows: 

DDL

Data Definition Language statements are used to define the database structure or schema. Examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language statements are used for managing data within schema objects. Examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language statements are generally used to provide control/access/privileges to user over database. Examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. Examples:

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original state
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use