Thursday, May 2, 2013

sql server truncate table

sql server truncate table

                     truncate command in sql server deletes all records in the table. this operation is faster than delete command.
because of following reasons

delete command records deleted entries will be logged into  log file. where as truncate doesn't.

delete command fires triggers, where as truncate don't.

Once truncate occurs on a table, user cannot rollback data.
Syntax
                truncate table  [tablename];
              

Multiple table truncates

             truncate table [table1],[table2] ...[table n];

Truncate a tables associated with foreign key reference:

Steps to truncate tables associated with primary key and foreign key references.

Step 1) Truncate all child tables
Step 2) Truncate parent table.
 
Step 1)Truncate table [child table 1],[child table 2]...[child table n];
Step 2)Truncate table [parent table 1] [parent table 2] ...[parent table n];
 

Tags: sql server Truncate table,sql server truncate tables, sql server truncate table with foreign key references, sql server truncate multiple tables, truncate trigger in sql server.sql server 2012 truncate table command

No comments:

Post a Comment