Truncate sql что это
Инструкция 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 для новичков.
Truncate sql что это
TRUNCATE — опустошить таблицу или набор таблиц
Синтаксис
Описание
Команда TRUNCATE быстро удаляет все строки из набора таблиц. Она действует так же, как безусловная команда DELETE для каждой таблицы, но гораздо быстрее, так как она фактически не сканирует таблицы. Более того, она немедленно высвобождает дисковое пространство, так что выполнять операцию VACUUM после неё не требуется. Наиболее полезна она для больших таблиц.
Параметры
Автоматически перезапускать последовательности, связанные со столбцами опустошаемой таблицы. CONTINUE IDENTITY
Не изменять значения последовательностей. Это поведение по умолчанию. CASCADE
Отказать в опустошении любых таблиц, на которые по внешнему ключу ссылаются другие таблицы, не перечисленные в этой команде. Это поведение по умолчанию.
Замечания
Чтобы опустошить таблицу, необходимо иметь право TRUNCATE для этой таблицы.
TRUNCATE нельзя использовать с таблицей, на которую по внешнему ключу ссылаются другие таблицы, если только и эти таблицы не опустошаются этой же командой. Проверка допустимости очистки в таких случаях потребовала бы сканирования таблицы, а главная идея данной команды в том, чтобы не делать этого. Для автоматической обработки всех зависимых таблиц можно использовать указание CASCADE — но будьте очень осторожны с ним, иначе вы можете потерять данные, которые не собирались удалять!
Команда TRUNCATE небезопасна с точки зрения MVCC. После опустошения таблицы она будет выглядеть пустой для параллельных транзакций, если они работают со снимком, полученным до опустошения. За подробностями обратитесь к Разделу 13.5.
TRUNCATE является надёжной транзакционной операцией в отношении данных в таблицах: опустошение будет безопасно отменено, если окружающая транзакция не будет зафиксирована.
TRUNCATE в настоящее время не поддерживается для сторонних таблиц. Из этого следует, что если у целевой таблицы есть дочерние таблицы, являющиеся сторонними, команда не будет выполнена.
Примеры
Опустошение таблиц bigtable и fattable :
Та же операция и сброс всех связанных генераторов последовательностей:
Опустошение таблицы othertable и каскадная обработка всех таблиц, ссылающихся на othertable по ограничениям внешнего ключа:
Совместимость
Truncate sql что это
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 (Transact-SQL)
Удаляет все строки в таблице или указанные секции таблицы, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
database_name
Имя базы данных.
schema_name
Имя схемы, которой принадлежит таблица.
table_name
Имя таблицы, которая должна быть усечена, или таблицы, из которой удаляются все строки. table_name должно быть литералом. table_name не может быть функцией OBJECT_ID() или переменной.
Указывает секции для усечения или секции, из которых удаляются все строки. Если таблица не секционирована, аргумент WITH PARTITIONS приведет к возникновению ошибки. Если предложение WITH PARTITIONS не указано, будет усечена вся таблица.
можно указать одним из следующих способов.
Указав номер секции, например WITH (PARTITIONS (2))
Указав номера нескольких секций, разделив их запятыми, например WITH (PARTITIONS (1, 5))
Указав диапазоны секций и отдельные секции, например WITH (PARTITIONS (2, 4, 6 TO 8))
Для усечения секционированной таблицы таблицы и индексы должны быть выровнены (секционированы одной функцией секционирования).
Remarks
Инструкция TRUNCATE TABLE обладает следующими преимуществами по сравнению с инструкцией DELETE.
Используется меньший объем журнала транзакций.
Инструкция DELETE производит удаление по одной строке и заносит в журнал транзакций запись для каждой удаляемой строки. Инструкция TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблиц, и в журнал транзакций записывает только данные об освобождении страниц.
Обычно используется меньшее количество блокировок.
Если инструкция DELETE выполняется с блокировкой строк, для удаления блокируется каждая строка таблицы. Инструкция TRUNCATE TABLE всегда блокирует таблицу (включая блокировку схемы (SCH-M)) и страницу, но не каждую строку.
В таблице остается нулевое количество страниц, без исключений.
После выполнения инструкции DELETE в таблице могут все еще оставаться пустые страницы. Например, чтобы освободить пустые страницы в куче, необходима, как минимум, монопольная блокировка таблицы (LCK_M_X). Если операция удаления не использует блокировку таблицы, таблица (куча) будет содержать множество пустых страниц. В индексах после операции удаления могут оказаться пустые страницы, хотя эти страницы будут быстро освобождены процессом фоновой очистки.
Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.
Для операции TRUNCATE TABLE можно выполнить откат.
Ограничения
Нельзя использовать TRUNCATE TABLE в таблицах в следующих случаях.
На таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.
Таблица является частью индексированного представления.
Таблица опубликована с использованием репликации транзакций или репликации слиянием.
Это темпоральная таблица с управлением версиями.
На таблицу ссылается ограничение EDGE.
Для таблиц с какими-либо из этих характеристик следует использовать инструкцию DELETE.
Инструкция TRUNCATE TABLE не может активировать триггер, поскольку она не записывает в журнал удаление отдельных строк. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).
В Azure Synapse Analytics и Система платформы аналитики (PDW):
Инструкцию TRUNCATE TABLE нельзя использовать в инструкции EXPLAIN.
Инструкцию TRUNCATE TABLE невозможно выполнить внутри транзакции.
Усечение больших таблиц
В Microsoft SQL Server существует возможность удалять или усекать таблицы, которые имеют больше 128 экстентов, не удерживая одновременные блокировки для всех экстентов, предназначенных для удаления.
Разрешения
Примеры
A. Усечение таблицы
Б. Усечение секций таблицы
Применимо к: SQL Server (с SQL Server 2016 (13.x); до текущей версии)
В следующем примере выполняется усечение указанных секций секционированной таблицы. Синтаксис WITH (PARTITIONS (2, 4, 6 TO 8)) задает усечение секций с номерами 2, 4, 6, 7 и 8.