删除与截断

DELETE -> 数据库引擎从相关数据页和输入该行的所有索引页中查找并删除该行。因此，索引越多，删除所需的时间就越长。

是的，尽管这里有两种选择。执行基表删除的同一操作员可以逐行从非聚集索引中删除行。这称为窄（或每行）更新计划：

或者，非聚集索引删除可以由单独的运算符执行，每个非聚集索引一个。在这种情况下（称为宽或每个索引更新计划），完整的操作集存储在工作表（急切假脱机）中，然后每个索引重播一次，通常按特定非聚集索引的键显式排序以鼓励顺序访问模式。

TRUNCATE -> 简单地删除所有表的数据页，使其成为删除表内容的更有效选项。

是的。 TRUNCATE TABLE效率更高的原因有很多：

1. 可能需要更少的锁。截断通常只需要一个表级别的模式修改锁（以及每个解除分配的范围上的排他锁）。删除可能会获得较低（行或页）粒度的锁，以及任何已释放页上的排他锁。
2. 只有截断才能保证从堆表中释放所有页。即使指定了独占表锁定提示（例如，如果为数据库启用了行版本控制隔离级别），删除也可能会在堆中留下空页。
3. 截断总是最低限度地记录（不管正在使用的恢复模型）。事务日志中只记录页面释放操作。
4. 如果对象的大小为 128 或更大，截断可以使用延迟删除。延迟删除意味着实际的释放工作由后台服务器线程异步执行。

不同的恢复模式如何影响每个语句？有什么影响吗？

删除总是完全记录（删除的每一行都记录在事务日志中）。如果恢复模式不是 ，则日志记录的内容会有一些小的差异FULL，但这在技术上仍然是完整的日志记录。

删除时，是扫描所有索引还是只扫描该行所在的索引？我会假设所有索引都被扫描（而不是寻找？）

删除索引中的一行（使用前面显示的窄更新计划或宽更新计划）始终是按键访问（查找）。扫描删除的每一行的整个索引将是非常低效的。让我们再看一下前面显示的 per-index 更新计划：

执行计划是需求驱动的管道：父操作员（左侧）通过一次向子操作员请求一行来驱动子操作员完成工作。排序运算符是阻塞的（它们必须在生成第一个排序行之前消耗整个输入），但它们仍然由请求第一行的父级（索引删除）驱动。索引删除从完成的排序中一次拉出一行，更新每一行的目标非聚集索引。

在广泛的更新计划中，您经常会看到基表更新运算符将列添加到行流中。在这种情况下，聚集索引删除将非聚集索引键列添加到流中。存储引擎需要这些数据来定位要从非聚集索引中删除的行：

命令是如何复制的？是否在每个订阅者上发送和处理 SQL 命令？还是 SQL Server 比这更智能一些？

使用事务复制或合并复制发布的表不允许截断。如何复制删除取决于复制的类型及其配置方式。例如，快照复制只是使用批量方法复制表的时间点视图 - 不会跟踪或应用增量更改。事务复制通过读取日志记录并生成适当的事务以将更改应用到订阅者来工作。合并复制使用触发器和元数据表跟踪更改。

相关阅读：优化更改数据的 T-SQL 查询

sql截断命令

1. 它是一个 DDL（数据定义语言）命令，因此 COMMIT 和 ROLLBACK 等命令不适用于该命令（这里的例外是 PostgreSQL 和 MSSQL，它们的 TRUNCATE 命令实现允许在事务中使用该命令）

2. 您无法撤消删除记录的操作，它会自动发生且不可逆（上述例外情况除外 - 但前提是该操作包含在 TRANSACTION 块中且会话未关闭）。在 Oracle 的情况下 - 包括两个隐式提交，一个在执行语句之前，一个在执行语句之后。因此，该命令无法撤回，而运行时错误将导致无论如何提交

3. 从表中删除所有记录，记录不能被限制删除。对于 Oracle，当按分区拆分表时，可以单独截断（TRUNCATE）各个分区，从而可以从表中部分删除所有数据

4. 释放表中数据占用的空间（在 TABLESPACE - 磁盘上）。对于 Oracle - 如果您使用 REUSE STORAGE 子句，则不会回滚数据段，即您将保留分配给表的已删除行的空间，如果要重新加载表中的数据，这可能会更有效. 高分将被重置

5. 将分配给表的 SEQUENCE 值重置为零。但是，可以使用以下选项：RESTART IDENTITY 或 CONTINUE IDENTITY

6. TRUNCATE 比 DELETE 快得多

7. TRUNCATE 生成的重做和撤消的数量可以忽略不计

8. TRUNCATE 操作使不可用的索引再次可用

9. 当启用的外键引用另一个表时，不能使用 TRUNCATE，那么您可以：

• 执行命令：DROP CONSTRAINT，然后TRUNCATE，然后通过CREATE CONSTRAINT或者
• 执行命令：SET FOREIGN_KEY_CHECKS = 0; 然后 TRUNCATE，然后： SET_FOREIGN_KEY_CHECKS = 1；

10. TRUNCATE 需要独占表锁，因此关闭独占表锁是一种防止对表进行 TRUNCATE 操作的方法

11. DML 触发器在执行 TRUNCATE 后不会触发（因此在这种情况下要非常小心，如果在表中定义了删除触发器来执行自动表清理或行删除后的登录操作，则不应使用 TRUNCATE）。在 Oracle 上，触发 DDL 触发器

sql删除命令

1. 它是一个 DML（数据操作语言）命令，因此以下命令用于该命令：COMMIT 和 ROLLBACK

2. 可以使用 ROLLBACK 命令撤消删除记录的操作

3. 从表中删除全部或部分记录，可以使用 WHERE 子句限制要删除的记录

4. 不释放表中数据占用的空间（在 TABLESPACE - 磁盘上）

5. 不重置分配给表的 SEQUENCE 值

6. DELETE 比 TRUNCATE 慢得多

7. DELETE 生成少量重做和大量撤消

8. DELETE 操作不会使不可用的索引再次可用

9. DELETE 如果启用的外键引用另一个表，可以（或不）应用取决于外键配置（如果没有），请：

• 执行命令：DROP CONSTRAINT，然后TRUNCATE，然后通过CREATE CONSTRAINT或者
• 执行命令：SET FOREIGN_KEY_CHECKS = 0; 然后 TRUNCATE，然后： SET_FOREIGN_KEY_CHECKS = 1；

10. DELETE 需要共享表锁

11. 触发器触发

更多细节： https ://rozwoj-oprogramowania.pl/en/blog/databases/truncate-vs-delete.html

问候