SQL Server BasicsWhat is SQL Server database?What is RDBMS?What is Normalization?Why we use Denormalization?What_is_SQL?What is PL/SQL?Difference between SQL and PL/SQLDatabase TableOne to One RelationshipOne to Many RelationshipMany to Many RelationshipMany to One RelationshipString Data TypesNumber Data TypesDate Data TypesOther Data TypesCreate DatabaseDrop DatabaseCreating and Managing Users in SQL ServerCreate TableAlter TableDrop TableConstraints in SQL serverPrimary KeyForeign KeyUnique KeyCandidate KeyComposite KeyDifference between primary key and candidate keyPrimary key and foreign key relationshipSurrogate KeyCascading Referential Integrity ConstraintsSelf Referential Integrity ConstraintsInsert into statementInsert multiple recordsUpdate statementDelete statementTruncate statementDifference between Delete and TruncateAlias in SQL ServerSelect statementSelect DistinctSelect TopSelect IntoNull Functions(ISNULL(),NULLIF(),COALESCE())Sub QueryIdentity ColumnSequence objectDifference between sequence and identity columnSQL Server ClausesWHERE ClauseOrder By ClauseTop N ClauseGroup By ClauseHaving ClauseDifference between Where and HavingSQL Server OperatorsArithmetic OperatorsComparison OperatorsLogical OperatorsBitwise OperatorsAny OperatorsAll OperatorsUnion OperatorsUnion All OperatorsDifference between Union and Union AllIntersect OperatorExcept OperatorDifference between Except and IntersectJoinsInner JoinLeft JoinRight JoinFull JoinSelf JoinCross JoinViewsWhat are views?Create views using SSMSIndexed ViewsComplex ViewsCheck Option in ViewCheck Encryption in ViewSchema Binding Option in ViewRead-only ViewsUpdatable ViewsAdvantages and disadvantages of viewsCreate multiple views on one tableCan we implement index on views?Can we Perform Insert, update, delete operation on views?Stored Procedure and FunctionsWhat are Stored Procedures?Why we use stored procedures?Passing parameters to Stored procedureUser-Defined FunctionsDifference between UDF and Stored procedurePre-Defined Functions@@Indentity and Scope_IndentityNULLIF, ISNULL and COALESCE
Previous
Next

Difference between Delete and Truncate in SQL Server

The DELETE and TRUNCATE statements in SQL Server are used to remove data from tables, but they have some key differences. Here's a comparison of the two:

  1. Operation:
    • DELETE: The DELETE statement removes rows from a table one by one. It allows for selective deletion based on specified conditions using the WHERE clause. Deleted rows can be rolled back using a transaction.
    • TRUNCATE: The TRUNCATE statement removes all rows from a table in a single operation. It deletes all data from the table and cannot be rolled back.
  2. Speed and Efficiency:
    • DELETE: The DELETE statement is slower and less efficient compared to TRUNCATE. Each row is deleted individually, and the operation generates more transaction log entries, which can impact performance.
    • TRUNCATE: The TRUNCATE statement is faster and more efficient. It removes all rows from the table in a single operation and generates fewer transaction log entries, resulting in better performance.
  3. Transaction Logging:
    • DELETE: Each row deletion performed by the DELETE statement is logged in the transaction log. This allows for rollback and recovery of individual deletions.
    • TRUNCATE: The TRUNCATE statement is minimally logged. It logs the deallocation of data pages instead of individual row deletions, making it faster. However, it cannot be rolled back on a row-by-row basis.
  4. Resetting Identity Columns:
    • DELETE: Deleting rows with identity columns does not reset the identity value. The next insert will continue from where it left off.
    • TRUNCATE: When you use TRUNCATE, it resets the identity column value back to its seed value. The next insert will start from the seed value.
  5. Trigger Execution:
    • DELETE: The DELETE statement triggers any associated delete triggers defined on the table. The triggers are fired for each deleted row.
    • TRUNCATE: The TRUNCATE statement does not fire delete triggers associated with the table. It bypasses trigger execution.
  6. Table Structure and Permissions:
    • DELETE: The DELETE statement preserves the table structure, including constraints, indexes, and permissions.
    • TRUNCATE: The TRUNCATE statement removes all data from the table, including the table structure, constraints, indexes, and permissions. After truncating, the table is empty, and it is treated as a new table.
  7. Usage Scenarios:
    • DELETE: The DELETE statement is typically used when you need to selectively delete specific rows based on conditions or when you need to log individual row deletions.
    • TRUNCATE: The TRUNCATE statement is useful when you want to remove all data from a table quickly, such as before repopulating it or when you no longer need the existing data.

In summary, the DELETE statement is more suitable for selective row deletions, while the TRUNCATE statement is more efficient for removing all rows from a table. The choice between them depends on the specific requirements and the impact on table structure, logging, and triggers.

Previous
Next