Truncate table sql описание: TRUNCATE TABLE (Transact-SQL) — SQL Server

Оператор SQL TRUNCATE: синтаксис, примеры

Оператор SQL TRUNCATE служит для очистки таблицы от всех данных. Оператор SQL TRUNCATE аналогичен оператору SQL DELETE применяемом без оператора SQL WHERE. Основные отличия:

• Оператор SQL TRUNCATE не ведет запись об удаленных данных в журнал событий.
• SQL DELETE осуществляет блокировку построчно, оператор SQL TRUNCATE по всей странице целиком. Вследствие этого, оператор SQL TRUNCATE не возвращает никакого значения, SQL DELETE же, возвращает количество удаленных строк.
• После применения оператора SQL DELETE возможно сделать откат операции и восстановить удаленные данные (команда ROLLBACK). При применении оператора SQL TRUNCATE этого сделать нельзя, однако в СУБД MS SQL Server, оператор может применяться в транзакциях.

Реализация оператора SQL TRUNCATE различна для каждой СУБД, поэтому рекомендуется обращаться к документации используемой системы.

Оператор SQL TRUNCATE имеет следующий синтаксис:

TRUNCATE TABLE Table_name

Примеры оператора SQL TRUNCATE. Имеется следующая таблица Artists:

Пример 1. Удалить все данные из таблицы используя оператор SQL TRUNCATE:

TRUNCATE TABLE Artists

Как было сказано выше, аналогичную задачу можно решить оператором SQL DELETE без использования оператора SQL WHERE:

DELETE FROM Artists

TRUNCATE TABLE оператор MySQL — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать MySQL оператор TRUNCATE TABLE с синтаксисом и примерами.

Описание

TRUNCATE TABLE используется для очищения всех записей из таблицы в MySQL. Он выполняет ту же функцию, что и оператор DELETE, без предложения WHERE.

Предупреждение. Если вы полностью очищаете таблицу, то оператор TRUNCATE TABLE нельзя откатить.

Синтаксис

Синтаксис TRUNCATE TABLE в MySQL:
TRUNCATE TABLE [database_name.]table_name;

Параметры или аргументы

database_name — необязательный. Если указан, то это имя базы данных.
table_name — таблица, которую вы хотите очистить.

Примечание

• Когда вы очищаете таблицу, счетчики AUTO_INCREMENT в таблице будут сброшены.
• MySQL очищает таблицу, удаляя и создавая таблицу вновь. Таким образом, триггеры DELETE для таблицы не срабатывают во время очистки.
• Начиная с MySQL 5.5, вы не можете очистить таблицу InnoDB, на которую ссылается внешний ключ в другой таблице.
• Начиная с MySQL 5.6, вы не можете очистить таблицу NDB, на которую ссылается внешний ключ в другой таблице.

Пример

В MySQL очистка таблицы — это быстрый способ очистить записи из таблицы, если вам не нужно беспокоиться об откате. В большинстве случаев MySQL обрабатывает процесс очистки таблицы несколько иначе, чем другие базы данных, такие как Oracle или SQL Server.

Рассмотрим пример использования TRUNCATE TABLE в MySQL.
Например:

TRUNCATE TABLE customers;

В этом примере очищается таблица customers, и удаляется все записи из этой таблицы.
Он будет эквивалентен следующему оператору DELETE в MySQL:

Оба этих оператора приведут к удалению всех данных из таблицы customers. Основное различие между ними состоит в том, что оператор DELETE вы можете откатить, если выберете, но вы не сможете откатить оператор TRUNCATE TABLE. Так что будьте предельно внимательны.

Рассмотрим еще один пример, где мы используем префикс имени таблицы с именем базы данных.
Например:

TRUNCATE TABLE best.products;

В этом примере очищается таблица, называемая products в базе данных best.

Инструкция TRUNCATE TABLE в Transact-SQL | Info-Comp.ru

В Microsoft SQL Server для удаления данных из таблицы можно использовать инструкцию DELETE, но также можно использовать и инструкцию TRUNCATE TABLE, поэтому сегодня мы поговорим о том, чем отличается TRUNCATE TABLE от DELETE и какие преимущества нам дает эта инструкция.

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

Что такое TRUNCATE TABLE?

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

Преимущества TRUNCATE TABLE и отличия от DELETE

• TRUNCATE TABLE требуется меньший объем журнала транзакций, так как она в отличие от инструкции DELETE не заносит в журнал транзакций запись для каждой удаляемой строки, напомню что DELETE производит удаление по одной строке отражая все действия в журнале транзакций;
• TRUNCATE TABLE выполняется быстрее по сравнению с инструкцией DELETE также за счет меньшего использования журнала транзакций;
• Используется меньшее количество блокировок. Инструкция TRUNCATE TABLE в отличие от инструкции DELETE блокирует таблицу и страницу, а не каждую строку таблицы;
• Если инструкция TRUNCATE TABLE применяется к таблице, которая содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения. Инструкция DELETE не сбрасывает счетчик столбца идентификаторов;
• Инструкция TRUNCATE TABLE не активирует триггер, поскольку она не записывает в журнал удаление отдельных строк.

Примечание! Преимущества TRUNCATE TABLE в некоторых случаях, как Вы понимаете, могут быть и недостатками.

Инструкция TRUNCATE TABLE удаляет все строки (данные) таблицы, но структура таблицы: столбцы, ограничения, индексы и так далее сохраняются. Для того чтобы полностью удалить таблицу и ее определение, следует использовать инструкцию DROP TABLE.

Ограничения TRUNCATE TABLE

• Если на таблицу ссылается ограничение FOREIGN KEY, то TRUNCATE TABLE использовать нельзя;
• Если таблица является частью индексированного представления, то TRUNCATE TABLE также нельзя использовать;
• Если таблица опубликована с использованием репликации транзакций или репликации слиянием, то инструкцию TRUNCATE TABLE использовать нельзя.

Пример использования инструкции TRUNCATE TABLE

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

Примечание! В качестве SQL сервера у меня выступает версия Microsoft SQL Server 2016 Express.

   
        --Создаем тестовую таблицу
        CREATE TABLE TestTable(
                ProductId INT IDENTITY(1,1) NOT NULL,
                ProductName VARCHAR(100) NOT NULL
        )
        --Добавляем данные
        INSERT INTO TestTable
                VALUES ('Компьютер'),
                           ('Монитор'),
                           ('Принтер')
        --Выборка данных                   
        SELECT * FROM TestTable    
        --Удаляем все данные инструкцией TRUNCATE TABLE
        TRUNCATE TABLE TestTable
        --Снова добавляем данные
        INSERT INTO TestTable
                VALUES ('Компьютер'),
                           ('Монитор'),
                           ('Принтер')
        --Выборка данных
        SELECT * FROM TestTable 

Как видим, счетчик сброшен, инструкция TRUNCATE TABLE отработала.

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

На этом у меня все, пока!

Нравится6Не нравится1

Команда DROP — DROP TABLE, TRUNCATE TABLE, DROP INDEX, DROP DATABASE, DROP SEQUENCE, DROP SYNONYM, DROP PUBLIC SYNONYM

Команда DROP

Используя запрос DROP можно удалить таблицы (TABLE), индексы (INDEX) и базы данных (DATABASE).

DROP TABLE

DROP TABLE, применяемый в базе данных Oracle.
Обычно с таблицей в базе данных связано несколько объектов, например индекс, создаваемый первичным ключом, или ограничение UNIQUE, налагаемое на столбцы таблицы.
При удалении таблицы Oracle автоматически удаляет и любой связанный с ней индекс. Для удаления таблицы из БД необходимо выполнить команду DROP TABLE:

DROP TABLE Пример 1
Удаление таблицы:

DROP TABLE table;

Однако удалить таблицу не всегда столь просто. В любой момент мы можем создать таблицу с ограничениями целостности. Ограничение целостности (Integrityconstraint ) – это правило, устанавливаемое для таблицы и ограничивающее тип данных, которые можно вводить в эту таблицу. Если попытаться удалить таблицу с ограничениями целостности, возвращается сообщение об ошибке следующего вида: «Unique/primary keys in table referenced by foreign keys» (на уникальные/первичные ключи таблицы ссылаются внешние ключи).

