In this lesson, l’m going to explore 3 statements that might sound similar but are different in how they work. The first statement is the Delete statement. Delete is used for removing records in a table or objects. If the delete statement is issued without a Where Clause, all records in the specified table will be removed. During deletion, the sql server will keep log each row deleted. If there is an identity column, delete does not reset the identity column. The identity column will resume populating values from the last point before the delete statement was issued. To delete all records in a table, say student table, we do it like this,
DELETE FROM student
If we want to delete a particular record or particular records, we use the WHERE clause to indicate what to delete. Lets say we want to delete the student with sid = 5, we do it like this,
DELETE FROM student
WHERE id = 5
The next statement is the Truncate statement. This statement is very close to the delete statement and many people might confuse the two as the same. Delete is a DML statement but Truncate is a DDL statement. A truncate statement will delete all records in the table and it will reset identity columns to start from the initial value or the seed. Truncate statement does not log every row being delete, it logs each page of table data deleted. Truncate is faster than delete statement. Truncate does not allow a WHERE clause. It deletes every record in the table. To truncate the table, for example, the student table, we do it like this,
TRUNCATE student
The last statement for this discussion is the drop statement. The drop statement is a DDL statement. It is used for removing every object that is created using the CREATE statement. The drop statement will delete all records in the object as well as delete the object and it’s structure. Drop is a powerful statement and care should be taken when using it. Let’s say we want to completely remove the student table and all of it’s data from the database, we do it like this,
DROP student.
For your information, TSQL or SQL in general is not case sensitive.
I’ll go over the UPDATE and the use of the WHERE CLAUSE in details in the next tutorial.
