Prerequisites
If you are new to the Azure SQL, then please watch my following videos on the Azure SQL database.
How To Create Azure SQL Database
How To Connect Azure SQL Database
Let us consider we have the Employee table in our Azure SQL Server database having the following records,
The following two commands are used to delete the records from the Azure SQL table.
- Delete
- Truncate
Using Delete Command
The delete command is used to delete all the records from the table or specific records by using the Where clause.What does Delete Command Do?
- The Delete command can be used with or without a WHERE clause.
- The Delete command removes all records if you use it without the where condition.
- The Delete command removes the one or more rows based on the condition given in the Where clause.
- The Delete command maintains the logs of each deleted record. This helps to keep track of each deleted record.
- The Delete command activates triggers. It means you can take certain action whenever you delete the record, such as inserting the deleted records in some other table etc.
- The Delete can be rolled back. It means that if the records are deleted by mistake, then we can restore the deleted record back to the table.
- The Delete command does not reset the identity of the table. It means that when you delete all the records from the table having Id between 1 and 10, then after adding the new record in the table, it will start with the ID count from 11, not 1.
Deleting Specific Records
To delete the specific record from the table, we need to use the where clause.
Syntax
delete from TableName where ColumName=value
Example
delete from Employee where Id=1
The above query will delete the records from the Employee table which has id 1. After deleting the record, then the table records will look like as follows.
Deleting all records
To delete all records from the table, we need to write a query without the where clause.
Example
delete from Employee
Using Truncate command
The truncate command removes all the rows from a table.
What does Truncate Command Do?
- Truncate removes all rows from a table, but the table structure such as columns, constraints, indexes remains.
- Truncate command can not be used with where or any condition.
- Truncate command can not be activates the trigger, It means we can not take any action on deleted records.
- Truncate cannot be rolled back, it means deleted records from the table cannot be restored.
- Truncate resets the identity of the table, it means that when you delete all the records from the table having Id between 1 and 10, then after adding the new record in the table, it will start with the ID count from 1, not 11.
- Truncate command can not be maintains the logs of deleted record. It means we can not keep the track of each deleted record.
Example
truncate table Employee
The preceding query deletes all the records from the table.
Summary
I hope this article helped to know how to delete records from the Azure SQL database. If you have a suggestion related to the article, then please comment using the comment box.
Post a Comment