t-sql supports two statements that can be used to erase data or records from a table. Hi! this
is Mina, in this video we are going to talk about the delete and truncate statements in sql server
management studio and the differences between them. the truncate statement is used to delete
all records or all data from the table however the table structure or the table itself will still remain
and the syntax for truncating data from a table is you type out the word truncate
the word table and then you provide the name of the table so it's truncate table table name
next to delete records from a table is to use the delete statement this is used to delete either all
records from the table or some part of the record from the table the syntax for deleting records
from the table is you type out the word delete the word from and then you provide the table name
this first part of the delete statement would delete all records from the table just as the
truncate statement, however, if you provide a condition by typing in the where clause
and the condition it means that you only want to delete part of the record from the table so for
instance if you type out where column name equals to one it means that you want to delete records
where column name is equal to one so you can delete part of a record using a delete statement
let's move on to our SQL server management studio and see how these statements work
i'll connect to my SQL server management studio and go to new query i'm just gonna paste
the syntaxes here for truncate and delete alright one thing i want you to notice is that whenever
you open a new query the default database is set to master, master is a system database so what you
have to do is to switch over to the database you want to work in here we are going to work in our
student record database so you can go to databases and this is the database that we'll be working in
i can type out the word use and then type the database name and execute this command
so our database is now set to student records database, instead of using
code you can also click on this drop-down arrow and select the appropriate database
and it should work the same way, all right so in our student record database i'll go to tables
and these are the tables that we have in our student records
database and these are the tables that we are going to be truncating and deleting data from
let's go ahead and see what records we have in these two tables, I would right-click on the
instructor table and select top thousand rows we have eight records in our instructor table
okay let's see what we have in our student table i'll right click and select top thousand rows
we have another eight records in our student table, now let's truncate the instructor table
so we type out the words truncate and table and then we provide the name of the table, instructor
table just as the syntax tells us to do, truncate table table name truncate table instructor
execute this command you can also click on F5. command completed successfully okay, so in our
instructor table these are the records we had in our instructor table before truncating it
now if we select top thousand rows we should see that all these records have been erased from the
table, however the table structure which are the column names - the instructor id, first name, last
name, email address, gender should still remain so now that we've truncated our table let's see
what records are left in our instructor table see all the records are gone but the table structure
is still there okay let's insert data back into our instructor table and see how the table
structure will look like i already have the syntax here to insert data back into my instructor table
and this is the insert into statement that i'm going to execute if you want to learn how to write
the insert into statement in t T-SQL, feel free to watch my previous video in the link above, i've
also provided the same link in the description section so i'm going to execute this command
multiple times to insert data back into our instructor table we've inserted two rows now let's
click some more to insert more data okay now we are going to select
top thousand rows again to see how many data has now been inserted back into our instructor table
see we have inserted eight rows again and the instructor id still started from one that is one
unique property of the truncate statement whenever you delete table records with the truncate
statement you can still insert data into the table with the identity numbering still starting from
one. we can insert more data into the instructor table to see, two more rows another two
more rows and let's execute this command see we've inserted back into our table and the
numbering still continues okay let's move on to the delete statement. with the delete statement
the syntax is type out the word delete from table name let's delete this student table this time
okay if we execute this command all the records in our student table is going to be deleted
just as the truncates worked. Let's take a look at how the table itself looks like before we
execute the delete command so we're going to select top thousand rows from the student table
we have eight records here pay attention we have eight records in our student table
the student id which is the identity column starts from one through to eight. we are
going to delete all the records from this student table using the delete statement, so let's do that
eight rules affected which means that all eight records have been deleted from our
students table, let's execute this command to select top thousand rows from our student table
see the table structure still remains but all the records have been deleted now let's
insert data back into our student table we are going to execute our insert into statement
let's insert it five times to get 10 records this time
okay so we are going back to select top thousand rows and see how many data has been inserted
back into our students table, all right so see the student id the numbering for the identity column
continued from where it ended the last id was eight and so now that we've been set the data back
into our student table the numbering has started from nine and it continues in that other this is
what you get for deleting records from a table using the delete statement unlike the truncate
according to microsoft.com the truncate statement also works faster when executed compared to the
delete statement so these are two differences between the delete and truncate. first of all
the numbering for truncate will restart after you reinsert data into a table
whereas the delete will continue from the last numbering in the identity column
and also truncate is faster when executed compared to the delete statement one more difference
between the truncate and delete is that you can provide a condition by typing the while statement
so if i want to delete specifically just this row of data, i could provide a condition and say
where student id equals 9 so i'm going to type out where student id equals nine and execute
one row affected, so with the delete statement
we can specifically delete just a record from the table and all the other record will still remain
let's execute the command to select top thousand rules and see what is left for the student
table, see the record for the student id 9 has been deleted so we now have our students
id starting from 10 and the rest of the data still remains. we cannot provide a where condition
when we use the truncate statement, let's say we want to truncate instructor id
1 so we type out where instructor id equal to one this should give us an error because truncate
statement does not accept a where condition so as you can see even this clause has been underlined which
tells us straight up, that this code is not going to work see incorrect syntax near the keyword where
okay guys this brings us to the end of our tutorial on truncating and deleting data from
a table. Thank you for staying along with me on this tutorial and see you in my next one bye bye