В каких случаях предпочтительнее использовать инструкцию truncate table а не delete

Приветствую всех на сайте Info-Comp.ru! Сегодня я расскажу Вам об отличиях, которые существуют между инструкциями TRUNCATE TABLE и DELETE в языке T-SQL, Вы узнаете, в каких случаях лучше использовать DELETE, а в каких предпочтительней TRUNCATE.

Для того чтобы удалить данные из таблицы в Microsoft SQL Server можно использовать несколько инструкций, в частности DELETE и TRUNCATE TABLE. Эти операторы похожи, так как их предназначение одно – это удаление строк из таблицы. В связи с этим у многих возникают вопросы, например – а в чем отличие TRUNCATE от DELETE? или Когда лучше использовать TRUNCATE, а когда DELETE? Подобные вопросы задают и на собеседованиях, ведь понимание различий между этими операторами помогает разработчикам SQL эффективно работать с данными, строить приложения и запросы.

Заметка! Сборник статей для изучения языка T-SQL и Microsoft SQL Server – в этом материале я собрал лучшие свои статьи по SQL Server и сгруппировал их по уровню подготовки.

Для начала давайте дадим короткое определение этим двум операторам.

Содержание

Что такое DELETE?
Что такое TRUNCATE TABLE?
Сравнительная таблица отличий TRUNCATE TABLE и DELETE
Пример отличия в работе TRUNCATE TABLE от DELETE

DELETE – это SQL инструкция языка T-SQL, которая удаляет одну или несколько строк из таблицы или представления в Microsoft SQL Server, при этом эта операция всегда полностью регистрируется в журнале транзакций, т.е. каждая удаленная строка.

Что такое TRUNCATE TABLE?

TRUNCATE TABLE – это SQL инструкция языка T-SQL, которая удаляет все строки в таблице, не записывая в журнал транзакций удаление отдельных строк данных. TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, но она выполняется быстрее и требует меньше ресурсов.

Сравнительная таблица отличий TRUNCATE TABLE и DELETE

Теперь давайте сформируем сравнительную таблицу, в которой отразим все отличия и особенности работы TRUNCATE TABLE и DELETE в языке T-SQL.

DELETE	TRUNCATE TABLE
DELETE – это оператор DML (Data Manipulation Language)	TRUNCATE TABLE – это оператор DDL (Data Definition Language);
DELETE может удалить часть данных за счет использования WHERE	TRUNCATE TABLE удаляет все данные из таблицы, секцию WHERE использовать нельзя
DELETE записывает в журнал транзакций удаление каждой строки	TRUNCATE TABLE удаляет все строки в таблице, не записывая при этом удаление отдельных строк данных в журнал транзакций
Для DELETE требуется больший объем журнала транзакций	Для TRUNCATE TABLE требуется меньший объем журнала транзакций, за счет того, что она не заносит в журнал запись для каждой удаляемой строки
DELETE выполняется медленнее	TRUNCATE TABLE выполняется быстрее также за счет меньшего использования журнала транзакций
DELETE удаляет строки по одной за раз	TRUNCATE TABLE удаляет все строки в таблице путем освобождения страниц
DELETE создает блокировку на каждую строку, которую требуется удалить из таблицы	TRUNCATE TABLE блокирует в целом таблицу и страницу, чтобы удалить все записи
DELETE сохраняет счетчик идентификаторов и не сбрасывает его до начального значения	TRUNCATE TABLE сбрасывает счетчик идентификаторов до начального значения
DELETE может активировать триггер	TRUNCATE TABLE не может активировать триггер, поскольку в данном случае в журнал не записываются удаление отдельных строк
DELETE сохраняет статистику объекта и выделенное пространство	TRUNCATE TABLE освобождает все страницы данных таблицы, поэтому TRUNCATE удаляет всю статистику и выделенное пространство
DELETE можно использовать, если таблица является частью индексированного представления	TRUNCATE TABLE нельзя использовать, если таблица является частью индексированного представления
Чтобы использовать DELETE, необходимы соответствующие права на удаление данных	Чтобы использовать TRUNCATE TABLE, у Вас должны быть права на ALTER TABLE, т.е. на изменение таблицы

Заметка! Чем отличаются функции от хранимых процедур в T-SQL.

Таким образом, проанализировав таблицу, Вы можете решить, в каком случае для Вас будет лучше использовать TRUNCATE TABLE, а в каком DELETE.

Самым очевидным здесь является то, что если Вам нужно удалить не все данные из таблицы, а только часть, т.е. по условию WHERE, то Вы можете использовать только DELETE.

А, например, если Вам приходится периодически очищать всю таблицу, и значения идентификаторов не важны, то вполне возможно предпочтительней будет использовать TRUNCATE TABLE, так как это будет быстрей, и ресурсов затрачиваться будет меньше.

Пример отличия в работе TRUNCATE TABLE от DELETE

Для примера давайте создадим таблицу со столбцом идентификаторов, затем добавим в нее данные и удалим их сначала инструкцией DELETE, а потом попробуем выполнить то же самое только инструкцией TRUNCATE TABLE (предварительно пересоздав таблицу).

Создание тестовой таблицы

  
   CREATE TABLE Goods(
      ProductId INT IDENTITY(1,1) NOT NULL,
      ProductName VARCHAR(100) NOT NULL
   );

Выполняем удаление с помощью DELETE

   
   --Добавляем данные
   INSERT INTO Goods
      VALUES ('Компьютер'),
             ('Монитор'),
             ('Принтер');
        
   --Выборка данных                   
   SELECT * FROM Goods;
        
   --Удаляем все данные инструкцией DELETE
   DELETE Goods;
        
   --Снова добавляем данные
   INSERT INTO Goods
      VALUES ('Компьютер'),
             ('Монитор'),
             ('Принтер');
        
   --Выборка данных
   SELECT * FROM Goods;

Выполняем удаление инструкцией TRUNCATE TABLE

   
   --Добавляем данные
   INSERT INTO Goods
      VALUES ('Компьютер'),
             ('Монитор'),
             ('Принтер');

   --Выборка данных
   SELECT * FROM Goods;

   --Удаляем все данные инструкцией TRUNCATE TABLE
   TRUNCATE TABLE Goods;

  --Снова добавляем данные
  INSERT INTO Goods
     VALUES ('Компьютер'),
            ('Монитор'),
            ('Принтер');

   --Выборка данных
   SELECT * FROM Goods;

Как видите, в первом случае счетчик не сбросился, и нумерация продолжилась, а во втором счетчик начался с самого начала.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.

На сегодня это все, пока!

Источник

Here is a summary of some important differences between these sql commands:

sql truncate command:

1) It is a DDL (Data Definition Language) command, therefore commands such as COMMIT and ROLLBACK do not apply to this command (the exceptions here are PostgreSQL and MSSQL, whose implementation of the TRUNCATE command allows the command to be used in a transaction)

2) You cannot undo the operation of deleting records, it occurs automatically and is irreversible (except for the above exceptions — provided, however, that the operation is included in the TRANSACTION block and the session is not closed). In case of Oracle — Includes two implicit commits, one before and one after the statement is executed. Therefore, the command cannot be withdrawn while a runtime error will result in commit anyway

3) Deletes all records from the table, records cannot be limited to deletion. For Oracle, when the table is split per partition, individual partitions can be truncated (TRUNCATE) in isolation, making it possible to partially remove all data from the table

4) Frees up the space occupied by the data in the table (in the TABLESPACE — on disk). For Oracle — if you use the REUSE STORAGE clause, the data segments will not be rolled back, i.e. you will keep space from the deleted rows allocated to the table, which can be a bit more efficient if the table is to be reloaded with data. The high mark will be reset

5) TRUNCATE works much faster than DELETE

6) Oracle Flashback in the case of TRUNCATE prevents going back to pre-operative states

7) Oracle — TRUNCATE cannot be granted (GRANT) without using DROP ANY TABLE

The TRUNCATE operation makes unusable indexes usable again

9) TRUNCATE cannot be used when the enabled foreign key refers to another table, then you can:

execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10) TRUNCATE requires an exclusive table lock, therefore, turning off exclusive table lock is a way to prevent TRUNCATE operation on the table

11) DML triggers do not fire after executing TRUNCATE (so be very careful in this case, you should not use TRUNCATE, if a delete trigger is defined in the table to perform an automatic table cleanup or a logon action after row deletion). On Oracle, DDL triggers are fired

12) Oracle — TRUNCATE cannot be used in the case of: database link
13) TRUNCATE does not return the number of records deleted

14) Transaction log — one log indicating page deallocation (removes data, releasing allocation of data pages used for storing table data and writes only page deallocations to the transaction log) — faster execution than DELETE. TRUNCATE only needs to adjust the pointer in the database to the table (High Water Mark) and the data is immediately deleted, therefore it uses less system resources and transaction logs

15) Performance (acquired lock) — table and page lock — does not degrade performance during execution

16) TRUNCATE cannot be used on tables involved in transactional replication or merge replication

sql delete command:

1) It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK

2) You can undo the operation of removing records by using the ROLLBACK command

3) Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause

4) Does not free the space occupied by the data in the table (in the TABLESPACE — on the disk)

5) DELETE works much slower than TRUNCATE

6) Oracle Flashback works for DELETE

7) Oracle — For DELETE, you can use the GRANT command

The DELETE operation does not make unusable indexes usable again

9) DELETE in case foreign key enabled refers to another table, can (or not) be applied depending on foreign key configuration (if not), please:

execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10) DELETE requires a shared table lock

11) Triggers fire

12) DELETE can be used in the case of: database link

13) DELETE returns the number of records deleted

14) Transaction log — for each deleted record (deletes rows one at a time and records an entry in the transaction log for each deleted row) — slower execution than TRUNCATE. The table may still contain blank pages after executing the DELETE statement. DELETE needs to read records, check constraints, update block, update indexes, and generate redo / undo. All of this takes time, hence it takes time much longer than with TRUNCATE

15) Performance (acquired lock) — record lock — reduces performance during execution — each record in the table is locked for deletion

16) DELETE can be used on a table used in transactional replication or merge replication

Источник

I have some tables that I build as a part of my report rollup. I don’t need them afterwards at all. Someone mentioned to truncate them as it would be faster.

juan

80.1k52 gold badges161 silver badges195 bronze badges

asked Sep 25, 2008 at 20:02

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

If you actually want to remove the table definitions as well as the data, simply drop the tables.

See this MSDN article for more info

answered Sep 25, 2008 at 20:07

JoshLJoshL

10.7k11 gold badges55 silver badges61 bronze badges

DROP TABLE deletes the table.

TRUNCATE TABLE empties it, but leaves its structure for future data.

answered Sep 25, 2008 at 20:03

ceejayozceejayoz

176k40 gold badges299 silver badges365 bronze badges

DROP and TRUNC do different things:

TRUNCATE TABLE

Removes all rows from a table without
logging the individual row deletions.
TRUNCATE TABLE is similar to the
DELETE statement with no WHERE clause;
however, TRUNCATE TABLE is faster and
uses fewer system and transaction log
resources.

DROP TABLE

Removes one or more table definitions
and all data, indexes, triggers,
constraints, and permission
specifications for those tables.

As far as speed is concerned the difference should be small. And anyway if you don’t need the table structure at all, certainly use DROP.

answered Sep 25, 2008 at 20:10

daremondaremon

4,8862 gold badges27 silver badges27 bronze badges

I think you means the difference between DELETE TABLE and TRUNCATE TABLE.

DROP TABLE

remove the table from the database.

DELETE TABLE

without a condition delete all rows. If there are trigger and references then this will process for every row. Also a index will be modify if there one.

TRUNCATE TABLE

set the row count zero and without logging each row. That it is many faster as the other both.

answered Sep 25, 2008 at 20:06

Horcrux7Horcrux7

23.7k21 gold badges98 silver badges156 bronze badges

None of these answer point out an important difference about these two operations. Drop table is an operation that can be rolled back. However, truncate cannot be rolled back [‘TRUNCATE TABLE’ can be rolled back as well]. In this way dropping a very large table can be very expensive if there are many rows, because they all have to be recorded in a temporary space in case you decide to roll it back.

Usually, if I want to get rid of a large table, I will truncate it, then drop it. This way the data will be nixed without record, and the table can be dropped, and that drop will be very inexpensive because no data needs to be recorded.

It is important to point out though that truncate just deletes data, leaving the table, while drop will, in fact, delete the data and the table itself. (assuming foreign keys don’t preclude such an action)

SolutionYogi

31.7k12 gold badges70 silver badges78 bronze badges

answered Sep 25, 2008 at 20:16

Nathan FegerNathan Feger

19.1k11 gold badges62 silver badges71 bronze badges

DROP Table

DROP TABLE [table_name];

The DROP command is used to remove a table from the database. It is a DDL command. All the rows, indexes and privileges of the table will also be removed. DROP operation cannot be rolled back.

DELETE Table

DELETE FROM [table_name]
WHERE [condition];

