Difference between delete, truncate, drop in sql 2013

View previous topic View next topic Go down

Difference between delete, truncate, drop in sql 2013

Post by Admin on Sun Sep 15, 2013 4:02 am

2013 2013 2013 <div dir="ltr" style="text-align: left;" trbidi="on"><u><b>SQL Delete Statement</b></u></br> It is a DML command</br>This command can be used to remove some or all rows from the table</br>After deletion, it does free the space containing the table</br>While deleting rows, it applies lock on each row</br>Where condition can be used</br>It deletes specified rows if where clause exists in the query</br>Rollback is possible</br>Triggers are fired</br>It is used to manipulate/modify the table data. It does not change the property of a table</br>Slower in performance since it maintains logs</br></br> <u><b>Syntax</b></u></br>DELETE FROM table_name [WHERE condition];</br>Where clause in this command is optional, if used the rows that satisfies the condition gets deleted</br> <u><b>Example</b></u></br>DELETE FROM employee WHERE id = 100; -> Delete rows where id = 100</br>DELETE FROM employee; -> Deletes all the rows</br></br><iframe src="http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137657713065266565&affid=kumarbhati" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" ></iframe><iframe src="http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137657725999716598&affid=kumarbhati" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" ></iframe><iframe src="http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137657739378994418&affid=kumarbhati" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" ></iframe><iframe src="http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137657745934599371&affid=kumarbhati" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" ></iframe><hr></br> <u><b>SQL Truncate Statement</b></u></br> It is a DDL command</br>This command is used to remove all the rows from the table</br>After deletion, it free the space containing the table</br>While deleting rows, it applies lock on the whole table</br>Where condition cannot be used with this command</br>It removes all the data from the table since where clause is not allowed</br>Rollback is not possible</br>Triggers are not fired</br>It resets identity columns, deallocates data pages and empty them for use of other objects in the database</br>Faster in performance</br></br> <u><b>Syntax</b></u></br>TRUNCATE TABLE table_name;</br> <u><b>Example</b></u></br>TRUNCATE TABLE employee;</br> It removes all the rows from table employee</br></br><hr></br><u><b>SQL DROP Statement</b></u></br>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. It cannot be rollback. When a table is dropped all the references to the table will not be valid.</br></br> <u><b>Syntax</b></u></br>DROP TABLE table_name;</br> <u><b>Example</b></u></br>DROP TABLE employee;</br></br> This command removes all the data of employee table plus removes the table as well from the database. </div><br> 2013 2013 2013 <br><div dir="ltr" style="text-align: left;" trbidi="on"><u><b>SQL Delete Statement</b></u></br> It is a DML command</br>This command can be used to remove some or all rows from the table</br>After deletion, it does free the space containing the table</br>While deleting rows, it applies lock on each row</br>Where condition can be used</br>It deletes specified rows if where clause exists in the query</br>Rollback is possible</br>Triggers are fired</br>It is used to manipulate/modify the table data. It does not change the property of a table</br>Slower in performance since it maintains logs</br></br> <u><b>Syntax</b></u></br>DELETE FROM table_name [WHERE condition];</br>Where clause in this command is optional, if used the rows that satisfies the condition gets deleted</br> <u><b>Example</b></u></br>DELETE FROM employee WHERE id = 100; -> Delete rows where id = 100</br>DELETE FROM employee; -> Deletes all the rows</br></br><iframe src="http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137657713065266565&affid=kumarbhati" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" ></iframe><iframe src="http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137657725999716598&affid=kumarbhati" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" ></iframe><iframe src="http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137657739378994418&affid=kumarbhati" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" ></iframe><iframe src="http://www.flipkart.com/affiliate/displayWidget?affrid=WRID-137657745934599371&affid=kumarbhati" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" ></iframe><hr></br> <u><b>SQL Truncate Statement</b></u></br> It is a DDL command</br>This command is used to remove all the rows from the table</br>After deletion, it free the space containing the table</br>While deleting rows, it applies lock on the whole table</br>Where condition cannot be used with this command</br>It removes all the data from the table since where clause is not allowed</br>Rollback is not possible</br>Triggers are not fired</br>It resets identity columns, deallocates data pages and empty them for use of other objects in the database</br>Faster in performance</br></br> <u><b>Syntax</b></u></br>TRUNCATE TABLE table_name;</br> <u><b>Example</b></u></br>TRUNCATE TABLE employee;</br> It removes all the rows from table employee</br></br><hr></br><u><b>SQL DROP Statement</b></u></br>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. It cannot be rollback. When a table is dropped all the references to the table will not be valid.</br></br> <u><b>Syntax</b></u></br>DROP TABLE table_name;</br> <u><b>Example</b></u></br>DROP TABLE employee;</br></br> This command removes all the data of employee table plus removes the table as well from the database. </div><br>2013 2013 2013 <br> <a href="http://www.matrixar.com/" title="Matrix ">المصفوفة : أجمل الخلفيات والصور</a>

Admin
Admin

Posts : 64122
Join date : 2013-02-22

View user profile http://prowebsites1.forumaroc.net

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum