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;

Этот пример очистит таблицу customers, и удалит все записи из этой таблицы.

Было бы равносильно следующему предложению DELETE в Oracle:

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

Рассмотрим еще один пример, с префиксом таблицы — именем схемы.

Например:

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:

Пример 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 .
• Во-вторых, вы помещаете условие в предложение WHERE, чтобы указать, какие строки следует удалить. Если вы опустите предложение WHERE , оператор удалит все строки в таблице.

Если вы хотите удалить все строки в большой таблице, вы должны использовать оператор TRUNCATE TABLE, который более эффективен, чем оператор DELETE .

Примеры SQL DELETE

Давайте рассмотрим несколько примеров использования оператора DELETE.

SQL DELETE - удаление одной строки в таблице

Следующий оператор удаляет сотрудника с идентификатором 3 из таблицы сотрудников :

SQL DELETE - удаление всех строк в таблице

Чтобы удалить все строки в таблице employee , вы просто выполните следующий запрос: (не рекомендуется и сделайте резервную копию перед этим)

SQL DELETE - удаление связанных строк в нескольких таблицах

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

Например, каждый сотрудник работает на одной или нескольких территориях, и на каждой территории есть несколько сотрудников. Таблица территорий сотрудников используется для хранения отношений между сотрудниками и территориями.

При удалении строки в таблице сотрудников необходимо также удалить связанные строки в таблице территорий сотрудников . Для этого необходимо выполнить два оператора DELETE следующим образом:

Большинство систем управления базами данных позволяют создавать ограничение внешнего ключа, чтобы при удалении строки в таблице соответствующие строки соответствующая таблица также удаляется автоматически.Это гарантирует целостность данных. В этом случае вам просто нужно выполнить первый оператор 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.

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

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

Попытайся

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

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

 ВЫБРАТЬ * ИЗ поставщиков; 

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

В этом примере будет усечена таблица с именем поставщики и удалены все записи из этой таблицы. Это было бы эквивалентно следующему оператору 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 с условиями фильтрации, как во втором примере.

Давайте воспользуемся этими командами в качестве примера. Вот таблица продукт :

Этот запрос ...

УДАЛИТЬ ИЗ продукта;
 

… удаляет все данные в таблице продукт . После этого запроса таблица товара будет пустой.

А вот ГДЕ запрос ...

УДАЛИТЬ ИЗ товара ГДЕ цена

 
… удаляет только записи о молоке и сахаре, так как их цена ниже 2 долларов.90. (молоко - 2,40 доллара, сахар - 2,88 доллара.) 

 
 Теперь в таблице   продукт   содержит только записи с ценами выше 2,90 долл. США: 

 

Как работает УДАЛЕНИЕ?

Если вы не хотите удалять структуру таблицы или удаляете только определенные строки, используйте команду 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 ? Мы подготовили таблицу ниже, чтобы суммировать свойства каждой команды:

Какая операция лучше всего подходит для какого варианта использования?

• Чтобы удалить определенные строки, используйте 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, выполните следующий запрос.

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

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

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

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

Пример 3: инструкция SQL Delete и значения идентификаторов

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

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

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

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

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

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

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

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

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

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

Пример 4: Откат транзакции инструкции удаления SQL

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

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

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

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

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

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

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

Пример 5: Удалить все строки таблицы Employee с помощью оператора усечения

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

Пример 6: Команда усечения SQL и значения идентификаторов

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

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

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

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

Пример 7: Команда усечения SQL с откатом

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

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

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

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

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

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

Давайте рассмотрим разницу между командами SQL Delete и SQL 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.