Sql truncate table: SQL DROP TABLE, TRUNCATE TABLE

sql — Что быстрее DROP TABLE или TRUNCATE TABLE

Допустим, при извлечении процедуры создается временная таблица #, заполняется какими-то данными.

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

Структура таблицы не меняется.

Будет ли профит если из временной таблицы создать нормальную таблицу и вместо DROP делать TRUNCATE?

• sql
• sql-server

4

По идее truncate должен быть несколько быстрее, чем drop (и тем более, чем пара drop + create), т.к. truncate лишь изменяет сведения о пространстве, выделенном для хранения данных таблицы, переводя его из состояния занятого в свободное. Тогда как drop делает то же самое, но вдобавок ещё и удаляет сведения об объекте (записи в системных таблицах о том, что существует такая-то таблица с таким-то набором столбцов).

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

Вдобавок, я бы поостерегся того, чтобы

из временной таблицы создать нормальную таблицу

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

TRUNCATE очищает таблицу не добавляя записи в журнал транзакций. Это быстрей чем DROP-CREATE. Но как было замечено, важно что будут возникать проблемы при совместном использовании таблицы несколькими процессами.

1

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки

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.

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

supplier_id	имя_поставщика	город	состояние
100	Майкрософт	Редмонд	Вашингтон
200	Гугл	Маунтин-Вью	Калифорния
300	Оракул	Редвуд Сити	Калифорния
400	Кимберли-Кларк	Ирвинг	Техас
500	Тайсон Фудс	Спрингдейл	Арканзас
600	СК Джонсон	Расин	Висконсин
700	Доул Фуд Компани	Вестлейк Виллидж	Калифорния
800	Цветочная еда	Томасвилл	Грузия
900	Электронное искусство	Редвуд Сити	Калифорния

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

Попробуйте

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

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

 ВЫБЕРИТЕ * ОТ поставщиков; 

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

supplier_id	имя_поставщика	город	состояние

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

Попробуйте

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

Оба этих утверждения приведут к удалению всех данных из таблицы

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

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

 TRUNCATE TABLE totn.suppliers; 

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

Поделись:

Рекламные объявления

Разница между операторами SQL Truncate и SQL Delete в SQL Server

Мы получаем требование удалить данные из реляционной таблицы SQL. Мы можем использовать оператор SQL Delete и SQL Truncate для удаления данных. Понимание различий между этими командами помогает разработчикам SQL правильно обрабатывать свои данные. Кроме того, это очень распространенный вопрос, который задают на собеседованиях новички в SQL.

Расскажите мне о разнице между командой удаления и усечения.

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

Команда удаления SQL

Мы используем команду SQL Delete в SQL Server для удаления записей из таблицы. Мы можем удалить все записи или использовать предложение Where для удаления записей, соответствующих критериям.

Пример 1: Удаление всех записей из таблицы

Предположим, мы хотим удалить все записи из таблицы Employee, выполним следующий запрос.

УДАЛИТЬ ИЗ [SQLShackDemo].[dbo].[Сотрудник];

Пример 2.

Удаление определенных записей из таблицы

Предположим, мы хотим удалить все записи о сотрудниках, принадлежащих определенному городу Сан-Антонио . Мы можем указать это условие в предложении Where с оператором SQL Delete, как показано ниже.

УДАЛИТЬ ИЗ [SQLShackDemo].[dbo].[Employee] , где City=’San Antonio’ ​​

Нам нужно использовать строковое значение в одинарных кавычках. Мы можем напрямую указать значение без одинарных кавычек в предложении Where, например EmpID, в следующем запросе.

УДАЛИТЬ [SQLShackDemo].[dbo].[Сотрудник] ГДЕ EmpID = 1001;

В фактическом плане выполнения предложения Delete мы видим, что он использует оператор Clustered Index Delete для удаления определенной строки.

Пример 3. Оператор SQL Delete и значения идентификаторов

Оператор Delete удаляет записи одну за другой и записывает каждую запись в журнал транзакций. Это оператор DML.

Предположим, мы удаляем строку из таблицы с помощью оператора DELETE, и эта таблица содержит значения SQL IDENTITY. Значения IDENTITY генерируются при каждой вставке новой записи. У нас может возникнуть вопрос-

Что происходит со значением идентификатора после удаления записи?

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

Столбец EmpID — это столбец идентификаторов. Мы можем проверить столбец идентификаторов в таблице с помощью команды sp_help.

sp_help ‘[Сотрудник]’

Нам нужно удалить EmpID 1014 из таблицы Employee.

УДАЛИТЬ [SQLShackDemo].[dbo].[Сотрудник] ГДЕ EmpID = 1014;

Как только мы удалим запись, просмотрим запись из таблицы. На следующем снимке экрана мы видим, что значение идентификатора 1014 недоступно, поскольку мы удалили эту запись из таблицы.

Оператор SQL Delete не сбрасывает значения идентификаторов в таблице. Мы можем использовать DBCC CHECKIDENT для проверки текущего значения идентификатора и вручную установить новое значение идентификатора для столбца идентификатора.

Например, в следующем запросе мы хотим сбросить значения идентификатора до 500.

DBCC CHECKIDENT(‘Сотрудник’, RESEED,500)

Если мы вставляем новую запись, она использует значение идентификатора 501.

вставить в значения сотрудников(‘bb’,’bb’,’b’,’bb’,1,555)

Пример 4. Откат транзакции оператора удаления SQL