DELETE FROM [table_name];

The DELETE command is a DML command. It can be used to delete all the rows or some rows from the table based on the condition specified in WHERE clause. It is executed using a row lock, each row in the table is locked for deletion. It maintain the transaction log, so it is slower than TRUNCATE.
DELETE operations can be rolled back.

TRUNCATE Table

TRUNCATE TABLE [table_name];

The TRUNCATE command removes all rows from a table. It won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster than DELETE. It is executed using a table lock and whole table is locked for remove all records. It is a DDL command. TRUNCATE operations cannot be rolled back.

answered Dec 6, 2017 at 5:37

OptimizerOptimizer

6877 silver badges7 bronze badges

TRUNCATE TABLE keeps all of your old indexing and whatnot. DROP TABLE would, obviously, get rid of the table and require you to recreate it later.

Filip De Vos

11.5k1 gold badge48 silver badges60 bronze badges

answered Sep 25, 2008 at 20:04

Kevin FairchildKevin Fairchild

10.9k6 gold badges32 silver badges52 bronze badges

Drop gets rid of the table completely, removing the definition as well. Truncate empties the table but does not get rid of the definition.

answered Sep 25, 2008 at 20:04

DMKingDMKing

1,7051 gold badge10 silver badges13 bronze badges

Truncating the table empties the table. Dropping the table deletes it entirely. Either one will be fast, but dropping it will likely be faster (depending on your database engine).

If you don’t need it anymore, drop it so it’s not cluttering up your schema.

answered Sep 25, 2008 at 20:04

Dan UdeyDan Udey

2,9672 gold badges21 silver badges18 bronze badges

DELETE TableA instead of TRUNCATE TableA?
A common misconception is that they do the same thing. Not
so. In fact, there are many differences between the two.

DELETE is a logged operation on a per row basis. This means
that the deletion of each row gets logged and physically deleted.

You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.

TRUNCATE is also a logged operation, but in a different way.
TRUNCATE logs the deallocation of the data pages in which the data
exists. The deallocation of data pages means that your data
rows still actually exist in the data pages, but the
extents have been marked as empty for reuse. This is what
makes TRUNCATE a faster operation to perform over DELETE.

You cannot TRUNCATE a table that has any foreign key
constraints. You will have to remove the contraints, TRUNCATE the
table, and reapply the contraints.

TRUNCATE will reset any identity columns to the default seed
value.

answered Oct 5, 2013 at 19:58

RajaRaja

513 bronze badges

truncate removes all the rows, but not the table itself, it is essentially equivalent to deleting with no where clause, but usually faster.

answered Sep 25, 2008 at 20:04

mmaibaummmaibaum

2,34913 silver badges11 bronze badges

I have a correction for one of the statements above… «truncate cannot be rolled back»

Truncate can be rolled back. There are some cases when you can’t do a truncate or drop table, such as when you have a foreign key reference. For a task such as monthly reporting, I’d probably just drop the table once I didn’t need it anymore. If I was doing this rollup reporting more often then I’d probably keep the table instead and use truncate.

Hope this helps, here’s some more info that you should find useful…

Please see the following article for more details:
http://sqlblog.com/blogs/denis_gobo/archive/2007/06/13/1458.aspx

Also, for more details on delete vs. truncate, see this article:
http://www.sql-server-performance.com/faq/delete_truncate_difference_p1.aspx

Thanks!
Jeff

answered Sep 25, 2008 at 20:59

JeffJeff

3883 silver badges10 bronze badges

TRUNCATE TABLE is functionally
identical to DELETE statement with no
WHERE clause: both remove all rows in
the table. But TRUNCATE TABLE is
faster and uses fewer system and
transaction log resources than DELETE.

The DELETE statement removes rows one
at a time and records an entry in the
transaction log for each deleted row.
TRUNCATE TABLE removes the data by
deallocating the data pages used to
store the table’s data, and only the
page deallocations are recorded in the
transaction log.

TRUNCATE TABLE removes all rows from a
table, but the table structure and its
columns, constraints, indexes and so
on remain. The counter used by an
identity for new rows is reset to the
seed for the column. If you want to
retain the identity counter, use
DELETE instead. If you want to remove
table definition and its data, use the
DROP TABLE statement.

You cannot use TRUNCATE TABLE on a
table referenced by a FOREIGN KEY
constraint; instead, use DELETE
statement without a WHERE clause.
Because TRUNCATE TABLE is not logged,
it cannot activate a trigger.

