in databases, what is the difference between a delete statement and a

in databases, what is the difference between a delete statement and a

We get the requirement to take away the info from the relational SQL desk. We will use each SQL Delete and SQL Truncate assertion to delete the info. Understanding variations between these instructions helps SQL builders to deal with their knowledge effectively. Moreover, this can be a quite common query requested in SQL newbie’s interviews.

Inform me the distinction between delete and truncate command.

You're reading: in databases, what is the difference between a delete statement and a

I’ve seen many of the candidates not acquainted with the distinction between these instructions. This text provides you a whole overview of Delete and Truncate statements together with variations.

SQL Delete Command

We use SQL Delete command in SQL Server to take away data from a desk. We will take away all data or use a The place clause to take away data matching the factors.

Instance 1: To take away all data from a desk

Suppose we wish to take away all data from an Worker desk, execute the next question.

Instance 2: To take away particular data from a desk

Suppose we wish to take away all worker data belongs to a specific metropolis San Antonio. We will specify this situation in a The place clause with SQL Delete assertion as proven beneath.

We have to use the string worth throughout the single quotes. We will immediately specify worth with out single quotes within the The place clause reminiscent of EmpID within the following question.

Within the Precise Execution Plan of a Delete clause, we will see it’s utilizing the Clustered Index Delete operator to take away a selected row.

Actual execution of a SQL Delete command

Instance 3: SQL Delete assertion and id values

Delete assertion removes the data one after the other and it logs every entry into the transaction log. It’s a DML assertion.

Suppose we take away a row from a desk utilizing the DELETE assertion and that desk accommodates the SQL IDENTITY values. IDENTITY values get generate on each new file insert. We’d have a question-

What occurs to an id worth as soon as we take away a file?

Let’s take a look at this state of affairs utilizing an instance. We have now the next knowledge within the Worker desk.

Sample data

EmpID column is an id column. We will test the id column in a desk utilizing sp_help command.

identity property of a table

We have to delete the EmpID 1014 from the Worker desk.

As soon as we take away the file, view the file from the desk. Within the following screenshot, we will see that id worth 1014 just isn’t obtainable as a result of we eliminated that file from the desk.

SQL Delete statement and identity values

SQL Delete assertion doesn’t reset the id values in a desk. We will use DBCC CHECKIDENT to test present id worth and manually set a brand new id worth for the id column.

For instance, within the following question, we wish to reset the id values to 500.

Output of DBCC CHECKIDENT

If we insert a brand new file, it makes use of an id worth 501.

Insert a new record

Instance 4: Rollback a SQL delete assertion transaction

We will roll again a transaction utilizing the delete assertion. Let’s use a delete assertion with BEGIN Transaction.

Find out: when a dynamic dns record is created, what is the ttl value used for?

We eliminated the file having EmpId 1010 within the worker desk.

Delete a record from the table

We will use to roll again the transaction. Execute this rollback command and think about the data within the desk. We will see the deleted file will get a rollback and we will see it within the Worker desk.

Rollback delete transaction

SQL Truncate Command

SQL Truncate is a knowledge definition language (DDL) command. It removes all rows in a desk. SQL Server shops knowledge of a desk within the pages. The truncate command deletes rows by deallocating the pages. It makes an entry for the de-allocation of pages within the transaction log. It doesn’t log every row deletion within the transaction log.

We can’t specify any situation within the SQL Truncate command. At a excessive stage, you’ll be able to contemplate truncate command just like a Delete command with out a The place clause. It locks the desk and the pages as an alternative of a row.

SQL Server doesn’t present the precise execution plan of a SQL Truncate command. Within the following screenshot, you’ll be able to see the estimated execution plan.

Estimated execution plan of SQL Truncate

Instance 5: Take away all rows of Worker desk utilizing the truncate assertion

The SQL truncate command is easy; you simply have to move the desk identify to take away all rows.

Instance 6: SQL Truncate command and id values

Within the earlier instance 3, we explored delete command with the id values. Delete doesn’t reset id values. Let’s see how the truncate command behaves with the id values.

First, execute the command in instance 5 to delete all rows of a desk.

The desk is empty now. Let’s insert a brand new file within the desk.

You possibly can see that Identification worth once more begins from 1 as outlined within the desk properties.

Reset identity value

Instance 7: SQL Truncate command with Rollback

In instance 4, w explored to roll again a transaction with the delete command. It’s a false impression amongst DBA’s that we can’t roll again a transaction executed with the TRUNCATE command. Is it true?

Let’s discover this once more with an instance.

Begin a transaction to truncate the worker desk.

As soon as the command is accomplished, confirm that there are not any data within the desk.

View the records in a table.

Now, challenge the Rollback Tran command and confirm the data within the desk. We get our knowledge again within the desk.

Rollback transaction

It reveals that we will roll again delete in addition to truncated command began inside a transaction.

Let’s discover the distinction between the SQL Delete and SQL Truncate command within the following desk.

Delete vs Truncate

SQL Delete

SQL Truncate

Delete command is helpful to delete all or particular rows from a desk specified utilizing a The place clause

Find out: what is the primary difference between editing and proofreading?

The truncate command removes all rows of a desk. We can’t use a The place clause on this.

It’s a DML command

It’s a DDL command.

SQL Delete command locations lock on every row requires to delete from a desk.

SQL Truncate command locations a desk and web page lock to take away all data.

Delete command logs entry for every deleted row within the transaction log.

The truncate command doesn’t log entries for every deleted row within the transaction log.

Delete command is slower than the Truncate command.

It’s quicker than the delete command.

It removes rows one by one.

It removes all rows in a desk by deallocating the pages which might be used to retailer the desk knowledge

It retains the id and doesn’t reset it to the seed worth.

Truncate command reset the id to its seed worth.

It requires extra transaction log area than the truncate command.

It requires much less transaction log area than the truncate command.

You require delete permission on a desk to make use of this

You require Alter desk permissions to truncate a desk.

You should use the Delete assertion with the listed views.

You can’t use the truncate command with the listed views.

Delete command retains the item statistics and allotted area.

Truncate deallocates all knowledge pages of a desk. Due to this fact, it removes all statistics and allotted area as effectively.

Delete command can activate a set off as effectively. Delete works on particular person rows and delete the info. Due to this fact, it prompts a set off.

The truncate command can’t activate a set off. The set off is activated if any row modification takes place. On this command, SQL Server deallocates all pages, so it doesn’t activate a set off.

Delete command removes the rows matched with the the place clause. It additionally doesn’t take away the columns, indexes, constraints, schema

The truncate command solely removes all rows of a desk. It doesn’t take away the columns, indexes, constraints, and schema.

Conclusion

On this article, we explored each the SQL Delete and SQL Truncate command to take away the rows in a desk. I hope this text helps to reply all of your questions associated to delete and truncate instructions in SQL Server and in addition perceive the distinction in these instructions. That you must watch out whereas utilizing the truncate command as a result of it removes all rows in a desk.

Read more: what is a personal driver called