Когда существуют ограничения для других таблиц, на которые ссылается удаляемая таблица, можно пользоваться каскадной конструкцией CASCADE CONSTRAINTS:
DROP TABLE. Пример 2
Удаление таблицы с ограничениями целостности:

DROP TABLE table CASCADE CONSTRAINTS;

DROP TABLE, применяемый в mySQL
DROP TABLE. Пример 3

Для удаления таблицы также используется запрос:

DROP TABLE table;

DROP TABLE. Пример 4

В случае, если необходимо установить проверку на существование таблицы при удалении (если существует удалить таблицу) запрос принимает следующий вид:

DROP TABLE IF EXISTS table;

Данный запрос будет выполнен в том случае, если удаляемая таблица существует в базе данных.

DROP INDEX
Данный запрос DROP INDEX используется для удаления индексов в таблице.

DROP INDEX, применяемый в базе данных Oracle:
Когда индекс в базе данных больше не нужен, разработчик может удалить его командой DROP INDEX. После удаления индекса эффективность поиска с использованием столбца или столбцов, ограниченных индексом, больше не повышается и упоминание об индексе исчезает из словаря данных. Индекс, применяемый для первичного ключа, удалить нельзя.

Синтаксис оператора DROP INDEX одинаков для удаления индекса любого типа (уникальности, битовой карты или В-дерева). Чтобы каким-то образом улучшить индекс, нужно сначала удалить его, а потом создать новый.

DROP INDEX. Пример 1

DROP INDEX my_index;

DROP INDEX, применяемый в mySQL:

DROP INDEX. Пример 2
Для удаления индексов (INDEX) используется запрос:

DROP INDEX my_index ON table;

Данный запрос удаляет индексы, указанные в my_index из таблицы table, но она не работает в версиях MySQL до 3.22. В версиях 3.22 и более поздних используется команда:

ALTER TABLE table_name DROP INDEX index_name;

DROP DATABASE

DROP DATABASE. Пример 1

DROP DATABASE database;

Запрос DROP DATABASE удаляет базу данных database.

TRUNCATE TABLE

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

TRUNCATE TABLE, примеры использования TRUNCATE TABLE
TRUNCATE TABLE. Пример 1

TRUNCATE TABLE table;

DROP SEQUENCE
DROP SEQUENCE используется для удаления последовательности.

DROP SEQUENCE. Пример 1

DROP SEQUENCE sequence_name;

DROP SYNONYM
DROP SYNONYM используется для удаления синонимов.

DROP SYNONYM. Пример 1

DROP SYNONYM synonym_name;

Для удаления общих синонимов необходимо воспользоваться командой DROP PUBLIC SYNONYM.

DROP SYNONYM. Пример 2

DROP PUBLIC SYNONYM synonym_name;

SQL — TRUNCATE

Вступление

Оператор TRUNCATE удаляет все данные из таблицы. Это похоже на DELETE без фильтра, но, в зависимости от программного обеспечения базы данных, имеет определенные ограничения и оптимизации.

Синтаксис

• TRUNCATE TABLE имя_таблицы;

замечания

TRUNCATE — это команда DDL (Data Definition Language), и поэтому существуют значительные различия между ней и DELETE (язык манипулирования данными, команда DML). Хотя TRUNCATE может быть средством быстрого удаления больших объемов записей из базы данных, эти различия следует понимать, чтобы решить, подходит ли использование команды TRUNCATE в вашей конкретной ситуации.

• TRUNCATE — это операция с данными. Поэтому триггеры DML (ON DELETE), связанные с таблицей, не срабатывают при выполнении операции TRUNCATE. Хотя это позволит сэкономить большое количество времени для массовых операций удаления, однако вам может понадобиться вручную удалить связанные данные.
• TRUNCATE освободит пространство на диске, используемое удаленными строками, DELETE освободит место
• Если усеченная таблица использует столбцы идентификаторов (MS SQL Server), то семя сбрасывается командой TRUNCATE. Это может привести к проблемам ссылочной целостности
• В зависимости от используемых ролей безопасности и используемого варианта SQL у вас могут не быть необходимых разрешений для выполнения команды TRUNCATE

Удаление всех строк из таблицы Employee

TRUNCATE TABLE Employee;

Использование таблицы усечений часто бывает лучше, чем использование DELETE TABLE, поскольку оно игнорирует все индексы и триггеры и просто удаляет все.

Удалить таблицу — это операция на основе строки, это означает, что каждая строка удаляется. Таблица усечения — это операция страницы с данными, перераспределенная на всю страницу данных. Если у вас есть таблица с миллионом строк, будет намного быстрее обрезать таблицу, чем было бы использовать оператор таблицы удалений.

Хотя мы можем удалять определенные строки с помощью DELETE, мы не можем TRUNCATE определенные строки, мы можем только TRUNCATE все записи сразу. Удаление всех строк, а затем добавление новой записи будет продолжать добавление значения «Приоритет» с автоматическим добавлением из ранее вставленного значения, где, как и в Truncate, значение первичного ключа Auto Incremental также будет сброшено и начнется с 1.

Обратите внимание, что при усечении таблицы внешние ключи не должны присутствовать , иначе вы получите сообщение об ошибке.

Какая разница между TRUNCATE и DELETE в SQL

В чем разница между TRUNCATE и DELETE в SQL?

Если ваш ответ зависит от платформы, укажите это.

ОТВЕТЫ

Ответ 1

Здесь список отличий. Я выдвинул на первый план специфичные для Oracle функции, и, надеюсь, сообщество также может добавить специфические отличия других поставщиков. Различия, которые являются общими для большинства поставщиков, могут идти непосредственно под заголовками, а различия выделены ниже.

Если вы хотите быстро удалить все строки из таблицы, и вы действительно уверены, что хотите это сделать, и у вас нет внешних ключей к таблицам, то TRUNCATE, вероятно, будет быстрее, чем DELETE.,

Различные системные проблемы должны быть рассмотрены, как подробно описано ниже.

Удалить — это DML, Truncate — это DDL (Что такое DDL и DML?)

Переменная поставщиком

SQL * Сервер

Усечение можно откатить.

PostgreSQL

Усечение можно откатить.

оракул

Поскольку TRUNCATE является DDL, он включает в себя две фиксации, одну до и одну после выполнения оператора. Поэтому усечение нельзя откатить, и сбой в процессе усечения в любом случае приведет к фиксации.

Тем не менее, см. Воспоминание ниже.

Удалить не восстанавливает пространство, Truncate восстанавливает пространство

оракул

Если вы используете предложение REUSE STORAGE, то сегменты данных не выделяются, что может быть несколько более эффективным, если таблицу необходимо перезагрузить данными. Верхний уровень воды сбрасывается.

Удалить можно использовать для удаления всех строк или только подмножества строк. Усечение удаляет все строки.

оракул

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

Удалить можно применять к таблицам и таблицам внутри кластера. Усечение применяется только к таблицам или всему кластеру. (Может быть специфичным для Oracle)

оракул

Удаление не влияет на идентификатор объекта данных, но truncate назначает новый идентификатор объекта данных, если только не было вставки в таблицу с момента ее создания. Даже одна откатная вставка, при которой выполняется откат, приведет к назначению нового идентификатора объекта данных при усечении.,

Воспоминание работает при удалении, но усечение предотвращает возврат к состояниям до операции.

Однако, начиная с 11gR2, функция FLASHBACK ARCHIVE позволяет это делать, за исключением Express Edition.

Использование FLASHBACK в Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638

переменная

оракул

Удаление может быть предоставлено в таблице другому пользователю или роли, но усечение не может быть выполнено без использования разрешения DROP ANY TABLE.

Удаление создает небольшое количество повторов и большое количество отмен. Усечение генерирует незначительное количество каждого.

оракул

Операция усечения делает неиспользуемые индексы снова пригодными для использования. Удалить не надо.

Усечение не может быть применено, когда включенный внешний ключ ссылается на таблицу. Обработка с удалением зависит от конфигурации внешних ключей.

оракул

Для усечения требуется эксклюзивная блокировка таблицы, для удаления требуется общая блокировка таблицы. Следовательно, отключение блокировок таблицы является способом предотвращения операций усечения таблицы.

Триггеры DML не запускаются в усеченном состоянии.

оракул

Триггеры DDL доступны.

оракул

Усечение не может быть выдано по ссылке в базе данных.

SQL * Сервер

Функция усечения сбрасывает последовательность для типов столбцов IDENTITY, удаление — нет.

В большинстве реализаций оператор DELETE может возвращать клиенту строки, которые были удалены.

Например, в подпрограмме Oracle PL/SQL вы можете:

DELETE FROM employees_temp
WHERE       employee_id = 299 
RETURNING   first_name,
            last_name
INTO        emp_first_name,
            emp_last_name;

Ответ 2

Разница между усечением и удалением приведена ниже:

+----------------------------------------+----------------------------------------------+
|                Truncate                |                    Delete                    |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing     | We can Rollback after delete.                |
| Truncate.                              |                                              |
|                                        |                                              |
| Example:                               | Example:                                     |
| BEGIN TRAN                             | BEGIN TRAN                                   |
| TRUNCATE TABLE tranTest                | DELETE FROM tranTest                         |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
| ROLLBACK                               | ROLLBACK                                     |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table.      | Delete does not reset identity of table.     |
+----------------------------------------+----------------------------------------------+
| It locks the entire table.             | It locks the table row.                      |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language)      | Its DML(Data Manipulation Language)          |
| command.                               | command.                                     |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it.     | We can use WHERE to filter data to delete.   |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate.   | Trigger is fired.                            |
+----------------------------------------+----------------------------------------------+
| Syntax :                               | Syntax :                                     |
| 1) TRUNCATE TABLE table_name           | 1) DELETE FROM table_name                    |
|                                        | 2) DELETE FROM table_name WHERE              |
|                                        |    example_column_id IN (1,2,3)              |
+----------------------------------------+----------------------------------------------+

Ответ 3

DROP

Команда DROP удаляет таблицу из базы данных. Строки, индексы и привилегии всех таблиц также будут удалены. Триггеры DML не будут запущены. Операцию нельзя отменить.

TRUNCATE

TRUNCATE удаляет все строки из таблицы. Операцию нельзя отменить, и триггеры не будут запущены. Таким образом, TRUCATE работает быстрее и не использует столько пространства отмены, как DELETE.

Удалить

Команда DELETE используется для удаления строк из таблицы. Предложение WHERE может использоваться только для удаления некоторых строк. Если условие WHERE не указано, все строки будут удалены. После выполнения операции DELETE вам необходимо COMMIT или ROLLBACK транзакцию, чтобы сделать изменение постоянным или отменить его. Обратите внимание, что эта операция приведет к срабатыванию всех триггеров DELETE в таблице.

От: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Ответ 4

Все хорошие ответы, на которые я должен добавить:

Так как TRUNCATE TABLE является DDL (Data Defination Language), а не DML (Data Manipulation Langauge), Delete Triggers не запускается.

Ответ 5

Сводка удаления и усечения в SQL-сервере
Для полной статьи перейдите по этой ссылке: http://codaffection.com/sql-server-article/delete-vs-truncate-in-sql-server/

Взято из статьи дотнет моб: Удалить Vs Truncate в SQL Server

Ответ 6

С SQL Server или MySQL, если есть PK с автоматическим приращением, truncate будет reset счетчиком.

Ответ 7

«Truncate ничего не записывает». Я бы пошел дальше:

Truncate не выполняется в контексте транзакции.

Преимущество скорости усечения над удалением должно быть очевидным. Это преимущество варьируется от тривиального до огромного, в зависимости от вашей ситуации.

Однако, я видел, что усечение непреднамеренно нарушает ссылочную целостность и нарушает другие ограничения. Власть, которую вы получаете, изменяя данные за пределами транзакции, должна быть сбалансирована с ответственностью, которую вы наследуете, когда вы ходите по канату без сети.

Ответ 8

Да, DELETE работает медленнее, TRUNCATE работает быстрее. Зачем?

DELETE должен читать записи, проверять ограничения, обновлять блок, индексы обновления и генерировать повтор/отмену. Все это требует времени.

TRUNCATE просто настраивает указатель в базе данных для таблицы (знак высокой воды) и poof! данные исчезли.

Это конкретный Oracle, AFAIK.

Ответ 9

TRUNCATE — это оператор DDL, тогда как DELETE — это оператор DML. Ниже приведены различия между ними:

• Поскольку TRUNCATE — это DDL (язык определения данных), он не требует фиксации, чтобы сделать изменения постоянными, И именно по этой причине строки, удаленные с помощью truncate, не могут быть отброшены. С другой стороны, DELETE — это DML (язык манипулирования данными), поэтому требуется явное фиксация, чтобы сделать его действие постоянным.

• TRUNCATE всегда удаляет все строки из таблицы, оставляя таблицу пустой, а структура таблицы неактивна, тогда как DELETE может быть условно удалена, если используется предложение where.

• Строки, удаленные оператором TRUNCATE TABLE, не могут быть восстановлены, и вы не можете указать предложение where в инструкции TRUNCATE.

  Операторы

• TRUNCATE не запускают триггеры, а не триггер delete в DELETE statement

Здесь — очень хорошая ссылка, относящаяся к теме.

Ответ 10

Если вы случайно удалили все данные из таблицы, используя Delete/Truncate. Вы можете откатить совершенную транзакцию. Восстановите последнюю резервную копию и запустите журнал транзакций до того времени, когда должно произойти удаление/усечение.

Связанная информация ниже от сообщения в блоге:

При работе с базой данных мы используем Delete и Truncate, не зная различий между ними. В этой статье мы обсудим разницу между Delete и Truncate в Sql.

Удалять:

• Удалить — это команда DML.
• Оператор удаления выполняется с использованием блокировки строк, каждая строка в таблице блокируется для удаления.
• Мы можем указать фильтры в предложении where.
• Удаляет указанные данные, если существует условие.
• Удалите действия триггера, потому что операция регистрируется индивидуально.
• Медленнее, чем усечение, потому что он ведет логи

усекать

• Усечение — это команда DDL.
• Усеченная таблица всегда блокирует таблицу и страницу, но не каждую строку. Так как она удаляет все данные.
• Невозможно использовать Где условие.
• Удаляет все данные.
• Усеченная таблица не может активировать триггер, поскольку операция не регистрирует удаление отдельных строк.
• Быстрее с точки зрения производительности, потому что он не ведет журналы.

Примечание. С помощью транзакции можно откатить и Удалить, и Обрезать. Если транзакция выполнена, значит зафиксирована, то мы не можем откатить команду Truncate, но мы все равно можем откатить команду Удалить из файлов журнала, так как удаляем записи записи их в файле журнала на случай, если в будущем потребуется откат из файлов журнала.

Если у вас есть ограничение внешнего ключа, относящееся к таблице, которую вы пытаетесь усечь, это не сработает, даже если в ссылающейся таблице нет данных. Это связано с тем, что проверка внешнего ключа выполняется с помощью DDL, а не DML. Это можно обойти, временно отключив ограничение (я) внешнего ключа для таблицы.

Удалить таблицу является зарегистрированной операцией. Таким образом, удаление каждой строки записывается в журнал транзакций, что делает его медленным. Усеченная таблица также удаляет все строки в таблице, но не регистрирует удаление каждой строки, а записывает освобождение страниц данных таблицы, что ускоряет ее.

~ Если вы случайно удалили все данные из таблицы, используя Delete/Truncate. Вы можете откатить совершенную транзакцию. Восстановите последнюю резервную копию и запустите журнал транзакций до того времени, когда должно произойти удаление/усечение.

Ответ 11

В SQL Server 2005 я считаю, что вы можете откат обрезать

Ответ 12

• он быстрее и использует меньше ресурсов системного и транзакционного журнала, чем DELETE.
• удаляет данные, освобождая страницы данных, используемые для хранения данных таблицы, и только журналы освобождения страницы записываются в журнал транзакций.
• удаляет все строки из таблицы, но структура таблицы, ее столбцы, ограничения, индексы и т.д. остаются. Счетчиком, используемым идентификатором для новых строк, является reset для семени для столбца.
• вы не можете использовать TRUNCATE TABLE в таблице, на которую ссылается ограничение FOREIGN KEY. Поскольку TRUNCATE TABLE не регистрируется, он не может активировать триггер.
• нельзя отменить.
• это команда DDL.
• сбрасывает личность таблицы

• удаляет строки по одному и записывает запись в журнал транзакций для каждой удаленной строки.
• если вы хотите сохранить счетчик идентификаторов, вместо этого используйте DELETE. Если вы хотите удалить определение таблицы и ее данные, используйте оператор DROP TABLE.
• может использоваться с или без предложения WHERE
• активирует триггеры.
• можно отбросить назад.
• это команда DML.
• не соответствует reset идентификатору таблицы.

Примечание: DELETE и TRUNCATE оба могут быть отброшены при окружении TRANSACTION, если текущий сеанс не закрыт. Если TRUNCATE написано в редакторе запросов, окруженном TRANSACTION, и если сеанс закрыт, его нельзя отменить, но DELETE можно отбросить назад.

Ответ 13

TRUNCATE может быть откат, если он завернут транзакцию.

Пожалуйста, ознакомьтесь с двумя приведенными ниже ссылками и проверьте сами: —

http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

TRUNCATE vs. DELETE является одним из печально известных вопросов во время интервью с SQL. Просто убедитесь, что вы правильно объяснили это интервьюеру, или это может стоить вам работы. Проблема в том, что мало кто знает, что, скорее всего, они посчитают ответ неправильным, если вы скажете им, что YES Truncate можно отбросить назад.

Ответ 14

Удалить

Команда DELETE используется для удаления строк из таблицы. Предложение WHERE может использоваться только для удаления некоторых строк. Если условие WHERE не указано, все строки будут удалены. После выполнения операции DELETE вам необходимо COMMIT или ROLLBACK транзакцию, чтобы сделать изменение постоянным или отменить его. Обратите внимание, что эта операция приведет к срабатыванию всех триггеров DELETE в таблице.

TRUNCATE

TRUNCATE удаляет все строки из таблицы. Операцию нельзя отменить, и триггеры не будут запущены. Таким образом, TRUCATE работает быстрее и не использует столько пространства отмены, как DELETE.

DROP

Команда DROP удаляет таблицу из базы данных. Строки, индексы и привилегии всех таблиц также будут удалены. Триггеры DML не будут запущены. Операцию нельзя отменить.

---

DROP и TRUNCATE — это команды DDL, тогда как DELETE — это команда DML. Поэтому операции DELETE можно отменить (отменить), в то время как операции DROP и TRUNCATE не могут быть отброшены назад.

От: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Ответ 15

Небольшая поправка к исходному ответу — delete также генерирует значительные количества повтора (поскольку отменяет сам защищенный повтор). Это видно из вывода autotrace:

SQL> delete from t1;

10918 rows deleted.

Elapsed: 00:00:00.58

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=1)
   1    0   DELETE OF 'T1'
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=43 Card=1)




Statistics
----------------------------------------------------------
         30  recursive calls
      12118  db block gets
        213  consistent gets
        142  physical reads
    3975328  redo size
        441  bytes sent via SQL*Net to client
        537  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10918  rows processed

Ответ 16

Вот мой подробный ответ на разницу между DELETE и TRUNCATE в SQL Server

• Удалить данные. Во-первых, оба могут быть использованы для удаления строк из таблицы.
Но DELETE можно использовать для удаления строк не только из таблицы, но также из VIEW или результата OPENROWSET или OPENQUERY с учетом возможностей поставщика.

• FROM Предложение. С помощью DELETE вы также можете удалять строки из одной таблицы/представления/rowset_function_limited на основе строк из другой таблицы, используя другое предложение FROM. В этом разделе FROM вы также можете написать обычные условия JOIN. Фактически вы можете создать оператор DELETE из инструкции SELECT, которая не содержит каких-либо агрегатных функций, заменив SELECT на DELETE и удалив имена столбцов.
С TRUNCATE вы не можете этого сделать.

• WHERE: TRUNCATE не может иметь условия WHERE, но DELETE может. Это означает, что с помощью TRUNCATE вы не можете удалить определенную строку или определенную группу строк.
TRUNCATE TABLE похож на оператор DELETE без предложения WHERE.

• Производительность: TRUNCATE TABLE быстрее и использует меньше ресурсов системного и транзакционного журнала.
И одна из причин — блокировки, используемые любыми утверждениями. Оператор DELETE выполняется с использованием блокировки строк, каждая строка в таблице блокируется для удаления. TRUNCATE TABLE всегда блокирует таблицу и страницу, но не каждую строку.

• Журнал транзакций: оператор DELETE удаляет строки по одному и делает отдельные записи в журнале транзакций для каждой строки.
TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблицы, и записывает только дезадаптации страниц в журнале транзакций.

• Страницы: после выполнения инструкции DELETE таблица может содержать пустые страницы.
TRUNCATE удаляет данные, освобождая страницы данных, используемые для хранения данных таблицы.

• Триггер: TRUNCATE не активирует триггеры удаления в таблице. Поэтому вы должны быть очень осторожны при использовании TRUNCATE. Никогда не следует использовать TRUNCATE, если в Tridger указано триггер, чтобы выполнить некоторые операции автоматической очистки или регистрации при удалении строк.

• Столбец Identity. С TRUNCATE, если таблица содержит столбец идентификатора, счетчик для этого столбца равен reset к начальному значению, определенному для столбца. Если семя не было определено, используется значение по умолчанию 1.
DELETE does not reset идентификатор. Поэтому, если вы хотите сохранить счетчик идентификаторов, вместо этого используйте DELETE.

• Репликация: DELETE может использоваться для таблицы, используемой для репликации транзакций или репликации слияния.
Хотя TRUNCATE не может использоваться против таблиц, участвующих в репликации транзакций или репликации слиянием.

• Откат: оператор DELETE может быть откат.
TRUNCATE также может быть откат, если он заключен в блок TRANSACTION, а сеанс не закрыт. Когда сеанс закрыт, вы не сможете отменить TRUNCATE.

• Ограничения. Оператор DELETE может выйти из строя, если он нарушает триггер или пытается удалить строку, на которую ссылаются данные в другой таблице, с ограничением FOREIGN KEY. Если DELETE удаляет несколько строк, и любая из удаленных строк нарушает триггер или ограничение, оператор отменяется, возвращается ошибка, а строки не удаляются.
И если DELETE используется против View, этот вид должен быть обновляемым представлением.
TRUNCATE не может использоваться против таблицы, используемой в индексированном представлении.
TRUNCATE не может использоваться против таблицы, на которую ссылается ограничение FOREIGN KEY, если только таблица с внешним ключом не ссылается.

Ответ 17

Самое большое различие заключается в том, что truncate является не зарегистрированной операцией, а delete.

Просто это означает, что в случае сбоя базы данных вы не сможете восстановить данные, на которых работает truncate, но с удалением вы можете.

Подробнее здесь

Ответ 18

DELETE Statement: эта команда удаляет только строки из таблицы на основе условия, указанного в предложении where, или удаляет все строки из таблицы, если условие не указано. Но он не освобождает пространство, содержащее таблицу.

Синтаксис оператора SQL DELETE:

УДАЛИТЬ ИЗ table_name [условие WHERE];

Инструкция TRUNCATE: эта команда используется для удаления всех строк из таблицы и освобождения пространства, содержащего таблицу.

Ответ 19

DELETE

DELETE is a DML command
DELETE you can rollback
Delete = Only Delete- so it can be rolled back
In DELETE you can write conditions using WHERE clause
Syntax – Delete from [Table] where [Condition]

TRUNCATE

TRUNCATE is a DDL command
You can't rollback in TRUNCATE, TRUNCATE removes the record permanently
Truncate = Delete+Commit -so we can't roll back
You can't use conditions(WHERE clause) in TRUNCATE
Syntax – Truncate table [Table]

Подробнее см.

http://www.zilckh.com/what-is-the-difference-between-truncate-and-delete/

Ответ 20

Еще одно отличие двух операций заключается в том, что если таблица содержит столбец идентификатора, то счетчик для этого столбца равен reset 1 (или начальному значению для столбца) в TRUNCATE. DELETE не влияет на это.

Ответ 21

Короче говоря, truncate ничего не записывает (так это намного быстрее, но не может быть отменено), тогда как удаление регистрируется (и может быть частью более крупной транзакции, будет откатываться и т.д.). Если у вас есть данные, которые вам не нужны в таблице в dev, обычно лучше обрезать, поскольку вы не рискуете заполнить журнал транзакций