TRUNCATE TABLE may not be used on
tables participating in an indexed
view.

From http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx

answered Sep 26, 2008 at 1:12

Ricardo CRicardo C

2,20520 silver badges24 bronze badges

In the SQL standard, DROP table removes the table and the table schema — TRUNCATE removes all rows.

Marcus Adams

52.8k9 gold badges90 silver badges143 bronze badges

answered Sep 25, 2008 at 20:04

hangyhangy

10.7k6 gold badges43 silver badges63 bronze badges

The answers here match up to the question, but I’m going to answer the question you didn’t ask. «Should I use truncate or delete?» If you are removing all rows from a table, you’ll typically want to truncate, since it’s much much faster. Why is it much faster? At least in the case of Oracle, it resets the high water mark. This is basically a dereferencing of the data and allows the db to reuse it for something else.

answered Sep 25, 2008 at 20:12

Dan CoatesDan Coates

1,9923 gold badges16 silver badges21 bronze badges

DELETE VS TRUNCATE

The DELETE statement removes rows one at a time and records an entry in the transaction
log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data
pages used to store the table data and records only the page deallocations in the
transaction log
We can use WHERE clause in DELETE but in TRUNCATE you cannot use it
When the DELETE statement is executed using a row lock, each row in the table is locked
for deletion. TRUNCATE TABLE always locks the table and page but not each row
After a DELETE statement is executed, the table can still contain empty pages.If the
delete operation does not use a table lock, the table (heap) will contain many empty
pages. For indexes, the delete operation can leave empty pages behind, although these
pages will be deallocated quickly by a background cleanup process
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns,
constraints, indexes, and so on remain
DELETE statement doesn’t RESEED identity column but TRUNCATE statement RESEEDS the
IDENTITY column
You cannot use TRUNCATE TABLE on tables that:
1. Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a
  foreign key that references itself.)
2. Participate in an indexed view.
3. Are published by using transactional replication or merge replication
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual
row deletions

Brad Gilbert

33.8k11 gold badges77 silver badges129 bronze badges

answered Apr 7, 2010 at 9:06

Drop
drop whole table and all its structure

truncate
delete all rows from table
it is different from delete that it also delete indexes of rows

answered Mar 29, 2018 at 8:36

Delete Statement

Delete Statement delete table rows and return the number of rows is deleted from the table.in this statement, we use where clause to deleted data from the table

Delete Statement is slower than Truncate statement because it deleted records one by one

Truncate Statement

Truncate statement Deleted or removing all the rows from the table.

It is faster than the Delete Statement because it deleted all the records from the table
Truncate statement not return the no of rows are deleted from the table

Drop statement

Drop statement deleted all records as well as the structure of the table

answered Mar 24, 2018 at 17:39

Zubair SaifZubair Saif

1,1061 gold badge14 silver badges29 bronze badges

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to
COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back … As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.

From: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

answered Dec 3, 2008 at 6:03

Источник

Introduction

In this article we’ll jump straight to the point in discussing delete vs truncate in Postgres SQL, what the differences are, and when each is used.

Delete and truncate operations are useful when managing data in both small and large tables. Delete is often used when you want to clear data from small tables. Truncate on the other hand is useful for large tables since it is able to clear all the data at once. When using a truncate statement, the operation is faster because it involves removing all rows in the table without scanning them as does with a delete statement. In additions, truncate statement reclaims the storage right away after removing the rows data, thus it does not require subsequent VACUMM operation to obtain the storage. Delete will require the VACUMM operation to restore the memory. This makes the TRUNCATE TABLE operation more efficient in almost all tables.

TRUNCATE and DELETE operations can be done together with other SQL conditions depending on the target results. In this article, we will discuss the application of the two statement that are used when eliminating data from tables.
DELETE statement with example applications.

To perform a successful delete statement, you need to provide the table from which the delete operation is done and the condition used to manage the delete process. The syntax of a normal delete statement is given below:

1 2	DELETE FROM TABLE WHERE condition;

The WHERE clause in this case is optional but omitting it will result to a removal of all the rows in the specified table. The delete statement return the total number of rows affected. Otherwise, if no row was affected through the condition, it returns zero. When we need to view the value of the affected rows, we use the RETURNING clause as indicated in the examples below.

Initially, we check the data in our delete_test table using the select statement:

1
2
3
4
5
6
7
8

