Sql clear table: TRUNCATE TABLE (Transact-SQL) — SQL Server
Инструкция 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 для новичков.
На этом у меня все, пока!
Нравится4Не нравится1
TRUNCATE TABLE — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite
В этом учебном материале вы узнаете, как использовать оператор Oracle TRUNCATE TABLE с синтаксисом, примерами и практическими упражнениями.
Описание
Оператор TRUNCATE TABLE используется для удаления всех записей из таблицы в Oracle. Он выполняет ту же функцию что и DELETE, только без условий WHERE.
Предупреждение: Если вы очистите таблицу с помощью оператора TRUNCATE TABLE, то ее откат невозможен.
Синтаксис
Синтаксис для оператора TRUNCATE TABLE в Oracle/PLSQL:
TRUNCATE TABLE [schema_name.]table_name
[ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
[ DROP STORAGE | REUSE STORAGE ] ;
Параметры или аргументы
schema_name
Необязательный. Если указано, то это имя схемы к которой принадлежит таблица.
table_name
Таблица, которую вы хотите очистить.
PRESERVE MATERIALIZED VIEW LOG
Необязательный. Если указано, то materialized view log будет сохранено, когда таблица очищается. Это поведение по умолчанию.
PURGE MATERIALIZED VIEW LOG
Необязательный. Если указано, то materialized view log будет очищен, когда таблица очищается.
DROP STORAGE
Необязательный. Если указано, всё хранилище очищающихся строк будет высвобождено, за исключением пространства, которое было выделено в MINEXTENTS. Это поведение по умолчанию.
REUSE STORAGE
Необязательный. Если указано, всё хранилище очищающихся строк останется распределенным в таблице.
Пример
В Oracle очищение таблицы является быстрым способом, чтобы очистить записи из таблицы, если вам не нужно беспокоиться об откате. Одной из причин является то, что, когда таблица очищается, это не влияет на какие-либо из индексов, триггеров или зависимостей таблицы. Очищение таблицы также намного проще, чем удаление таблицы и ее воссоздания.
Рассмотрим пример того, как использовать оператор TABLE TRUNCATE в Oracle/PLSQL.
Например:
TRUNCATE TABLE customers;
| TRUNCATE TABLE customers; |
Этот пример очистит таблицу customers, и удалит все записи из этой таблицы.
Было бы равносильно следующему предложению DELETE в Oracle:
Оба эти примера приведут к тому, что все данные из таблицы customers удаляется. Основное различие между ними состоит в том, что вы можете выполнить откат DELETE если выберите, но вы не сможете выполнить откат оператора TRUNCATE TABLE.
Рассмотрим еще один пример, с префиксом таблицы — именем схемы.
Например:
TRUNCATE TABLE totn.suppliers;
| TRUNCATE TABLE totn. suppliers; |
Этот пример очистит таблицу с именем suppliers в схеме под названием totn. Перед тем, как очистить таблицу в другой схеме, вы должны иметь необходимые привилегии, такие как DROP ANY TABLE.
Оператор 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:
Singer | Album | Year | Sale |
The Prodigy | Invaders Must Die | 2008 | 1200000 |
Drowning Pool | Sinner | 2001 | 400000 |
Massive Attack | Mezzanine | 1998 | 2300000 |
The Prodigy | Fat of the Land | 1997 | 600000 |
The Prodigy | Music For The Jilted Generation | 1994 | 1500000 |
Massive Attack | 100th Window | 2003 | 1200000 |
Drowning Pool | Full Circle | 2007 | 800000 |
Massive Attack | Danny The Dog | 2004 | 1900000 |
Drowning Pool | Resilience | 2013 | 500000 |
Пример 1. Удалить все данные из таблицы используя оператор SQL TRUNCATE:
TRUNCATE TABLE Artists
Как было сказано выше, аналогичную задачу можно решить оператором SQL DELETE без использования оператора SQL WHERE:
DELETE FROM Artists
Команда 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 DELETE
Оператор SQL DELETE
Оператор DELETE
используется для удаления существующих записей в таблице.
УДАЛИТЬ Синтаксис
УДАЛИТЬ ИЗ имя_таблицы ГДЕ условие ;
Примечание: Будьте осторожны при удалении записей в таблице! Обратите внимание на
в
Предложение WHERE
DELETE
выписка.
Предложение WHERE
определяет, какие записи следует удалить.Если
если опустить предложение WHERE
, все записи в таблице будут удалены!
Демо-база данных
Ниже представлен выбор из таблицы «Клиенты» в примере Northwind.
база данных:
Идентификатор клиента | Имя клиента | ContactName | Адрес | Город | Почтовый индекс | Страна |
---|---|---|---|---|---|---|
1 | Альфредс Футтеркисте | Мария Андерс | Обере, ул.57 | Берлин | 12209 | Германия |
2 | Ana Trujillo Emparedados y helados | Ана Трухильо | Avda. de la Constitución 2222 | México D.F. | 05021 | Мексика |
3 | Антонио Морено Такерия | Антонио Морено | Матадерос 2312 | Мексика Д.F. | 05023 | Мексика |
4 | Вокруг Рога | Томас Харди | 120 Ганновер пл. | Лондон | WA1 1DP | Великобритания |
5 | Berglunds snabbköp | Кристина Берглунд | Berguvsvägen 8 | Лулео | С-958 22 | Швеция |
Пример SQL DELETE
Следующий оператор SQL удаляет клиента «Alfreds Futterkiste» из
Таблица «Покупатели»:
Пример
УДАЛИТЬ ОТ клиентов
ГДЕ CustomerName = ‘Альфред Футтеркисте’;
Попробуй сам »
Таблица «Клиенты» теперь будет выглядеть так:
Идентификатор клиента | Имя клиента | ContactName | Адрес | Город | Почтовый индекс | Страна |
---|---|---|---|---|---|---|
2 | Ana Trujillo Emparedados y helados | Ана Трухильо | Avda. de la Constitución 2222 | México D.F. | 05021 | Мексика |
3 | Антонио Морено Такерия | Антонио Морено | Матадерос 2312 | México D.F. | 05023 | Мексика |
4 | Вокруг Рога | Томас Харди | 120 Ганновер пл. | Лондон | WA1 1DP | Великобритания |
5 | Berglunds snabbköp | Кристина Берглунд | Berguvsvägen 8 | Лулео | С-958 22 | Швеция |
Удалить все записи
Можно удалить все строки в таблице, не удаляя таблицу. Этот
означает, что структура таблицы, атрибуты и индексы останутся без изменений:
Следующий оператор SQL удаляет все строки в таблице «Клиенты»,
без удаления таблицы:
Усечь / очистить табличную переменную в Sql Server 2008
Табличные переменные не поддерживают синтаксис TRUNCATE
— единственный способ их усечь — неявно, позволяя им выпадать из области видимости.
Как временные таблицы, так и переменные таблицы могут быть кэшированы при использовании в хранимых процедурах, и приведенное ниже может привести к тому, что та же самая переменная таблицы будет использоваться после усечения, а не фактического удаления, и создать
СОЗДАТЬ ПРОЦЕСС dbo.foo @start INT
В ВИДЕ
НАЧИНАТЬ
ОБЪЯВИТЬ @tableVariable ТАБЛИЦА (
id INT,
значение VARCHAR (20))
ВСТАВИТЬ В @tableVariable
(я бы,
значение)
ВЫБЕРИТЕ идентификатор,
значение
ОТ xTable
ГДЕ id = @start;
--Используйте @tableVariable
КОНЕЦ
ИДТИ
ПОКА @start <= @stop
НАЧИНАТЬ
EXEC dbo.foo @start
УСТАНОВИТЬ @start = @start + 1
КОНЕЦ
Конечно, гораздо более простой альтернативой было бы переключиться на использование таблицы #temp
, поскольку она напрямую поддерживает TRUNCATE
.
DML для табличных переменных и временных таблиц записывает в журнал транзакций tempdb
. Стоит ли переключаться на TRUNCATE
, а не на DELETE
, зависит от размера задействованных данных. TRUNCATE
просто регистрирует освобождение страниц. DELETE
будет регистрировать фактические удаленные значения. Еще одно различие между ними заключается в том, что TRUNCATE
освобождает последнюю страницу из таблицы, а DELETE
- нет. Если на каждой итерации цикла вставляется и удаляется только небольшое количество данных, то накладные расходы на регистрацию удаленных строк могут быть меньше, чем накладные расходы на постоянное освобождение и перераспределение отдельной страницы в таблице.
И наоборот, если вы будете вставлять и удалять большие объемы данных на каждой итерации, вы можете обнаружить, что TRUNCATE
не только делает операцию удаления всех строк более эффективной, но также может принести пользу последующему оператору вставки.
SQL DELETE - удаление данных в таблице или нескольких таблицах
Сводка : в этом руководстве вы узнаете, как использовать оператор SQL DELETE для удаления одной или нескольких строк в таблице.
Оператор DELETE
навсегда удаляет одну или несколько строк из таблицы. Синтаксис оператора DELETE
следующий:
DELETE FROM table WHERE condition; |
Чтобы удалить одну или несколько строк в таблице:
- Сначала вы указываете имя таблицы, из которой вы хотите удалить данные, в предложении
DELETE FROM
. - Во-вторых, вы помещаете условие в предложение WHERE, чтобы указать, какие строки следует удалить. Если вы опустите предложение
WHERE
, оператор удалит все строки в таблице.
Если вы хотите удалить все строки в большой таблице, вы должны использовать оператор TRUNCATE TABLE, который более эффективен, чем оператор DELETE
.
Примеры SQL DELETE
Давайте рассмотрим несколько примеров использования оператора DELETE.
SQL DELETE - удаление одной строки в таблице
Следующий оператор удаляет сотрудника с идентификатором 3 из таблицы сотрудников
:
УДАЛИТЬ ОТ сотрудников WHERE employeeID = 3; |
SQL DELETE - удаление всех строк в таблице
Чтобы удалить все строки в таблице employee
, вы просто выполните следующий запрос: (не рекомендуется и сделайте резервную копию перед этим)
SQL DELETE - удаление связанных строк в нескольких таблицах
Это становится более сложным, когда вы хотите удалить строку в таблице, которая связана с другими строками в другой таблице.
Например, каждый сотрудник работает на одной или нескольких территориях, и на каждой территории есть несколько сотрудников. Таблица территорий сотрудников
используется для хранения отношений между сотрудниками и территориями.
При удалении строки в таблице сотрудников
необходимо также удалить связанные строки в таблице территорий сотрудников
. Для этого необходимо выполнить два оператора DELETE
следующим образом:
DELETE FROM employee WHERE employeeID = 3; |
УДАЛИТЬ С территории сотрудников WHERE employeeID = 3 |
Большинство систем управления базами данных позволяют создавать ограничение внешнего ключа, чтобы при удалении строки в таблице соответствующие строки соответствующая таблица также удаляется автоматически.Это гарантирует целостность данных. В этом случае вам просто нужно выполнить первый оператор DELETE
только для удаления строк в двух таблицах.
В случае, если система управления базой данных не поддерживает ограничение внешнего ключа, вы должны выполнить оба оператора DELETE
в одной транзакции, чтобы убедиться, что операторы выполняются в режиме «все или ничего».
В этом руководстве мы показали вам, как использовать оператор SQL DELETE
для удаления одной или нескольких строк в таблице.
- Было ли это руководство полезным?
- Да Нет
SQL: TRUNCATE TABLE Statement
В этом руководстве по SQL объясняется, как использовать оператор TRUNCATE TABLE SQL с синтаксисом и примерами.
Описание
Оператор SQL TRUNCATE TABLE используется для удаления всех записей из таблицы. Он выполняет ту же функцию, что и оператор DELETE, без предложения WHERE.
Предупреждение : Если вы усекаете таблицу, оператор TRUNCATE TABLE не может быть отменен в некоторых базах данных.
Синтаксис
Синтаксис оператора TRUNCATE TABLE в SQL:
TRUNCATE TABLE имя_таблицы;
Параметры или аргументы
- имя_таблицы
- Таблица, которую вы хотите усечь.
DDL / DML для примеров
Если вы хотите следовать этому руководству, получите DDL для создания таблиц и DML для заполнения данных. Тогда попробуйте примеры в своей базе данных!
Получить DDL / DML
Пример
Вы можете усечь таблицу вместо того, чтобы отбрасывать ее и создавать заново.Усечение таблицы может быть быстрее и не влияет на индексы, триггеры и зависимости таблицы. Это также быстрый способ удалить записи из таблицы, если вам не нужно беспокоиться об откате.
Рассмотрим пример использования оператора TRUNCATE TABLE в SQL.
В этом примере у нас есть таблица с именем поставщиков со следующими данными:
ID поставщика | имя_поставщика | город | состояние |
---|---|---|---|
100 | Microsoft | Редмонд | Вашингтон |
200 | Маунтин-Вью | Калифорния | |
300 | Оракул | Редвуд-Сити | Калифорния |
400 | Кимберли-Кларк | Ирвинг | Техас |
500 | Тайсон Фудс | Спрингдейл | Арканзас |
600 | СК Джонсон | Расин | Висконсин |
700 | Dole Food Company | Вестлейк-Виллидж | Калифорния |
800 | Цветы Еда | Thomasville | Грузия |
900 | Электронное искусство | Редвуд-Сити | Калифорния |
Введите следующий оператор TRUNCATE TABLE:
Попытайся
поставщиков TRUNCATE TABLE;
Затем снова выберите данные из таблицы поставщиков :
ВЫБРАТЬ * ИЗ поставщиков;
Вот результаты, которые вы должны увидеть:
ID поставщика | имя_поставщика | город | состояние |
---|---|---|---|
В этом примере будет усечена таблица с именем поставщики и удалены все записи из этой таблицы. Это было бы эквивалентно следующему оператору DELETE в SQL:
Попытайся
УДАЛИТЬ ОТ поставщиков;
Оба этих оператора приведут к удалению всех данных из таблицы поставщиков . Основное различие между ними состоит в том, что вы можете откатить инструкцию DELETE, если захотите, но вы не сможете откатить инструкцию TRUNCATE TABLE во всех базах данных SQL.
Если вам нужно префикс имени таблицы с именем базы данных, вы можете переписать оператор TRUNCATE TABLE следующим образом:
ОБРЕЗАННАЯ ТАБЛИЦА общ.поставщики;
В этом примере будет усечена таблица с именем поставщики в базе данных с именем до .
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
с условиями фильтрации, как во втором примере.
Давайте воспользуемся этими командами в качестве примера. Вот таблица продукт
:
id | наименование | цена |
---|---|---|
1 | молоко | 2,40 |
2 | хлеб | 3,68 |
3 | масло сливочное | 5.55 |
4 | сахар | 2,88 |
Этот запрос ...
УДАЛИТЬ ИЗ продукта;
… удаляет все данные в таблице продукт
. После этого запроса таблица товара
будет пустой.
А вот ГДЕ
запрос ...
УДАЛИТЬ ИЗ товара ГДЕ цена… удаляет только записи о молоке и сахаре, так как их цена ниже 2 долларов.90. (молоко - 2,40 доллара, сахар - 2,88 доллара.)
Теперь в таблице
продукт
содержит только записи с ценами выше 2,90 долл. США:
id | наименование | цена |
---|---|---|
2 | хлеб | 3,68 |
3 | масло сливочное | 5,55 |
Как работает УДАЛЕНИЕ?
Если вы не хотите удалять структуру таблицы или удаляете только определенные строки, используйте команду 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
? Мы подготовили таблицу ниже, чтобы суммировать свойства каждой команды:
УДАЛИТЬ | УРЕЗАТЬ | ОТПРАВИТЬ | |
---|---|---|---|
Тип | DML | DDL | DDL |
Использует замок | Замок стола | Замок ряда | Замок стола |
Работает ГДЕ | Да | Нет | Нет |
Снимает... | Одна, несколько или все строки в таблице. | Все строки в таблице. | Вся структура таблицы: данные, привилегии, индексы, ограничения, триггеры. |
Сбрасывает автоприращение идентификатора | Нет | MySQL: Да Oracle: Нет PostgreSQL: Пользователь принимает решение SQL Server: Да | Не применяется |
Откат | Да | MySQL: Нет Oracle: Нет PostgreSQL: Да SQL Server: Да | MySQL: Нет Oracle: Да PostgreSQL: Да SQL Server: Да |
Ведение журнала транзакций | Каждая строка | Вся таблица (минимальная) | Вся таблица (минимальная) |
Работает с индексированными представлениями | Да | Нет | Нет |
Требуемое пространство | Больше места | Меньше места | Больше места |
Пожарные триггеры | Да | Нет | Нет |
Скорость | Медленно | Самый быстрый | Быстрее |
Какая операция лучше всего подходит для какого варианта использования?
- Чтобы удалить определенные строки, используйте
DELETE
. - Чтобы удалить все строки из большой таблицы и оставить структуру таблицы, используйте
TRUNCATE TABLE
. Это быстрее, чемDELETE
. - Чтобы удалить всю таблицу, включая ее структуру и данные, используйте
DROP TABLE
.
Вы можете узнать больше о создании структур баз данных на нашем пути разработки данных.
В SQL, DELETE
, TRUNCATE TABLE
и DELETE TABLE
имеют свои плюсы и минусы.Надеюсь, эта статья помогла вам понять, когда и как использовать каждую команду.
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
, если вы хотите удалить ограниченное количество строк на основе определенного условия или если вы не хотите сбрасывать значение автоинкремента.
Разница между операторами 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]. [Сотрудник] , где Город = «Сан-Антонио» |
Нам нужно использовать строковое значение в одинарных кавычках.Мы можем напрямую указать значение без одинарных кавычек в предложении Where, например EmpID, в следующем запросе.
УДАЛИТЬ [SQLShackDemo]. [Dbo]. [Сотрудник] ГДЕ EmpID = 1001; |
В фактическом плане выполнения предложения Delete мы видим, что он использует оператор удаления кластерного индекса для удаления определенной строки.
Пример 3: инструкция SQL Delete и значения идентификаторов
Оператор Delete удаляет записи одну за другой и записывает каждую запись в журнал транзакций.Это оператор DML.
Предположим, мы удаляем строку из таблицы с помощью оператора DELETE, и эта таблица содержит значения SQL IDENTITY. Значения IDENTITY генерируются при каждой новой вставке записи. У нас может быть вопрос-
Что происходит со значением идентичности после удаления записи?
Давайте рассмотрим этот сценарий на примере. У нас есть следующие данные в таблице сотрудников.
Столбец EmpID является столбцом идентификации.Мы можем проверить столбец идентификаторов в таблице с помощью команды 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.
НАЧАТЬ СДЕЛКУ; УДАЛИТЬ ИЗ [SQLShackDemo].[dbo]. [Сотрудник] ГДЕ EmpID = 1010; |
Мы удалили запись с EmpId 1010 в таблице сотрудников.
Мы можем использовать для отката транзакции. Выполните эту команду отката и просмотрите записи в таблице. Мы видим, что удаленная запись получает откат, и мы видим это в таблице «Сотрудник».
Команда усечения SQL
SQL Truncate - это команда языка определения данных (DDL).Удаляет все строки в таблице. SQL Server хранит данные таблицы на страницах. Команда truncate удаляет строки, освобождая страницы. Он делает запись об освобождении страниц в журнале транзакций. Он не регистрирует удаление каждой строки в журнале транзакций.
Мы не можем указать какое-либо условие в команде SQL Truncate . На высоком уровне вы можете рассматривать команду усечения, аналогичную команде Delete, без предложения Where. Он блокирует таблицу и страницы вместо строки.
SQL Server не показывает фактический план выполнения команды усечения SQL. На следующем снимке экрана вы можете увидеть примерный план выполнения.
Пример 5: Удалить все строки таблицы Employee с помощью оператора усечения
Команда усечения SQL проста; вам просто нужно передать имя таблицы, чтобы удалить все строки.
ОБРЕЗАТЬ ТАБЛИЦУ [SQLShackDemo].[dbo]. [Сотрудник]; |
Пример 6: Команда усечения SQL и значения идентификаторов
В предыдущем примере 3 мы исследовали команду удаления со значениями идентификаторов. Удалить не сбрасывает значения идентичности. Давайте посмотрим, как команда усечения ведет себя со значениями идентификаторов.
Сначала выполните команду из примера 5, чтобы удалить все строки таблицы.
ОБРЕЗАТЬ ТАБЛИЦУ [SQLShackDemo].[dbo]. [Сотрудник]; |
Стол сейчас пуст. Вставим новую запись в таблицу.
ВСТАВИТЬ в значения сотрудников ('bb', 'bb', 'b', 'bb', 1,555) |
Вы можете видеть, что значение Identity снова начинается с 1, как определено в свойствах таблицы.
Пример 7: Команда усечения SQL с откатом
В примере 4 мы исследовали возможность отката транзакции с помощью команды удаления.Администраторы баз данных ошибочно считают, что мы не можем откатить транзакцию, выполненную с помощью команды TRUNCATE. Это правда?
Давайте еще раз рассмотрим это на примере.
Запустите транзакцию, чтобы усечь таблицу сотрудников.
НАЧАТЬ ТРАН; ОБРЕЗАТЬ ТАБЛИЦУ [SQLShackDemo]. [Dbo]. [Сотрудник]; |
После выполнения команды убедитесь, что в таблице нет записей.
Теперь выполните команду 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 освобождает все страницы данных таблицы. Следовательно, он также удаляет всю статистику и выделенное пространство. |
Команда удаления также может активировать триггер.Удалить работает с отдельными строками и удаляет данные. Следовательно, он активирует триггер. | Команда усечения не может активировать триггер. Триггер активируется при любом изменении строки. В этой команде SQL Server освобождает все страницы, поэтому он не активирует триггер. |
Команда Delete удаляет строки, соответствующие предложению where. Также не удаляются столбцы, индексы, ограничения, схемы. | Команда truncate удаляет только все строки таблицы.Он не удаляет столбцы, индексы, ограничения и схему. |
Заключение
В этой статье мы изучили команды SQL Delete и SQL Truncate для удаления строк в таблице. Я надеюсь, что эта статья поможет ответить на все ваши вопросы, связанные с командами удаления и усечения в SQL Server, а также понять разницу в этих командах. Будьте осторожны при использовании команды truncate, потому что она удаляет все строки в таблице.
Как сертифицированный MCSA и сертифицированный инструктор Microsoft в Гургаоне, Индия, с 13-летним опытом работы, Раджендра работает в различных крупных компаниях, специализируясь на оптимизации производительности, мониторинге, высокой доступности и стратегиях и внедрении аварийного восстановления. Он является автором сотен авторитетных статей о SQL Server, Azure, MySQL, Linux, Power BI, настройке производительности, AWS / Amazon RDS, Git и связанных технологиях, которые на сегодняшний день просмотрели более 10 миллионов читателей.
Он является создателем одного из крупнейших бесплатных онлайн-сборников статей по одной теме с его серией из 50 статей о группах доступности SQL Server Always On. Основываясь на своем вкладе в сообщество SQL Server, он был отмечен различными наградами, включая престижную награду «Лучший автор года» в 2020 и 2021 годах на SQLShack.