Click here to Skip to main content
14,207,131 members
   

Database

 
QuestionSQL 2008 Truncate vs Delete Pin
mrfalk12-Feb-13 6:47
membermrfalk12-Feb-13 6:47 
AnswerRe: SQL 2008 Truncate vs Delete Pin
Eddy Vluggen12-Feb-13 7:48
mveEddy Vluggen12-Feb-13 7:48 
GeneralRe: SQL 2008 Truncate vs Delete Pin
mrfalk12-Feb-13 8:27
membermrfalk12-Feb-13 8:27 
GeneralRe: SQL 2008 Truncate vs Delete Pin
Eddy Vluggen12-Feb-13 8:36
mveEddy Vluggen12-Feb-13 8:36 
GeneralRe: SQL 2008 Truncate vs Delete Pin
Khorshed Alam, Dhaka18-Feb-13 18:09
memberKhorshed Alam, Dhaka18-Feb-13 18:09 
AnswerRe: SQL 2008 Truncate vs Delete Pin
NickPace12-Feb-13 10:09
memberNickPace12-Feb-13 10:09 
AnswerRe: SQL 2008 Truncate vs Delete Pin
Corporal Agarn13-Feb-13 3:59
professionalCorporal Agarn13-Feb-13 3:59 
AnswerRe: SQL 2008 Truncate vs Delete Pin
gvprabu20-Feb-13 21:37
membergvprabu20-Feb-13 21:37 
Hi,


-- DELETE, TRUNCATE and DROP Statements
DELETE
/*
The DELETE command is used to remove rows from a table.
A WHERE clause can be used to only remove some rows.
If no WHERE condition is specified, all rows will be removed.
After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
Note that this operation will cause all DELETE triggers on the table to fire.
*/
TRUNCATE
/*
TRUNCATE removes all rows from a table.
The operation cannot be rolled back and no triggers will be fired.
As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
*/
DROP
/*
The DROP command removes a table from the database.
All the tables' rows, indexes and privileges will also be removed.
No DML triggers will be fired. The operation cannot be rolled back.
*/

--Difference between TRUNCATE and DELETE commands
/*
1) TRUNCATE is a DDL command whereas DELETE is a DML command.

2) TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.
Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3) You cann't rollback in TRUNCATE but in DELETE you can rollback.
TRUNCATE removes the record permanently.

4) In case of TRUNCATE ,Trigger doesn't get fired.
But in DML commands like DELETE .Trigger get fired.

5) You cann't use conditions(WHERE clause) in TRUNCATE.
But in DELETE you can write conditions using WHERE clause
*/

Regards,
GVPrabu
QuestionSQL Server 2008 Pin
VishwaKL10-Feb-13 17:51
memberVishwaKL10-Feb-13 17:51 
AnswerRe: SQL Server 2008 Pin
Shameel10-Feb-13 22:33
professionalShameel10-Feb-13 22:33 
AnswerRe: SQL Server 2008 Pin
jschell11-Feb-13 8:20
memberjschell11-Feb-13 8:20 
Questionupdate values in a table in single query Pin
nainakarri7-Feb-13 20:07
membernainakarri7-Feb-13 20:07 
AnswerRe: update values in a table in single query Pin
Mycroft Holmes7-Feb-13 21:00
memberMycroft Holmes7-Feb-13 21:00 
GeneralRe: update values in a table in single query Pin
Shameel10-Feb-13 22:38
professionalShameel10-Feb-13 22:38 
GeneralRe: update values in a table in single query Pin
Mycroft Holmes11-Feb-13 13:30
memberMycroft Holmes11-Feb-13 13:30 
AnswerRe: update values in a table in single query Pin
Eddy Vluggen7-Feb-13 22:42
mveEddy Vluggen7-Feb-13 22:42 
GeneralRe: update values in a table in single query Pin
gvprabu10-Feb-13 18:02
membergvprabu10-Feb-13 18:02 
AnswerRe: update values in a table in single query Pin
gvprabu20-Feb-13 21:43
membergvprabu20-Feb-13 21:43 
QuestionIs it possible to print a variable in stored procedure Pin
Deepthi.214567-Feb-13 19:45
memberDeepthi.214567-Feb-13 19:45 
AnswerRe: Is it possible to print a variable in stored procedure Pin
nainakarri7-Feb-13 20:22
membernainakarri7-Feb-13 20:22 
GeneralRe: Is it possible to print a variable in stored procedure Pin
vanikanc12-Feb-13 8:19
membervanikanc12-Feb-13 8:19 
QuestionVisual Studio 2005 Access 2010 Database Pin
Member 98194707-Feb-13 12:06
memberMember 98194707-Feb-13 12:06 
AnswerRe: Visual Studio 2005 Access 2010 Database Pin
Mycroft Holmes7-Feb-13 13:45
memberMycroft Holmes7-Feb-13 13:45 
QuestionSimulate transaction: non blocking insert / update Pin
Bob Stanneveld7-Feb-13 11:12
memberBob Stanneveld7-Feb-13 11:12 
AnswerRe: Simulate transaction: non blocking insert / update Pin
Mycroft Holmes7-Feb-13 13:44
memberMycroft Holmes7-Feb-13 13:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190612.1 | Last Updated 10 Jun 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid