.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

whats the difference between Drop Truncate and Delete in Sql

Posted By: Sreeraj Nair Mungath     Posted Date: July 05, 2012    Points:5   Category :Sql Server
SQL
whats the difference between Drop Truncate and Delete in Sql

Responses
Author: Sreeraj Nair Mungath             
Posted Date: July 05, 2012     Points: 20   

i got the answer.thanks in advance

Author: Mohanraj          Accepted Answer   
Posted Date: July 05, 2012     Points: 40   

Hi,

Drop

The Drop command is used to remove a table from the database

if you drop a table all rows in the table will be deleted and the table structure will be removed from database


Delete

The Delete command is used to delete rows from the table based on the where condition.
where clause in the delete command is optional. it is used to identify the rows.

Truncate

The truncate command is used to delete all the rows from the table and free the space containing in the table.

Author: Syed Shakeer Hussain             
Posted Date: July 07, 2012     Points: 20   

Delete Statement

The DELETE Statement is used to delete rows from a table.

The Syntax of a SQL DELETE statement is:

DELETE FROM table_name [WHERE condition];

table_name -- the table name which has to be updated.

NOTE:The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.

For Example: To delete an employee with id 100 from the employee table, the sql delete query would be like,

DELETE FROM employee WHERE id = 100;

To delete all the rows from the employee table, the query would be like,

DELETE FROM employee;
SQL TRUNCATE Statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
Syntax to TRUNCATE a table:

TRUNCATE TABLE table_name;

For Example: To delete all the rows from employee table, the query would be like,

TRUNCATE TABLE employee;

Difference between DELETE and TRUNCATE Statements:

DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.
SQL DROP Statement:

The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using RENAME command. When a table is dropped all the references to the table will not be valid.

Syntax to drop a sql table structure:

DROP TABLE table_name;

For Example: To drop the table employee, the query would be like

DROP TABLE employee;

Difference between DROP and TRUNCATE Statement:


If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.



Author: chaitanya             
Posted Date: July 10, 2012     Points: 20   

HI,
Refer link http://blog.sqlauthority.com/2007/04/17/sql-server-interview-questions-part-3/

Thanks


Author: Sandeep Tripathi             
Posted Date: July 12, 2012     Points: 20   

thanks every 1 for such good replies....

Thanks and regards,
Sandeep
Author: Sreeraj Nair Mungath             
Posted Date: July 17, 2012     Points: 20   

Thank u so much 4 valuable feed back



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend