sql语句截断_SQL Server中SQL截断和SQL删除语句之间的区别

sql语句截断

We get the requirement to remove the data from the relational SQL table. We can use both SQL Delete and SQL Truncate statement to delete the data. Understanding differences between these commands helps SQL developers to handle their data well. Additionally, this is a very common question asked in SQL beginner’s interviews.

我们得到了从关系SQL表中删除数据的要求。 我们可以同时使用SQL Delete和SQL Truncate语句来删除数据。 了解这些命令之间的差异有助于SQL开发人员很好地处理其数据。 此外，这是SQL初学者访谈中一个非常常见的问题。

Tell me the difference between delete and truncate command.

告诉我delete和truncate命令之间的区别。

I have seen most of the candidates not familiar with the difference between these commands. This article gives you a complete overview of Delete and Truncate statements along with differences.

我看到大多数候选人都不熟悉这些命令之间的区别。 本文为您提供了Delete和Truncate语句及其差异的完整概述。

SQL删除命令 (SQL Delete Command)

We use SQL Delete command in SQL Server to remove records from a table. We can remove all records or use a Where clause to remove records matching the criteria.

我们在SQL Server中使用SQL Delete命令从表中删除记录。 我们可以删除所有记录，或使用Where子句删除符合条件的记录。

示例1：从表中删除所有记录 (Example 1: To remove all records from a table)

Suppose we want to remove all records from an Employee table, execute the following query.

假设我们要从Employee表中删除所有记录，请执行以下查询。

DELETE FROM [SQLShackDemo].[dbo].[Employee];

示例2：从表中删除特定记录 (Example 2: To remove specific records from a table)

Suppose we want to remove all employee records belongs to a particular city San Antonio. We can specify this condition in a Where clause with SQL Delete statement as shown below.

假设我们要删除属于特定城市圣安东尼奥的所有员工记录。 我们可以在带有SQL Delete语句的Where子句中指定此条件，如下所示。

DELETE FROM [SQLShackDemo].[dbo].[Employee]
where City='San Antonio'

We need to use the string value within the single quotes. We can directly specify value without single quotes in the Where clause such as EmpID in the following query.

我们需要在单引号内使用字符串值。 我们可以直接在Where子句中指定不带单引号的值，例如以下查询中的EmpID。

DELETE [SQLShackDemo].[dbo].[Employee]
WHERE EmpID = 1001;

In the Actual Execution Plan of a Delete clause, we can see it is using the Clustered Index Delete operator to remove a specific row.

在Delete子句的实际执行计划中，我们可以看到它正在使用Clustered Index Delete运算符删除特定的行。

示例3：SQL Delete语句和标识值 (Example 3: SQL Delete statement and identity values)

Delete statement removes the records one by one and it logs each entry into the transaction log. It is a DML statement.

Delete语句一个接一个地删除记录，并将每个条目记录到事务日志中。 这是DML语句。

Suppose we remove a row from a table using the DELETE statement and that table contains the SQL IDENTITY values. IDENTITY values get generate on every new record insert. We might have a question-

假设我们使用DELETE语句从表中删除一行，并且该表包含SQL IDENTITY值。 IDENTITY值在每个新记录插入时生成。 我们可能有一个问题-

What happens to an identity value once we remove a record?

一旦我们删除记录，身份值会怎样？

Let’s look at this scenario using an example. We have the following data in the Employee table.

让我们通过一个例子来看看这种情况。 我们在Employee表中有以下数据。

EmpID column is an identity column. We can check the identity column in a table using sp_help command.

EmpID列是一个标识列。 我们可以使用sp_help命令检查表中的identity列。

sp_help '[Employee]'

We need to delete the EmpID 1014 from the Employee table.

我们需要从Employee表中删除EmpID 1014。

DELETE [SQLShackDemo].[dbo].[Employee]
WHERE EmpID = 1014;

Once we remove the record, view the record from the table. In the following screenshot, we can see that identity value 1014 is not available because we removed that record from the table.

删除记录后，从表中查看记录。 在下面的屏幕截图中，我们可以看到标识值1014不可用，因为我们从表中删除了该记录。

SQL Delete statement does not reset the identity values in a table. We can use DBCC CHECKIDENT to check current identity value and manually set a new identity value for the identity column.

SQL Delete语句不会重置表中的标识值。 我们可以使用DBCC CHECKIDENT来检查当前的标识值，并手动为标识列设置新的标识值。

For example, in the following query, we want to reset the identity values to 500.

例如，在以下查询中，我们要将身份值重置为500。

DBCC CHECKIDENT('Employee', RESEED,500)

If we insert a new record, it uses an identity value 501.

如果我们插入一条新记录，它将使用一个标识值501。

insert into employee values('bb','bb','b','bb',1,555)

示例4：回滚SQL delete语句事务 (Example 4: Rollback a SQL delete statement transaction)

We can roll back a transaction using the delete statement. Let’s use a delete statement with BEGIN Transaction.

我们可以使用delete语句回滚事务。 让我们在BEGIN Transaction中使用delete语句。

BEGIN TRANSACTION;
DELETE FROM [SQLShackDemo].[dbo].[Employee]
WHERE EmpID = 1010;

We removed the record having EmpId 1010 in the employee table.

我们删除了employee表中具有EmpId 1010的记录。

We can use to roll back the transaction. Execute this rollback command and view the records in the table. We can see the deleted record gets a rollback and we can see it in the Employee table.

我们可以用来回滚事务。 执行此回滚命令并查看表中的记录。 我们可以看到已删除的记录得到回滚，并且可以在Employee表中看到它。

Rollback transaction

SQL截断命令 (SQL Truncate Command)

SQL Truncate is a data definition language (DDL) command. It removes all rows in a table. SQL Server stores data of a table in the pages. The truncate command deletes rows by deallocating the pages. It makes an entry for the de-allocation of pages in the transaction log. It does not log each row deletion in the transaction log.

SQL Truncate是一种数据定义语言（DDL）命令。 它删除表中的所有行。 SQL Server在页面中存储表的数据。 truncate命令通过取消分配页面来删除行。 它在事务日志中输入要取消分配页面的条目。 它不会在事务日志中记录每行删除。

We cannot specify any condition in the SQL Truncate command. At a high level, you can consider truncate command similar to a Delete command without a Where clause. It locks the table and the pages instead of a row.

我们无法在SQL Truncate命令中指定任何条件。 在较高的级别上，您可以考虑truncate命令类似于不带Where子句的Delete命令。 它锁定表和页面而不是行。

SQL Server does not show the actual execution plan of a SQL Truncate command. In the following screenshot, you can see the estimated execution plan.

SQL Server不显示SQL Truncate命令的实际执行计划。 在以下屏幕截图中，您可以看到估计的执行计划。

示例5：使用truncate语句删除Employee表的所有行 (Example 5: Remove all rows of Employee table using the truncate statement)

The SQL truncate command is straightforward; you just need to pass the table name to remove all rows.

SQL truncate命令很简单。 您只需要传递表名即可删除所有行。

TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

示例6：SQL Truncate命令和标识值 (Example 6: SQL Truncate command and identity values)

In the previous example 3, we explored delete command with the identity values. Delete does not reset identity values. Let’s see how the truncate command behaves with the identity values.

在前面的示例3中，我们探索了带有标识值的delete命令。 删除不会重置身份值。 让我们看一下truncate命令与标识值的行为。

First, execute the command in example 5 to delete all rows of a table.

首先，执行示例5中的命令以删除表的所有行。

TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

The table is empty now. Let’s insert a new record in the table.

该表现在是空的。 让我们在表中插入一条新记录。

INSERT into employee values('bb','bb','b','bb',1,555)

You can see that Identity value again starts from 1 as defined in the table properties.

您可以看到Identity值再次从表属性中定义的1开始。

示例7：带有回滚SQL Truncate命令 (Example 7: SQL Truncate command with Rollback)

In example 4, w explored to roll back a transaction with the delete command. It is a misconception among DBA’s that we cannot roll back a transaction executed with the TRUNCATE command. Is it true?

在示例4中，w探索了使用delete命令回滚事务的方法。 在DBA中，我们无法回退使用TRUNCATE命令执行的事务是一个误解。 是真的吗

Let’s explore this again with an example.

让我们通过一个示例再次进行探讨。

Start a transaction to truncate the employee table.

启动事务以截断employee表。

BEGIN TRAN;
TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

Once the command is completed, verify that there are no records in the table.

命令完成后，请验证表中是否没有记录。

Now, issue the Rollback Tran command and verify the records in the table. We get our data back in the table.

现在，发出Rollback Tran命令并验证表中的记录。 我们将数据返回表中。

It shows that we can roll back delete as well as truncated command started within a transaction.

它显示了我们可以回滚删除以及在事务中启动的截断命令。

Let’s explore the difference between the SQL Delete and SQL Truncate command in the following table.

下表探讨了SQL Delete和SQL Truncate命令之间的区别。

删除与截断 (Delete vs Truncate)

SQL Delete	SQL Truncate
Delete command is useful to delete all or specific rows from a table specified using a Where clause	The truncate command removes all rows of a table. We cannot use a Where clause in this.
It is a DML command	It is a DDL command.
SQL Delete command places lock on each row requires to delete from a table.	SQL Truncate command places a table and page lock to remove all records.
Delete command logs entry for each deleted row in the transaction log.	The truncate command does not log entries for each deleted row in the transaction log.
Delete command is slower than the Truncate command.	It is faster than the delete command.
It removes rows one at a time.	It removes all rows in a table by deallocating the pages that are used to store the table data
It retains the identity and does not reset it to the seed value.	Truncate command reset the identity to its seed value.
It requires more transaction log space than the truncate command.	It requires less transaction log space than the truncate command.
You require delete permission on a table to use this	You require Alter table permissions to truncate a table.
You can use the Delete statement with the indexed views.	You cannot use the truncate command with the indexed views.
Delete command retains the object statistics and allocated space.	Truncate deallocates all data pages of a table. Therefore, it removes all statistics and allocated space as well.
Delete command can activate a trigger as well. Delete works on individual rows and delete the data. Therefore, it activates a trigger.	The truncate command cannot activate a trigger. The trigger is activated if any row modification takes place. In this command, SQL Server deallocates all pages, so it does not activate a trigger.
Delete command removes the rows matched with the where clause. It also does not remove the columns, indexes, constraints, schema	The truncate command only removes all rows of a table. It does not remove the columns, indexes, constraints, and schema.

SQL删除	SQL截断
Delete命令对于从使用Where子句指定的表中删除所有或特定行很有用	truncate命令删除表的所有行。 我们不能在此使用Where子句。
这是DML命令	这是DDL命令。
SQL Delete命令在需要从表中删除的每一行上放置锁。	SQL Truncate命令放置一个表和页锁以删除所有记录。
删除命令记录事务日志中每个已删除行的条目。	truncate命令不会在事务日志中记录每个已删除行的条目。
Delete命令比Truncate命令慢。	它比delete命令快。
它一次删除一行。	它通过取消分配用于存储表数据的页面来删除表中的所有行
它保留身份并且不将其重置为种子值。	Truncate命令将身份重置为其种子值。
它比truncate命令需要更多的事务日志空间。	与truncate命令相比，它需要更少的事务日志空间。
您需要对表具有删除权限才能使用此功能	您需要具有Alter表权限才能截断表。
您可以将Delete语句与索引视图一起使用。	您不能将truncate命令与索引视图一起使用。
Delete命令保留对象统计信息和分配的空间。	截断可取消分配表的所有数据页。 因此，它也删除所有统计信息和分配的空间。
Delete命令也可以激活触发器。 删除适用于单个行并删除数据。 因此，它激活触发器。	truncate命令无法激活触发器。 如果发生任何行修改，将激活触发器。 在此命令中，SQL Server取消分配所有页面，因此它不会激活触发器。
Delete命令删除与where子句匹配的行。 它还不会删除列，索引，约束，架构	truncate命令仅除去表的所有行。 它不会删除列，索引，约束和架构。

结论 (Conclusion)

In this article, we explored both the SQL Delete and SQL Truncate command to remove the rows in a table. I hope this article helps to answer all your questions related to delete and truncate commands in SQL Server and also understand the difference in these commands. You need to be careful while using the truncate command because it removes all rows in a table.

在本文中，我们研究了SQL Delete和SQL Truncate命令以删除表中的行。 我希望本文有助于回答您与SQL Server中的删除和截断命令有关的所有问题，并希望您理解这些命令之间的区别。 使用truncate命令时需要小心，因为它会删除表中的所有行。

翻译自: https://www.sqlshack.com/difference-between-sql-truncate-and-sql-delete-statements-in-sql-server/

sql语句截断