Then, we perform a delete on the table using delete statement:

1 2	DELETE FROM delete_test WHERE id = 4;

Figure 1: Returns 1 as the total number of rows deleted
After running another select statement, the result is as follows:

To delete all rows from the table we use the following statement:

1	DELETE FROM delete_test;

And the results are as follows:

TRUNCATE TABLE Statement.

As mentioned earlier, TRUNCATE TABLE statement is normally used when removing all rows from a specified table. We start by creating a truncate_test table and insert some values in it as shown below.

To remove all data from the able table we use the following statement:

Syntax

1	TRUNCATE TABLE TABLE_NAME

Sql statement

1	TRUNCATE TABLE truncate_test;

The figure below shows the result after truncate operation:

Truncate statement can be used to remove all the data from multiple tables using the following syntax:

1	TRUNCATE TABLE table_name1, table_name2, …;

In our case, we can use the following syntax to remove data from delete_test and the truncate_test tables all at once.

1	TRUNCATE TABLE delete_test, truncate_test;

Conclusion

We hope you now clearly understand the difference between delete vs truncate in SQL. We demonstrated some examples using PostgresSQL so you could see it in action.

If you have any questions please don’t hesitate to reach out to us at Object Rocket where we take the complexity of managing production databases out of your hands.

Источник

DELETE -> the database engine finds and removes the row from the
relevant data pages and all index pages where the row is entered.
Thus, the more indexes the longer the delete takes.

Yes, though there are two options here. Rows may be deleted from nonclustered indexes row-by-row by the same operator that performs the base table deletes. This is known as a narrow (or per-row) update plan:

Or, the nonclustered index deletions may be performed by separate operators, one per nonclustered index. In this case (known as a wide, or per-index update plan) the complete set of actions is stored in a worktable (eager spool) before being replayed once per index, often explicitly sorted by the particular nonclustered index’s keys to encourage a sequential access pattern.

TRUNCATE -> simply removes all the table’s data pages en masse making
this a more efficient option for deleting the contents of a table.

Yes. TRUNCATE TABLE is more efficient for a number of reasons:

Fewer locks may be needed. Truncation typically requires only a single schema modification lock at the table level (and exclusive locks on each extent deallocated). Deletion might acquire locks at a lower (row or page) granularity as well as exclusive locks on any pages deallocated.
Only truncation guarantees that all pages are deallocated from a heap table. Deletion may leave empty pages in a heap even if an exclusive table lock hint is specified (for example if a row-versioning isolation level is enabled for the database).
Truncation is always minimally logged (regardless of the recovery model in use). Only page deallocation operations are recorded in the transaction log.
Truncation can use deferred drop if the object is 128 extents or larger in size. Deferred drop means the actual deallocation work is performed asynchronously by a background server thread.

How do different recovery modes affect each statement? Is there is any
effect at all?

Deletion is always fully logged (every row deleted is recorded in the transaction log). There are some small differences in the content of log records if the recovery model is other than FULL, but this is still technically full logging.

When deleting, are all indexes scanned or only those where the row is?
I would assume all indexes are scanned (and not seeked?)

Deleting a row in an index (using either the narrow or wide update plans shown previously) is always an access by key (a seek). Scanning the whole index for each row deleted would be horribly inefficient. Let’s look again at the per-index update plan shown earlier:

Execution plans are demand-driven pipelines: parent operators (to the left) drive child operators to do work by requesting a row at a time from them. The Sort operators are blocking (they must consume their whole input before producing the first sorted row), but they are still being driven by their parent (the Index Delete) requesting that first row. The Index Delete pulls a row at a time from the completed Sort, updating the target nonclustered index for each row.

In a wide update plan, you will often see columns being added to the row stream by the base table update operator. In this case, the Clustered Index Delete adds nonclustered index key columns to the stream. This data is required by the storage engine to locate the row to remove from the nonclustered index:

How are the commands replicated? Is the SQL command sent and processed
on each subscriber? Or is SQL Server a bit more intelligent than that?

Truncation is not allowed on a table that is published using transactional or merge replication. How deletions are replicated depends on the type of replication and how it is configured. For example, snapshot replication just replicates a point-in-time view of the table using bulk methods — incremental changes are not tracked or applied. Transactional replication works by reading log records and generating appropriate transactions to apply the changes at subscribers. Merge replication tracks changes using triggers and metadata tables.

Related reading: Optimizing T-SQL queries that change data

Источник