Difference between delete, truncate, drop in sql 2013

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

Back to top


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