Мы можем откатить транзакцию, используя оператор удаления. Давайте воспользуемся оператором удаления с BEGIN Transaction.

НАЧАТЬ ТРАНЗАКЦИЮ; УДАЛИТЬ ИЗ [SQLShackDemo]. [dbo].[Сотрудник] ГДЕ EmpID = 1010;

Мы удалили запись с EmpId 1010 в таблице сотрудников.

Мы можем использовать для отката транзакции. Выполните эту команду отката и просмотрите записи в таблице. Мы видим, что удаленная запись получает откат, и мы видим это в таблице «Сотрудник».

Откат транзакции

Команда усечения SQL

SQL Truncate — это команда языка определения данных (DDL). Он удаляет все строки в таблице. SQL Server хранит данные таблицы на страницах. Команда truncate удаляет строки, освобождая страницы. Он делает запись об освобождении страниц в журнале транзакций. Он не регистрирует каждое удаление строки в журнале транзакций.

Мы не можем указать какое-либо условие в команде SQL Truncate . На высоком уровне вы можете считать команду truncate похожей на команду Delete без предложения Where. Он блокирует таблицу и страницы вместо строки.

SQL Server не показывает фактический план выполнения команды SQL Truncate. На следующем снимке экрана вы можете увидеть предполагаемый план выполнения.

Пример 5. Удаление всех строк таблицы Employee с помощью оператора truncate

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

TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

Пример 6. Команда SQL Truncate и значения идентификаторов

В предыдущем примере 3 мы рассмотрели команду удаления со значениями идентификатора. Удалить не сбрасывает идентификационные значения. Давайте посмотрим, как команда truncate ведет себя со значениями идентификатора.

Сначала выполните команду из примера 5, чтобы удалить все строки таблицы.

TRUNCATE TABLE [SQLShackDemo]. [dbo].[Employee];

Сейчас стол пуст. Вставим новую запись в таблицу.

ВСТАВИТЬ в значения сотрудников(‘bb’,’bb’,’b’,’bb’,1,555)

Вы можете видеть, что значение Identity снова начинается с 1, как указано в свойствах таблицы.

Пример 7: команда SQL Truncate с откатом

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

Давайте рассмотрим это снова на примере.

Запустите транзакцию, чтобы усечь таблицу сотрудников.

НАЧАТЬ ТРАНС; TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

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

Теперь введите команду Rollback Tran и проверьте записи в таблице. Возвращаем наши данные обратно в таблицу.

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

Давайте рассмотрим разницу между командами SQL Delete и SQL Truncate в следующей таблице.

Удалить против усечения

SQL Удалить	Усечение SQL
Команда Delete полезна для удаления всех или определенных строк из таблицы, указанной с помощью предложения Where.	Команда truncate удаляет все строки таблицы. Мы не можем использовать предложение Where в этом.
Это DML-команда	Это команда DDL.
Команда SQL Delete блокирует каждую строку, которую требуется удалить из таблицы.	Команда SQL Truncate устанавливает блокировку таблицы и страницы для удаления всех записей.
Удалить запись журнала команд для каждой удаленной строки в журнале транзакций.	Команда truncate не регистрирует записи для каждой удаленной строки в журнале транзакций.
Команда «Удалить» работает медленнее, чем команда «Усечение».	Это быстрее, чем команда удаления.
Он удаляет строки по одной.	Он удаляет все строки в таблице, освобождая страницы, которые используются для хранения данных таблицы.
Он сохраняет идентификатор и не сбрасывает его до начального значения.	Команда Truncate сбрасывает идентификатор до его начального значения.
Для этого требуется больше места в журнале транзакций, чем для команды truncate.	Для этого требуется меньше места в журнале транзакций, чем для команды truncate.
Вам требуется разрешение на удаление таблицы, чтобы использовать это	Вам требуются разрешения на изменение таблицы, чтобы обрезать таблицу.
Вы можете использовать оператор Delete с индексированными представлениями.	Вы не можете использовать команду truncate с индексированными представлениями.
Команда Удалить сохраняет статистику объекта и выделенное пространство.	Truncate освобождает все страницы данных таблицы. Поэтому он также удаляет всю статистику и выделенное пространство.
Команда удаления также может активировать триггер. Удалить работает с отдельными строками и удалить данные. Следовательно, он активирует триггер.	Команда truncate не может активировать триггер. Триггер активируется, если происходит какое-либо изменение строки. В этой команде SQL Server освобождает все страницы, поэтому не активирует триггер.
Команда Delete удаляет строки, соответствующие предложению where. Он также не удаляет столбцы, индексы, ограничения, схемы	Команда truncate удаляет только все строки таблицы. Он не удаляет столбцы, индексы, ограничения и схему.

Заключение

В этой статье мы рассмотрели команды SQL Delete и SQL Truncate для удаления строк в таблице. Я надеюсь, что эта статья поможет ответить на все ваши вопросы, связанные с командами удаления и усечения в SQL Server, а также понять разницу между этими командами. Вы должны быть осторожны при использовании команды truncate, потому что она удаляет все строки в таблице.

• Автор
• Последние сообщения

Раджендра Гупта

Привет! Я Раджендра Гупта, специалист по базам данных и архитектор, помогаю организациям быстро и эффективно внедрять решения Microsoft SQL Server, Azure, Couchbase, AWS, устранять связанные проблемы и настраивать производительность с более чем 14-летним опытом.