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

Truncate statement in SQL Server

The TRUNCATE TABLE statement in SQL Server is used to remove all the records from a table. Unlike the DELETE statement, which deletes rows one by one, the TRUNCATE TABLE statement efficiently removes all rows from a table in a single operation. It also resets any identity columns and reclaims the disk space used by the table.

The basic syntax for the TRUNCATE TABLE statement is as follows: 


TRUNCATE TABLE table_name;

Let's say we have a table called "Employees" with columns "EmployeeID," "FirstName," and "LastName." Here's an example of how to use the TRUNCATE TABLE statement to remove all the records from the table: 


TRUNCATE TABLE Employees;

In this example, all the records from the "Employees" table will be deleted, and the table will be left empty.

It's important to note a few considerations when using the TRUNCATE TABLE statement:

  1. TRUNCATE TABLE cannot be rolled back: Unlike the DELETE statement, the TRUNCATE TABLE statement cannot be rolled back. Once the operation is executed, the data is permanently removed from the table.
  2. TRUNCATE TABLE resets identity columns: If the table has an identity column (auto-incrementing column), using TRUNCATE TABLE will reset the identity value back to its seed value.
  3. TRUNCATE TABLE is faster than DELETE: TRUNCATE TABLE is faster and uses fewer system resources compared to the DELETE statement when deleting all records from a table.
  4. TRUNCATE TABLE cannot have a WHERE clause: Unlike the DELETE statement, the TRUNCATE TABLE statement does not allow specifying a WHERE clause. It deletes all rows in the table.
  5. TRUNCATE TABLE does not trigger triggers or fire delete triggers: When using TRUNCATE TABLE, any triggers defined on the table are not fired. It bypasses trigger logic, which can be beneficial in certain scenarios.
  6. TRUNCATE TABLE requires appropriate permissions: The user executing the TRUNCATE TABLE statement must have the necessary permissions on the table.

It's crucial to exercise caution when using the TRUNCATE TABLE statement since it permanently removes all the data from a table. Always make sure to have a backup of the data or perform the operation on a test environment before executing it in a production setting.

Previous
Next