Ответ 22

Большая причина, по которой это удобно, — это когда вам нужно обновить данные в многомиллионной таблице строк, но не хотите ее перестраивать. «Удалить *» будет навсегда, в то время как влияние Truncate на производительность будет незначительным.

Ответ 23

Невозможно выполнить DDL через dblink.

Ответ 24

Я бы прокомментировал сообщение matthieu, но у меня пока нет репутации…

В MySQL счетчик автоматического приращения получает reset с усечением, но не с удалением.

Ответ 25

Это не значит, что truncate ничего не записывает в SQL Server. truncate не записывает никакой информации, но регистрирует страницу удаления данных для таблицы, на которой вы запускали TRUNCATE.

и усеченная запись может быть откат, если мы определяем транзакцию в начале, и мы можем восстановить усеченную запись после ее откат. Но не удается восстановить усеченные записи из резервной копии журнала транзакций после совершения усеченной транзакции.

Ответ 26

Усечение также можно отбросить здесь exapmle

begin Tran
delete from  Employee

select * from Employee
Rollback
select * from Employee

Ответ 27

Усечение и удаление в SQL — это две команды, которые используются для удаления или удаления данных из таблицы. Несмотря на довольно простой характер, команды Sql могут создать массу проблем, пока вы не познакомитесь с деталями перед его использованием.
Неправильный выбор команды может привести либо к очень медленному процессу, либо даже к взлому сегмента журнала, если слишком много данных нужно удалить, а журнала недостаточно. Поэтому важно знать, когда использовать команду truncate и delete в SQL, но перед их использованием вы должны знать о различиях между Truncate и Delete и на их основе, мы должны иметь возможность узнать, когда DELETE является лучшим вариантом для удаления данные или TRUNCATE должны использоваться для очистки таблиц.

Обратите внимание: нажмите здесь

Ответ 28

Выдавая инструкцию TRUNCATE TABLE, вы поручаете SQL Server удалять каждую запись в таблице без регистрации или обработки транзакций.

Ответ 29

УДАЛИТЬ

Когда оператор DELETE выполняется без предложения WHERE, он удалит все строки. Однако, когда вставлена ​​новая запись, значение идентификатора увеличивается с 11 (максимальная идентичность) до 12. Оно не reset, а продолжает увеличиваться.
TRUNCATE

Когда выполняется инструкция TRUNCATE, она удалит все строки. Однако, когда вставлена ​​новая запись, значение идентификатора увеличивается с 11 (что является исходным значением). TRUNCATE сбрасывает значение идентификатора в исходное начальное значение таблицы.

DROP

Ответ 30

DELETE и TRUNCATE оба могут быть отброшены при использовании с TRANSACTION.

Если транзакция завершена, значит COMMITED, то мы не можем отменить команду TRUNCATE, но мы можем по-прежнему откатить команду DELETE из файлов LOG, поскольку DELETE записывает их в файл журнала, если это необходимо для отката в будущем из файлов LOG.

В чем разница между TRUNCATE и DELETE в SQL

Вот мой подробный ответ на разницу между DELETE и TRUNCATE в SQL. Сервер

• Удалить данные . Во-первых, обе можно использовать для удаления строк из таблицы.
Но DELETE можно использовать для удаления строк не только из таблицы, но также из VIEW или результата OPENROWSET или OPENQUERY в зависимости от возможностей поставщика.

• Предложение FROM : с помощью DELETE вы также можете удалять строки из одной таблицы / представления / rowset_function_limited на основе строк из другой таблицы, используя другое предложение FROM. В этом предложении FROM вы также можете написать обычные условия JOIN. Фактически вы можете создать оператор DELETE из оператора SELECT, который не содержит никаких агрегатных функций, заменив SELECT на DELETE и удалив имена столбцов.
С TRUNCATE вы не можете этого сделать.

• WHERE : TRUNCATE не может иметь условий WHERE, а DELETE может. Это означает, что с помощью TRUNCATE вы не можете удалить определенную строку или определенную группу строк.
TRUNCATE TABLE похож на оператор DELETE без предложения WHERE.

• Производительность : TRUNCATE TABLE работает быстрее и использует меньше ресурсов системы и журнала транзакций.
И одна из причин — блокировки, используемые обоими операторами. Оператор DELETE выполняется с использованием блокировки строки, каждая строка в таблице блокируется для удаления. TRUNCATE TABLE всегда блокирует таблицу и страницу, но не каждую строку.

• Журнал транзакций : оператор DELETE удаляет строки по одной и делает отдельные записи в журнале транзакций для каждой строки.
TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблицы, и записывает только освобожденные страницы в журнал транзакций.

• Страницы : после выполнения инструкции DELETE таблица все еще может содержать пустые страницы.
TRUNCATE удаляет данные, освобождая страницы данных, используемые для хранения данных таблицы.

• Триггер : TRUNCATE не активирует триггеры удаления в таблице. Поэтому вы должны быть очень осторожны при использовании TRUNCATE. Никогда не следует использовать TRUNCATE, если в таблице определен триггер удаления для выполнения некоторых действий по автоматической очистке или регистрации при удалении строк.

• Столбец идентификаторов : с помощью TRUNCATE, если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для столбца. Если начальное число не было определено, используется значение по умолчанию 1.
DELETE не сбрасывает счетчик идентификаторов. Поэтому, если вы хотите сохранить счетчик идентификаторов, используйте вместо этого DELETE.

• Репликация : DELETE можно использовать для таблицы, используемой при репликации транзакций или репликации слиянием.
Хотя TRUNCATE нельзя использовать против таблиц, участвующих в репликации транзакций или репликации слиянием.

• Откат : оператор DELETE можно откатить.
TRUNCATE также можно откатить, если он заключен в блок TRANSACTION и сеанс не закрыт. После закрытия сеанса вы не сможете откатить TRUNCATE.

• Ограничения : оператор DELETE может завершиться ошибкой, если он нарушает триггер или пытается удалить строку, на которую ссылаются данные в другой таблице с ограничением FOREIGN KEY. Если DELETE удаляет несколько строк, и любая из удаленных строк нарушает триггер или ограничение, оператор отменяется, возвращается ошибка и никакие строки не удаляются.
И если DELETE используется для просмотра, это представление должно быть обновляемым.
TRUNCATE нельзя использовать для таблицы, используемой в индексированном представлении.
TRUNCATE нельзя использовать для таблицы, на которую ссылается ограничение FOREIGN KEY, за исключением таблицы, имеющей внешний ключ, ссылающийся на себя.

SQL: оператор TRUNCATE TABLE

В этом руководстве по SQL объясняется, как использовать оператор SQL TRUNCATE TABLE с синтаксисом и примерами.

Описание

Оператор SQL TRUNCATE TABLE используется для удаления всех записей из таблицы. Он выполняет ту же функцию, что и оператор DELETE, без предложения WHERE.

Предупреждение : При усечении таблицы оператор TRUNCATE TABLE не может быть отменен в некоторых базах данных.

Синтаксис

Синтаксис оператора TRUNCATE TABLE в SQL:

 TRUNCATE TABLE имя_таблицы; 

Параметры или аргументы

имя_таблицы

Таблица, которую вы хотите усечь.

DDL / DML для примеров

Если вы хотите следовать этому руководству, получите DDL для создания таблиц и DML для заполнения данных. Тогда попробуйте примеры в своей базе данных!

Получить DDL / DML

Пример

Вы можете усечь таблицу вместо того, чтобы отбрасывать ее и создавать заново.Усечение таблицы может быть быстрее и не влияет на индексы, триггеры и зависимости таблицы. Это также быстрый способ удалить записи из таблицы, если вам не нужно беспокоиться об откате.

Рассмотрим пример использования оператора TRUNCATE TABLE в SQL.

В этом примере у нас есть таблица с именем поставщиков со следующими данными:

Введите следующий оператор TRUNCATE TABLE:

Попытайся

 поставщики TRUNCATE TABLE; 

Затем снова выберите данные из таблицы поставщиков :

 ВЫБРАТЬ * ИЗ ПОСТАВЩИКОВ; 

Вот результаты, которые вы должны увидеть:

