Sql truncate table: SQL DROP TABLE, TRUNCATE TABLE

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-летним опытом.