Каков наилучший способ удалить всю большую таблицу в t-sql? Sql очистить таблицу


Очистить таблицу MySQL можно двумя простыми способами

Здравствуйте! В ходе моей работы появилась необходимость в MySQL очистить таблицу наиболее быстрым способом. Напрямую с базой данных и языками программирования типа PHP, C сталкиваться последние времени приходится не часто. Кое-что уже подзабыл и залез в свои заметки, чтобы освежить память.

На удивление быстро все нашел и вспомнил. В текущей публикации поделюсь с вами.

Полная и выборочная очистка таблицы

В целом для такой работы с MySQL есть два подходящих запроса:

У них есть существенная разница в производительности – скорости исполнения операции.

TRUNCATE обычно выполняется быстрее, потребляя меньше системных ресурсов.

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

TRUNCATE TABLE newspk;

Итак, с полной очисткой таблицы и её преимуществами разобрались, посмотрим на выборочную.

DELETE FROM newspk WHERE id=’34’;

Здесь уже используется условие, так что удалиться только запись с идентификатором 34. Если же условие WHERE не будет, то произойдет полное очищение.

Как видите, в MySQL можно очистить таблицу достаточно просто. Знаете о других способах или просто желаете поделиться размышлениями? Под текстом данной заметки можно оставить комментарии.

Отмечу, что программирование относится к моей сфере деятельности, однако является второстепенной темой на этом блоге. Основная тематика – работа в интернете. При том стараюсь рассматривать не всё подряд, а только нормальные способы с хорошими финансовыми перспективами. При желании можете посмотреть публикации.

Обновления на блоге появляются регулярно. Анонсы новых материалов добавляю в социальные сети, ссылки для перехода есть в боковой колонке. Также через специальную форму можно подписаться на бесплатную рассылку по e-mail. До связи.

workip.ru

TRUNCATE TABLE (Transact-SQL) | Microsoft Docs

В этой статье

Удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.

Синтаксические обозначения в Transact-SQL

Синтаксис

TRUNCATE TABLE     [ { database_name .[ schema_name ] . | schema_name . } ]     table_name [ ; ]

Аргументы

Замечания

Инструкция TRUNCATE TABLE обладает следующими преимуществами по сравнению с инструкцией DELETE:

Инструкция TRUNCATE TABLE удаляет все строки таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т. п. сохраняются. Чтобы удалить не только данные таблицы, но и ее определение, следует использовать инструкцию DROP TABLE.

Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.

Ограничения

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

Для таблиц с какими-либо из этих характеристик следует использовать инструкцию DELETE.

Инструкция TRUNCATE TABLE не может активировать триггер, поскольку она не записывает в журнал удаление отдельных строк. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).

Усечение больших таблиц

В Microsoft SQL Server существует возможность удалять или усекать таблицы, которые имеют больше 128 экстентов, не удерживая одновременные блокировки для всех экстентов, предназначенных для удаления.

Разрешения

Минимально необходимым разрешением является ALTER table_name. Разрешения TRUNCATE TABLE назначаются по умолчанию владельцу таблицы, членам предопределенной роли сервера sysadmin, а также предопределенных ролей базы данных db_owner и db_ddladmin, они не могут быть переданы. Тем не менее инструкцию TRUNCATE TABLE можно встроить в модуль, например в хранимую процедуру, и предоставить соответствующие разрешения этому модулю с помощью предложения EXECUTE AS.

Примеры

В ходе выполнения следующего примера удаляются все данные таблицы JobCandidate. Инструкции SELECT включены до и после инструкции TRUNCATE TABLE для сравнения результатов.

USE AdventureWorks2012; GO SELECT COUNT(*) AS BeforeTruncateCount FROM HumanResources.JobCandidate; GO TRUNCATE TABLE HumanResources.JobCandidate; GO SELECT COUNT(*) AS AfterTruncateCount FROM HumanResources.JobCandidate; GO

См. также

Справочник

DELETE (Transact-SQL)

DROP TABLE (Transact-SQL)

IDENTITY (свойство) (Transact-SQL)

msdn.microsoft.com

Каков наилучший способ удалить всю большую таблицу в t-sql? Безопасный SQL

Мы столкнулись с несколько странной ситуацией. В принципе, в одной из наших баз данных есть две таблицы, на которые подаются тонны и тонны данных регистрации, которые нам не нужны или не заботятся. Частично из-за этого у нас заканчивается дисковое пространство.

Я пытаюсь очистить столы, но это происходит вечно (есть все еще 57 000 000+ записей после того, как это пропустит выходные … и это только первый стол!)

Просто использование таблицы удаления занимает навсегда и ест пространство на диске (я считаю, из-за журнала транзакций.) Сейчас я использую цикл while для удаления записей X за раз, играя с X, чтобы определить, что на самом деле быстрее всего. Например, X = 1000 занимает 3 секунды, а X = 100 000 занимает 26 секунд … что делает математику немного быстрее.

Но вопрос в том, есть ли лучший способ?

(Как только это будет сделано, при запуске задания агента SQL очистите таблицу один раз в день … но сначала нужно ее очистить).

Solutions Collecting From Web of "Каков наилучший способ удалить всю большую таблицу в t-sql?"

ПРОВЕРИТЬ таблицу или отключить индексы перед удалением

TRUNCATE TABLE [tablename]

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

Чтобы добавить к другим ответам, если вы хотите сохранить данные за прошлый день (или за последний месяц или год или что-то еще), затем сохраните это, выполните команду TRUNCATE TABLE и вставьте ее обратно в исходную таблицу:

SELECT * INTO tmp_My_Table FROM My_Table WHERE <Some_Criteria> TRUNCATE TABLE My_Table INSERT INTO My_Table SELECT * FROM tmp_My_Table

Следующее, что нужно сделать, это спросить себя, почему вы вставляете всю эту информацию в журнал, если об этом никто не заботится. Если вам это действительно не нужно, отключите ведение журнала в источнике.

1) Таблица усечения

2) скрипт из таблицы, падение и воссоздание таблицы

TRUNCATE TABLE [tablename]

удалит все записи без ведения журнала.

В зависимости от того, сколько вы хотите сохранить, вы можете просто скопировать записи, которые вы хотите в таблицу temp, обрезать таблицу журналов и скопировать записи таблицы temp обратно в таблицу журналов.

Если вы можете выработать оптимальный x, это будет постоянно обходить удаление по самым быстрым темпам. Установка rowcount ограничивает количество записей, которые будут удалены на каждом шаге цикла. Если лог-файл становится слишком большим; вставьте счетчик в цикле и обрезайте каждый миллион строк или около того.

set @@ rowcount x while 1 = 1 Начало

удалить из таблицы. Если @@ Rowcount = 0 break

Конец

Изменение режима ведения журнала на db на простое или массовое протоколирование уменьшит часть издержек на удаление.

Проверь это

  1. статья из MSDN Delete_a_Huge_Amount_of_Data_from
  2. Информация о моделях восстановления
  3. и просмотреть или изменить модель восстановления базы данных

sql.fliplinux.com

TRUNCATE TABLE | Oracle PL/SQL •MySQL •SQL Server

Это учебное пособие Oracle объясняет, как использовать оператор 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.

oracleplsql.ru

TRUNCATE TABLE оператор MySQL | Oracle PL/SQL •MySQL •SQL Server

В этом учебном пособии вы узнаете, как использовать MySQL оператор TRUNCATE TABLE с синтаксисом и примерами.

Описание

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

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

Синтаксис

Синтаксис TRUNCATE TABLE в MySQL:TRUNCATE TABLE [database_name.]table_name;

Параметры или аргументы

database_name — необязательный. Если указан, то это имя базы данных.table_name — таблица, которую вы хотите очистить.

Примечание

Пример

В MySQL очистка таблицы — это быстрый способ очистить записи из таблицы, если вам не нужно беспокоиться об откате. В большинстве случаев MySQL обрабатывает процесс очистки таблицы несколько иначе, чем другие базы данных, такие как Oracle или SQL Server.

Рассмотрим пример использования TRUNCATE TABLE в MySQL.Например:

TRUNCATE TABLE customers;

TRUNCATE TABLE customers;

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

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

Рассмотрим еще один пример, где мы используем префикс имени таблицы с именем базы данных.Например:

TRUNCATE TABLE best.products;

TRUNCATE TABLE best.products;

В этом примере очищается таблица, называемая products в базе данных best.

oracleplsql.ru

sql - Как удалить все таблицы из базы данных с помощью одного SQL-запроса?

Я не хочу набирать имя всех таблиц, чтобы удалить все из них. Возможно ли это с одним запросом?

источник поделиться

Используйте представление INFORMATION_SCHEMA.TABLES, чтобы получить список таблиц. Создайте сценарии Drop в инструкции select и опустите его с помощью Dynamic SQL:

DECLARE @sql NVARCHAR(max)='' SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' Exec Sp_executesql @sql

Sys.Tables Версия

SELECT ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; ' FROM sys.tables t JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] WHERE t.type = 'U' Exec sp_executesql @sql

Примечание. Если у вас есть foreign Keys, определенный между таблицами, сначала выполните нижеприведенный запрос отключить все foreign Keys, имеющиеся в вашей базе данных.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Для получения дополнительной информации проверьте здесь.

источник поделиться

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

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Это скрытая хранимая процедура в sql-сервере и будет выполнена для каждой таблицы в базе данных, к которой вы подключены.

Примечание. Перед запуском над запросом сначала проверьте, есть ли у вас отношения внешних ключей с любой таблицей. Если у вас есть только disable ограничение внешнего ключа, запустив этот запрос

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" источник поделиться

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

USE Databasename SELECT 'DROP TABLE [' + name + '];' FROM sys.tables

Затем скопируйте и вставьте в новое окно SSMS, чтобы запустить его.

ответ дан Dave.Gugg 22 дек. '14 в 19:30 источник поделиться

Я просто сделаю небольшое изменение для ответа @NoDisplayName и использую QUOTENAME() в столбце TABLE_NAME, а также включить столбец TABLE_SCHEMA, чтобы таблицы не находились в схеме dbo.

DECLARE @sql nvarchar(max) = ''; SELECT @sql += 'DROP TABLE ' + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME]) + ';' FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_TYPE] = 'BASE TABLE'; EXEC SP_EXECUTESQL @sql;

Или используя sys виды схемы (согласно комментарию @swasheck):

DECLARE @sql nvarchar(max) = ''; SELECT @sql += 'DROP TABLE ' + QUOTENAME([S].[name]) + '.' + QUOTENAME([T].[name]) + ';' FROM [sys].[tables] AS [T] INNER JOIN [sys].[schemas] AS [S] ON ([T].[schema_id] = [S].[schema_id]) WHERE [T].[type] = 'U' AND [T].[is_ms_shipped] = 0; EXEC SP_EXECUTESQL @sql; ответ дан AeroX 22 дек. '14 в 19:37 источник поделиться

Если у кого-то еще была проблема с лучшим решением ответа (включая отключение внешних ключей), вот другое решение от меня:

-- CLEAN DB use [DB_NAME] EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'DELETE FROM ?' DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql WHILE (@@FETCH_STATUS = 0) BEGIN Exec SP_EXECUTESQL @Sql FETCH NEXT FROM @Cursor INTO @Sql END CLOSE @Cursor DEALLOCATE @Cursor GO EXEC sp_MSForEachTable 'DROP TABLE ?' GO EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' ответ дан Ani 13 июля '16 в 16:18 источник поделиться

В качестве продолжения ответа Dave.Gugg это будет код, который кому-то понадобится, чтобы получить все строки DROP TABLE в MySQL:

SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'your_database_name' ответ дан OMA 13 нояб. '15 в 1:05 источник поделиться

Используйте следующие script to drop все constraints:

DECLARE @sql NVARCHAR(max)='' SELECT @sql += ' ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT all; ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' Exec Sp_executesql @sql

Затем запустите все, чтобы удалить все таблицы:

select @sql=''; SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' Exec Sp_executesql @sql

Это работало для меня в Azure SQL Database, где 'sp_msforeachtable' не было доступно!

источник поделиться

Не совсем 1 запрос, все еще довольно короткий и сладкий:

-- Disable all referential integrity constraints EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO -- Drop all PKs and FKs declare @sql nvarchar(max) SELECT @sql = STUFF((SELECT '; ' + 'ALTER TABLE ' + Table_Name +' drop constraint ' + Constraint_Name from Information_Schema.CONSTRAINT_TABLE_USAGE ORDER BY Constraint_Name FOR XML PATH('')),1,1,'') EXECUTE (@sql) GO -- Drop all tables EXEC sp_msforeachtable 'DROP TABLE ?' GO источник поделиться

Самый быстрый способ - сбросить и воссоздать всю базу данных. Вы можете щелкнуть по script в SQL Management Studio:

источник поделиться

qaru.site