В этом примере будет усечена таблица поставщиков и удалены все записи из этой таблицы.Это было бы эквивалентно следующему оператору DELETE в SQL:

Попытайся

 УДАЛИТЬ ОТ поставщиков; 

Оба эти оператора приведут к удалению всех данных из таблицы поставщиков . Основное различие между ними заключается в том, что вы можете откатить оператор DELETE, если захотите, но вы не сможете откатить оператор TRUNCATE TABLE во всех базах данных SQL.

Если вам нужно префикс имени таблицы с именем базы данных, вы можете переписать оператор TRUNCATE TABLE следующим образом:

 ОБРЕЗАТЬ ТАБЛИЦА общ.поставщики; 

В этом примере будет усечена таблица с именем поставщиков в базе данных с именем до .

операций усечения таблиц в SQL Server

При усечении таблицы удаляются все записи во всей таблице или в ее разделе. Таблица TRUNCATE
функционально аналогична таблице DELETE без предложения WHERE. Однако таблица TRUNCATE намного быстрее, чем DELETE с
относительно времени и ресурсов, которые мы рассмотрим в этой статье.Оператор TRUNCATE удаляет
данные путем освобождения страниц данных в данных таблицы. Это означает, что TRUNCATE похож на drop и
воссоздайте таблицу. Кроме того, он записывает в журнал транзакций только освобождение страниц, а не по строкам, как в
Оператор DELETE.

Настройка среды

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

Из приведенного выше кода T-SQL создается образец таблицы с именем SampleTable и достаточное количество
были заполнены образцы записей.

Чтобы обрезать таблицу, можно выполнить следующую команду T-SQL.

Важно отметить, что в операторе TRUNCATE нет предложения WHERE. Параметр TRUNCATE доступен в SQL
Сервер всех выпусков, экземпляры Azure SQL Server и хранилища данных Azure.

Сравнение ресурсов

Давайте сравним потребление ресурсов операторами TRUNCATE и DELETE.Это было сделано с той же таблицей
с 500 000 записей.

Нет ничего, что можно было бы сравнить между TRUNCATE и DELETE, как показано в приведенной выше таблице.Очевидно, что TRUNCATE
намного быстрее и использует меньше ресурсов, чем оператор DELETE во всех аспектах.

Сравнение файла журнала транзакций

Давайте сравним использование файла журнала транзакций во время операторов TRUNCATE и DELETE.

На следующем снимке экрана показан размер журнала после того, как были сделаны вставки в образец таблицы, который был создан ранее.

Из приведенного выше снимка экрана видно, что процент использования журнала транзакций равен 0.3%.

Ниже показано использование файла журнала после завершения оператора DELETE.

Как видно из приведенного выше снимка экрана, использование журнала транзакций увеличилось до 0,7% после удаления этих записей.
через оператор DELETE.

Ниже приведен снимок экрана с процентом использования журнала при удалении данных из оператора TRUNCATE.

Из приведенного выше снимка экрана вы увидите, что использование журнала вообще не увеличилось.Это означает, что во время TRUNCATE
будет минимально записываться в журнал транзакций, чем оператор DELETE.

Поскольку оператор TRUNCATE минимально регистрируется в журнале транзакций, TRUNCATE работает быстрее, чем оператор DELETE. Это
из-за того, что TRUNCATE эквивалентен удалению таблицы и ее воссозданию. Поскольку TRUNCATE не потребляет
большие ресурсы журнала транзакций, файл резервной копии журнала транзакций будет меньше по размеру и улучшит производительность
параметров восстановления, таких как зеркалирование и доставка журналов.

Свойство IDENTITY во время TRUNCATE TABLE

Свойство IDENTITY используется в таблице, когда вам нужно автоматически увеличить число для столбца. Это означает, что когда
вставлена ​​первая запись. Столбец IDENTITY станет 1, следующая запись будет 2 и так далее. Когда все данные в
таблица удаляется, что будет со следующим номером.

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

Ниже приведены выходные данные для вышеуказанного запроса.

Вы увидите, что, хотя количество записей равно 1, текущий максимальный номер столбца идентификаторов равен 500001. Это
означает, что оператор DELETE не повлияет на следующее значение столбца IDENTITY, а оператор DELETE НЕ будет сброшен
столбец ИДЕНТИЧНОСТЬ, даже если записей нет.

Давайте посмотрим на то же поведение с оператором TRUNCATE.

Вы можете видеть, что с помощью оператора TRUNCATE столбец IDENTITY сбрасывается.

транзакции

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

Усечения разделов

TRUNCATE не может быть выполнен с предложением WHERE, что означает, что все записи будут удалены из TRUNCATE /
утверждение.Однако разделы могут быть усечены, как показано в приведенном ниже операторе T-SQL.

Из приведенного выше утверждения, разделы 2,4,6,7,8 будут усечены, а данные других разделов не будут
усеченный.

Триггеры

Хотя оператор DELETE запускает триггер DELETE, важно отметить, что TRUNCATE не запускает
УДАЛИТЬ триггер.Следовательно, если вашей системе необходимо удалить триггер для срабатывания, оператор TRUNCATE TABLE не должен
быть выпущенным.

Безопасность

TRUNCATE TABLE требует минимального разрешения на изменение таблицы для пользователя. Это означает, что разрешение на УДАЛЕНИЕ таблицы не
достаточно для выполнения оператора TRUNCATE.

Ограничения

Оператор TRUNCATE будет ограничен в следующих сценариях.

• Когда на таблицу ссылается ограничение внешнего ключа, усечение невозможно.Однако вы можете усечь
  таблицы, где он ссылается на ту же таблицу
• Когда таблица включена в индексированное представление, TRUNCATE невозможно.
• Если таблицы включены в транзакционную репликацию или репликацию слиянием, TRUNCATE невозможно.

Динеш Асанка является MVP в категории SQL Server за последние 8 лет. Он работает с SQL Server более 15 лет, пишет статьи и является соавтором книг.Он является докладчиком в различных группах пользователей и университетах. Он всегда готов учиться и делиться своими знаниями.

Посмотреть все сообщения Динеш Асанка

Последние сообщения Динеш Асанка (посмотреть все)

SQL TRUNCATE TABLE Vs DELETE statement

В этом руководстве вы узнаете, как быстро удалить все строки из таблицы с помощью SQL.

Удаление данных таблицы

Оператор TRUNCATE TABLE удаляет все строки из таблицы быстрее, чем оператор DELETE .Логически TRUNCATE TABLE аналогичен оператору DELETE без предложения WHERE .

Оператор TRUNCATE TABLE удаляет все строки из таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т. Д. Остаются неизменными. Чтобы удалить определение таблицы в дополнение к ее данным, вы можете использовать оператор DROP TABLE .

Синтаксис

Базовый синтаксис TRUNCATE TABLE может быть задан следующим образом:

TRUNCATE TABLE table_name ;

Давайте выполним операцию усечения над таблицей базы данных.

Допустим, у нас есть таблица сотрудников в нашей базе данных со следующими записями:

+ -------- + -------------- + ------------ + -------- + --- ------ +
| emp_id | emp_name | найм_дата | зарплата | dept_id |
+ -------- + -------------- + ------------ + -------- + --- ------ +
| 1 | Итан Хант | 2001-05-01 | 5000 | 4 |
| 2 | Тони Монтана | 2002-07-15 | 6500 | 1 |
| 3 | Сара Коннор | 2005-10-18 | 8000 | 5 |
| 4 | Рик Декард | 2007-01-03 | 7200 | 3 |
| 5 | Мартин Бланк | 2008-06-24 | 5600 | NULL |
+ -------- + -------------- + ------------ + -------- + --- ------ +
 

Следующая команда удаляет все строки из таблицы сотрудников :

  TRUNCATE TABLE сотрудников;  

Теперь, после выполнения вышеуказанного оператора SQL, если вы попытаетесь выбрать записи из таблицы сотрудников , вы получите пустой набор результатов.

ОБРЕЗАТЬ ТАБЛИЦУ против УДАЛИТЬ

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

• TRUNCATE TABLE Оператор отбрасывает и повторно создает таблицу таким образом, что любое значение автоинкремента
  сбрасывается до своего начального значения, которое обычно равно 1.
