truncate table что делает
Truncate table что делает
TRUNCATE — опустошить таблицу или набор таблиц
Синтаксис
Описание
Команда TRUNCATE быстро удаляет все строки из набора таблиц. Она действует так же, как безусловная команда DELETE для каждой таблицы, но гораздо быстрее, так как она фактически не сканирует таблицы. Более того, она немедленно высвобождает дисковое пространство, так что выполнять операцию VACUUM после неё не требуется. Наиболее полезна она для больших таблиц.
Параметры
Автоматически перезапускать последовательности, связанные со столбцами опустошаемой таблицы. CONTINUE IDENTITY
Не изменять значения последовательностей. Это поведение по умолчанию. CASCADE
Отказать в опустошении любых таблиц, на которые по внешнему ключу ссылаются другие таблицы, не перечисленные в этой команде. Это поведение по умолчанию.
Замечания
Чтобы опустошить таблицу, необходимо иметь право TRUNCATE для этой таблицы.
TRUNCATE нельзя использовать с таблицей, на которую по внешнему ключу ссылаются другие таблицы, если только и эти таблицы не опустошаются этой же командой. Проверка допустимости очистки в таких случаях потребовала бы сканирования таблицы, а главная идея данной команды в том, чтобы не делать этого. Для автоматической обработки всех зависимых таблиц можно использовать указание CASCADE — но будьте очень осторожны с ним, иначе вы можете потерять данные, которые не собирались удалять!
Команда TRUNCATE небезопасна с точки зрения MVCC. После опустошения таблицы она будет выглядеть пустой для параллельных транзакций, если они работают со снимком, полученным до опустошения. За подробностями обратитесь к Разделу 13.5.
TRUNCATE является надёжной транзакционной операцией в отношении данных в таблицах: опустошение будет безопасно отменено, если окружающая транзакция не будет зафиксирована.
TRUNCATE в настоящее время не поддерживается для сторонних таблиц. Из этого следует, что если у целевой таблицы есть дочерние таблицы, являющиеся сторонними, команда не будет выполнена.
Примеры
Опустошение таблиц bigtable и fattable :
Та же операция и сброс всех связанных генераторов последовательностей:
Опустошение таблицы othertable и каскадная обработка всех таблиц, ссылающихся на othertable по ограничениям внешнего ключа:
Совместимость
Отличия TRUNCATE от DELETE в языке T-SQL
Приветствую всех на сайте 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?
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, т.е. на изменение таблицы |
Таким образом, проанализировав таблицу, Вы можете решить, в каком случае для Вас будет лучше использовать TRUNCATE TABLE, а в каком DELETE.
Самым очевидным здесь является то, что если Вам нужно удалить не все данные из таблицы, а только часть, т.е. по условию WHERE, то Вы можете использовать только DELETE.
А, например, если Вам приходится периодически очищать всю таблицу, и значения идентификаторов не важны, то вполне возможно предпочтительней будет использовать TRUNCATE TABLE, так как это будет быстрей, и ресурсов затрачиваться будет меньше.
Пример отличия в работе TRUNCATE TABLE от DELETE
Для примера давайте создадим таблицу со столбцом идентификаторов, затем добавим в нее данные и удалим их сначала инструкцией DELETE, а потом попробуем выполнить то же самое только инструкцией TRUNCATE TABLE (предварительно пересоздав таблицу).
Создание тестовой таблицы
Выполняем удаление с помощью DELETE
Выполняем удаление инструкцией TRUNCATE TABLE
Как видите, в первом случае счетчик не сбросился, и нумерация продолжилась, а во втором счетчик начался с самого начала.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
Инструкция TRUNCATE TABLE
Содержание
Стандартный синтаксис de facto
Официально инструкция TRUNCATE TABLE не является стандартной командой ANSI. Однако есть стандарт, который поддерживают платформы и который имеет следующий формат.
TRUNCATE TABLE имя_таблицы
Ключевые слова
имя_таблицы
Имя любой таблицы в текущей базе данных или схеме.
Общие правила
Инструкция TRUNCATE TABLE оказывает то же действие, что и инструкция DELETE без предложения WHERE. Обе инструкции удаляют все строки в данной таблице. Однако есть два важных отличия. Инструкция TRUNCATE TABLE работает быстрее и не журналируется, что означает невозможность отката в случае ошибки. Кроме того, инструкция TRUNCATE TABLE не активирует триггеры, в то время как DELETE активирует их.
Эту команду следует подавать вручную. Мы настоятельно не рекомендуем использовать ее в скриптах или работающих системах, которые содержат невосстановимые данные. Ее нельзя использовать совместно с инструкциями, управляющими транзакциями, такими, как BEGIN TRAN и COMMIT.
Советы и хитрости программирования
Поскольку инструкция TRUNCATE TABLE не заносится в журнал, обычно она используется в базах данных, находящихся в процессе разработки. В рабочих базах данных ее нужно применять осторожно!
Мы настоятельно не рекомендуем вставлять инструкцию TRUNCATE
Команда TRUNCATE TABLE не будет выполнена, если другой пользователь заблокировал таблицу в тот момент, когда команда была подана. Инструкция TRUNCATE TABLE не активизирует триггеры, но будет работать и при их наличии. Однако инструкция не сработает, если в данной таблице есть ограничения FOREIGN KEY.
Версия 8 DB2 не поддерживает инструкцию TRUNCATE TABLE.
MySQL
Платформа MySQL, начиная с версии 3.23, поддерживает базовый формат инструкции TRUNCATE TABLE.
TRUNCATE TABLE имя
Платформа MySQL получает нужный результат путем удаления и повторного создания указанной таблицы. Поскольку MySQL хранит каждую таблицу в файле с именем имя./гт, то для правильной работы команды файл с этим именем должен существовать в директории, содержащей файлы базы данных.
Например, следующая инструкция удалит все данные из таблицы publishers.
TRUNCATE TABLE publishers
Платформа Oracle позволяет удалить таблицу или индексированный кластер (но не хеш-кластер). Синтаксис следующий.
Инструкция TRUNCATE TABLE впервые появилась на платформе Oracle, и другие производители вскоре ввели поддержку этой инструкции. В Oracle к этой инструкции были добавлены дополнительные возможности, которые часто поддерживаются другими производителями.
Синтаксические элементы в Oracle следующие
CLUSTER | TABLE
PRESERVE MA TERIALIZED VIEWLOG
При удалении основной таблицы сохраняются все журналы-снимки.
PURGE MATERIALIZED VIEW LOG Удаляются все журналы-снимки.
Дисковое пространство, освободившееся при удалении рядов, высвобождается. REUSE STORAGE
Дисковое пространство, освободившееся при удалении рядов, остается закрепленным за данной таблицей. Например:
TRUNCATE TABLE scon. authors
PRESERVE SNAPSHOT LOG REUSE STORAGE
В этом примере удаляются все записи в таблице scott.authors. Сохраняется существующий журнал-снимок, и таблица может повторно использовать закрепленное за ней освободившееся место.
PostgreSQL
Платформа PostgreSQL мало отличается от сложившегося промышленного стандарта. Было добавлено ключевое слово TABLE.
TRUNCATE [TABLET имя
Ключевое слово TABLE является необязательным. Например, следующая команда удаляет все записи из таблицы authors базы данных PostgreSQL.
TRUNCATE autners
SQL Server
Платформа SQL Server поддерживает сложившийся de facto промышленный стандарт. Смотрите также
DELETE
Дополнительная информация по теме
Синтаксис при использовании инструкции CREATE/ALTER TABLE, расширения и дополнения
Что такое иснстуркция CREATE ROLE, и особенности синтаксиса при ее использовании и ключевые слова
Правила и методы использования инструкции UPDATE в базах данных на различных платформах
Особенности использования инструкции CREATE SCHEMA, основной синтаксис при использовании
Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite
Базы данных
TRUNCATE TABLE
В этом учебном материале вы узнаете, как использовать оператор Oracle TRUNCATE TABLE с синтаксисом, примерами и практическими упражнениями.
Описание
Оператор TRUNCATE TABLE используется для удаления всех записей из таблицы в Oracle. Он выполняет ту же функцию что и DELETE, только без условий WHERE.
Предупреждение: Если вы очистите таблицу с помощью оператора TRUNCATE TABLE, то ее откат невозможен.
Синтаксис
Синтаксис для оператора TRUNCATE TABLE в Oracle/PLSQL:
Параметры или аргументы
Необязательный. Если указано, то это имя схемы к которой принадлежит таблица.
Таблица, которую вы хотите очистить.
PRESERVE MATERIALIZED VIEW LOG
Необязательный. Если указано, то materialized view log будет сохранено, когда таблица очищается. Это поведение по умолчанию.
PURGE MATERIALIZED VIEW LOG
Необязательный. Если указано, то materialized view log будет очищен, когда таблица очищается.
Необязательный. Если указано, всё хранилище очищающихся строк останется распределенным в таблице.
Пример
В Oracle очищение таблицы является быстрым способом, чтобы очистить записи из таблицы, если вам не нужно беспокоиться об откате. Одной из причин является то, что, когда таблица очищается, это не влияет на какие-либо из индексов, триггеров или зависимостей таблицы. Очищение таблицы также намного проще, чем удаление таблицы и ее воссоздания.
Рассмотрим пример того, как использовать оператор TABLE TRUNCATE в Oracle/PLSQL.
Инструкция TRUNCATE TABLE в Transact-SQL
В 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 в некоторых случаях, как Вы понимаете, могут быть и недостатками.
Инструкция TRUNCATE TABLE удаляет все строки (данные) таблицы, но структура таблицы: столбцы, ограничения, индексы и так далее сохраняются. Для того чтобы полностью удалить таблицу и ее определение, следует использовать инструкцию DROP TABLE.
Ограничения TRUNCATE TABLE
Пример использования инструкции TRUNCATE TABLE
Для примера давайте создадим таблицу со столбцом идентификаторов, затем добавим в нее данные и удалим их инструкцией TRUNCATE TABLE, затем снова добавим данные и проверим, сбросился ли счетчик столбца идентификаторов на начальное значение.
Примечание! В качестве SQL сервера у меня выступает версия Microsoft SQL Server 2016 Express.
Как видим, счетчик сброшен, инструкция TRUNCATE TABLE отработала.
Заметка! Для изучения всех возможностей SQL и языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL для новичков.