• DELETE позволяет фильтровать строки для удаления на основе необязательного предложения WHERE , тогда как TRUNCATE TABLE не поддерживает предложение WHERE , он просто удаляет все строки.
• TRUNCATE TABLE работает быстрее и использует меньше системных ресурсов, чем DELETE , потому что DELETE сканирует таблицу для создания количества затронутых строк, затем удаляет строки одну за другой и записывает запись в журнал базы данных для каждой удаленная строка, а TRUNCATE TABLE просто удаляет все строки без предоставления какой-либо дополнительной информации.

Совет: Используйте TRUNCATE TABLE , если вы просто хотите удалить все строки и заново создать всю таблицу.Используйте DELETE , если вы хотите удалить ограниченное количество строк на основе определенного условия или если вы не хотите сбрасывать значение автоинкремента.

TRUNCATE TABLE vs. DELETE vs. DROP TABLE: удаление таблиц и данных в SQL

В чем разница между усечением, удалением и удалением таблицы в SQL? Узнайте в этой статье.

Существует множество способов удаления данных в SQL, включая команды DELETE , TRUNCATE TABLE и DROP TABLE . Какой из них следует использовать в той или иной ситуации?

В этой статье вы узнаете синтаксис каждой команды в различных механизмах баз данных, таких как MySQL, PostgreSQL, SQL Server и Oracle. И вы поймете споры DROP TABLE vs. DELETE vs. TRUNCATE TABLE .

Приступим!

УДАЛИТЬ

DELETE — это команда DML (язык обработки данных).Эта команда удаляет записи из таблицы. Он используется только для удаления данных из таблицы, а не для удаления таблицы из базы данных.

Вы можете удалить всех записей с синтаксисом:

УДАЛИТЬ ИЗ name_table;
 

Или вы можете удалить группу записей , используя предложение WHERE:

УДАЛИТЬ ИЗ name_table WHERE col = value;
 

Если вы хотите удалить все записи из данной таблицы, введите DELETE FROM, за которым следует имя таблицы.Обратите внимание, что в этом синтаксисе нет имен столбцов; вы удаляете все записи, а не данные в выбранных столбцах.

Если вы хотите удалить определенные записи, используйте WHERE с условиями фильтрации, как во втором примере.

Давайте воспользуемся этими командами в качестве примера. Вот таблица продукт :

Этот запрос …

УДАЛИТЬ ИЗ продукта;
 

… удаляет все данные из таблицы продукт . После этого запроса таблица продукт будет пустой.

А вот ГДЕ запрос…

УДАЛИТЬ ИЗ товара ГДЕ цена

 
… удаляет только записи о молоке и сахаре, потому что их цена ниже 2,90 долларов. (Молоко стоит 2,40 доллара, а сахар - 2,88 доллара.) 

 
 Теперь в таблице   продукт   содержит только записи с ценами выше 2,90 долл. США: 

 

Как работает УДАЛЕНИЕ?

Если вы не хотите удалять структуру таблицы или удаляете только определенные строки, используйте команду DELETE . Он может удалить одну, несколько или все строки в таблице. DELETE возвращает количество строк, удаленных из таблицы.

Однако DELETE использует блокировку строки во время выполнения и может быть отменен. Каждая удаленная строка заблокирована, поэтому, если вы работаете с большой таблицей, потребуется много блокировок.

DELETE также сохраняет идентификатор автоинкремента в таблице. Если вы удалите последнюю запись в таблице с ID = 20, а затем добавите новую запись, эта запись будет иметь ID = 21 — даже если запись непосредственно перед ней будет ID = 19.

DELETE может выполняться с помощью триггеров. Триггер может быть вызван до, после или вместо операции DELETE . Его можно выполнить при любом изменении строки или при удалении всех строк. Удаление строк в другой таблице также может вызвать DELETE .

Конечно, для использования команды DELETE вам потребуется разрешение DELETE для этой таблицы.

ТАБЛИЦА УПРАВЛЕНИЯ

TRUNCATE TABLE аналогичен DELETE , но эта операция является командой DDL (языка определения данных). Он также удаляет записи из таблицы без удаления структуры таблицы, но не использует предложение WHERE . Вот синтаксис:

TRUNCATE TABLE имя_таблицы;
 

Если вы воспользуетесь этой командой, все строки в этой таблице будут удалены.Следующий запрос …

TRUNCATE TABLE продукт;
 

… удаляет все записи, хранящиеся в таблице продукт .

Как работает TRUNCATE TABLE?

Будьте осторожны при использовании этой команды. TRUNCATE транзакций можно откатить в механизмах баз данных, таких как SQL Server и PostgreSQL, но не в MySQL и Oracle.

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

В отличие от DELETE , TRUNCATE не возвращает количество строк, удаленных из таблицы. Он также сбрасывает значение автоинкремента таблицы на начальное значение (обычно 1). Если вы добавите запись после усечения таблицы, она будет иметь ID = 1. Примечание: В PostgreSQL вы можете перезапустить или продолжить автоматическое увеличение значения. Oracle использует последовательность для увеличения значений, которая не сбрасывается с помощью TRUNCATE .

Конечно, вам нужно разрешение на использование TRUNCATE TABLE . В PostgreSQL вам нужна привилегия TRUNCATE ; в SQL Server минимальное разрешение — ALTER table; в MySQL вам понадобится привилегия DROP . Наконец, для использования этой команды Oracle требуется системная привилегия DROP ANY TABLE .

Вы можете узнать больше в курсе «Основы создания таблиц в SQL», который является частью нашего Пути проектирования данных.

ТАБЛИЦА ПАДЕНИЯ

DROP TABLE — это еще одна операция DDL (языка определения данных). Но он не используется для простого удаления данных из таблицы; он удаляет структуру таблицы из базы данных вместе со всеми данными, хранящимися в таблице.

Вот синтаксис этой команды:

DROP TABLE имя_таблицы;
 

Все, что вам нужно после DROP TABLE , — это имя таблицы, которую вы хотите удалить. Например, если вы хотите удалить всю таблицу product из базы данных, вы должны написать:

Продукт DROP TABLE;
 

Удаляются все данные в таблице , продукт и структура таблицы.

Как работает DROP TABLE?

Операция DROP TABLE удаляет определение таблицы и данные, а также индексы, ограничения и триггеры, связанные с таблицей.

Эта команда освобождает место в памяти.

При выполнении DROP TABLE триггеры не срабатывают.

Эту операцию нельзя откатить в MySQL, но можно в Oracle, SQL Server и PostgreSQL.

В SQL Server для команды DROP TABLE требуется разрешение ALTER в схеме, которой принадлежит таблица; MySQL требует привилегии DROP; Oracle требует привилегию DROP ANY TABLE .В PostgreSQL пользователи могут удалять собственные таблицы.

DROP TABLE против DELETE TABLE против TRUNCATE TABLE в SQL

В каких случаях требуется КАБЕЛЬНАЯ ТАБЛИЦА ? Когда следует использовать TRUNCATE или выбрать простой DELETE ? Мы подготовили таблицу ниже, чтобы суммировать свойства каждой команды:

Какая операция лучше всего подходит для какого варианта использования?

• Чтобы удалить определенные строки, используйте DELETE .
• Чтобы удалить все строки из большой таблицы и оставить структуру таблицы, используйте TRUNCATE TABLE . Это быстрее, чем DELETE .
• Чтобы удалить всю таблицу, включая ее структуру и данные, используйте DROP TABLE .

Вы можете узнать больше о создании структур баз данных на нашем пути разработки данных.

В SQL, DELETE , TRUNCATE TABLE и DELETE TABLE имеют свои плюсы и минусы.Надеюсь, эта статья помогла вам понять, когда и как использовать каждую команду.

mysql — путают со стандартным определением оператора Truncate

У меня достаточно опыта работы с сервером MSSQL. У нас есть миграция базы данных с MYSQL на сервер SQL. Я никогда не работал с MYSQL, поэтому я перебирал различия, используя эту ссылку.

В одном месте на странице ниже приведено определение Truncate.

Стандарт SQL определяет оператор TRUNCATE TABLE tablename (дополнительный идентификатор функции F200, новый в SQL: 2008) как:
Удалить все строки базовой таблицы, не вызывая каких-либо триггерных действий.

К сожалению, стандарт не указывает
1. следует ли разрешить TRUNCATE TABLE в транзакции, включающей другие операторы, или нет
2. следует ли TRUNCATE TABLE подразумевать немедленную COMMIT или нет

Для SQL-сервера на странице указано

Соответствует стандарту.

В MSSQL TRUNCATE TABLE разрешен в транзакции, включающей другие операции, а TRUNCATE TABLE не подразумевает немедленную операцию COMMIT.

Для MYSQL на странице указано

MySQL имеет оператор TRUNCATE TABLE, но он не всегда соответствует стандарту.

Обратите внимание, что в некоторых случаях команда MySQL truncate на самом деле является эквивалентом неограниченной команды DELETE (т. Е. Потенциально медленной и вызывающей триггер). Его поведение зависит от того, каким механизмом хранения управляет таблица.

При использовании таблиц InnoDB (безопасных для транзакций) TRUNCATE TABLE разрешается в транзакции, включающей другие операции, однако TRUNCATE TABLE подразумевает немедленную операцию COMMIT.

В SQLServer при усечении таблицы
1. Операция может регистрироваться минимально
2. Происходит только освобождение страниц, которые регистрируются в журнале транзакций

Итак, мой вопрос:
1. Что делает нижеприведенная формулировка стандартными средствами и как SQL-сервер соответствует стандарту, а MYSQL — нет.

, следует ли TRUNCATE TABLE подразумевать немедленную COMMIT или нет

PostgreSQL TRUNCATE TABLE с практическими примерами

Резюме : в этом руководстве вы узнаете, как использовать оператор PostgreSQL TRUNCATE TABLE для быстрого удаления всех данных из больших таблиц.

Введение в оператор PostgreSQL TRUNCATE TABLE

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

 
  TRUNCATE TABLE имя_таблицы;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Оператор TRUNCATE TABLE удаляет все данные из таблицы, не просматривая ее.По этой причине он быстрее, чем оператор DELETE .

Кроме того, оператор TRUNCATE TABLE сразу же освобождает хранилище, поэтому вам не нужно выполнять последующую операцию VACUMM , что полезно в случае больших таблиц.

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

Самая простая форма оператора TRUNCATE TABLE выглядит следующим образом:

 
  TRUNCATE TABLE имя_таблицы;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

В следующем примере оператор TRUNCATE TABLE используется для удаления всех данных из таблицы invoices :

 
  TRUNCATE TABLE invoices;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Помимо удаления данных, вы можете сбросить значения в столбце идентификаторов с помощью параметра RESTART IDENTITY , например:

 
  TRUNCATE TABLE table_name
ПЕРЕЗАГРУЗИТЬ ИДЕНТИЧНОСТЬ;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Например, следующий оператор удаляет все строки из таблицы invoice и сбрасывает последовательность, связанную со столбцом invoice_no :

 
  TRUNCATE ТАБЛИЦА счетов-фактур
ПЕРЕЗАГРУЗИТЬ ИДЕНТИЧНОСТЬ;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

По умолчанию в операторе TRUNCATE TABLE используется параметр CONTINUE IDENTITY .Этот параметр в основном не перезапускает значение в последовательности, связанной со столбцом в таблице.

Удалить все данные из нескольких таблиц

Чтобы удалить все данные из нескольких таблиц сразу, вы разделяете каждую таблицу запятой (,) следующим образом:

 
  TRUNCATE TABLE
    table_name1,
    table_name2,
    ...;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Например, следующий оператор удаляет все данные из счетов-фактур и клиентов таблиц:

 
  TRUNCATE TABLE invoices, customers;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Удалить все данные из таблицы, имеющей ссылки на внешние ключи

На практике таблица, которую вы хотите усечь, часто имеет ссылки на внешние ключи из других таблиц, которые не указаны в инструкции TRUNCATE TABLE .

По умолчанию оператор TRUNCATE TABLE не удаляет какие-либо данные из таблицы, имеющей ссылки на внешние ключи.

Чтобы удалить данные из таблицы и других таблиц, внешний ключ которых ссылается на таблицу, используйте параметр CASCADE в операторе TRUNCATE TABLE следующим образом:

 
  TRUNCATE TABLE имя_таблицы
КАСКАД;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

В следующем примере удаляются данные из таблицы invoices и других таблиц, которые ссылаются на таблицу invoices через ограничения внешнего ключа:

 
  TRUNCATE TABLE счета-фактуры КАСКАД;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Параметр CASCADE следует использовать с дальнейшим рассмотрением, иначе вы можете удалить данные из таблиц, которые вам не нужны.

По умолчанию оператор TRUNCATE TABLE использует параметр RESTRICT , который не позволяет вам усекать таблицу, имеющую ссылки на ограничение внешнего ключа.

PostgreSQL TRUNCATE TABLE и триггер ON DELETE

Несмотря на то, что оператор TRUNCATE TABLE удаляет все данные из таблицы, он не запускает триггеры ON DELETE , связанные с таблицей.

Чтобы запустить триггер при применении команды TRUNCATE TABLE к таблице, необходимо определить триггеры BEFORE TRUNCATE и / или AFTER TRUNCATE для этой таблицы.

PostgreSQL TRUNCATE TABLE и транзакция

TRUNCATE TABLE безопасна для транзакций. Это означает, что если вы поместите его в транзакцию, вы можете безопасно откатить его.

Сводка

• Используйте оператор TRUNCATE TABLE , чтобы удалить все данные из большой таблицы.
• Используйте параметр CASCADE для усечения таблицы и других таблиц, которые ссылаются на таблицу через ограничение внешнего ключа.
• TRUNCATE TABLE не запускает триггер ON DELETE .Вместо этого он запускает триггеры BEFORE TRUNCATE и AFTER TRUNCATE .
• Оператор TRUNCATE TABLE безопасен для транзакций.

Было ли это руководство полезным?

ТАБЛИЦА УСКОРЕНИЯ

ТАБЛИЦА УСКОРЕНИЯ

Оператор TRUNCATE TABLE аналогичен оператору DELETE, который удаляет все строки. Однако в большинстве случаев это быстрее, чем DELETE, поскольку DELETE удаляет каждую строку отдельно.

Требуемые привилегии

Владелец таблицы не требует никаких привилегий.

DELETE для таблицы другого пользователя.

Синтаксис SQL

 TRUNCATE TABLE [Владелец] TableName
 

Параметры

ТАБЛИЦА TRUNCATE имеет параметр:

Описание

• TRUNCATE является оператором DDL и, следовательно, управляется атрибутом DDLCommitBehavior .Если DDLCommitBehavior = 0 (по умолчанию), то фиксация выполняется до и после выполнения оператора TRUNCATE. Если DDLCommitBehavior = 1, то TRUNCATE является частью транзакции, и применяются следующие правила транзакции:

• Допускаются одновременные зафиксированные операции чтения чтения, и семантика чтения такая же, как и для подтвержденных операций чтения при наличии операторов DELETE

• TRUNCATE разрешено даже при наличии дочерних таблиц.Однако для продолжения TRUNCATE дочерние таблицы должны быть пустыми. Если в какой-либо из дочерних таблиц есть какие-либо строки, TimesTen возвращает ошибку, указывающую, что дочерняя таблица не пуста.

• TRUNCATE не поддерживается с подробными таблицами материализованного представления и таблицей, которая является частью группы кэша или временной таблицы.

• Когда таблица содержит внешние данные переменной длины, производительность TRUNCATE TABLE аналогична производительности оператора DELETE, который удаляет все строки в таблице.Дополнительные сведения о данных вне линии см. В разделе «Числовые типы данных».

• Если таблицы реплицируются, оператор TRUNCATE реплицируется на подписчика, даже если никакие строки не обрабатываются.

• Когда таблицы реплицируются с включенной проверкой конфликтов меток времени, о конфликтах не сообщается.

• Операции

  DROP TABLE и ALTER TABLE не могут использоваться для изменения хэш-страниц в незафиксированных усеченных таблицах.

Примеры

Для удаления всех строк из воссоздания .булавы стол, использование:

 TRUNCATE TABLE отдых.
Добавить комментарий Отменить ответ
Ваш адрес email не будет опубликован. Обязательные поля помечены *
Комментарий *
Имя * 
Email * 
Сайт