DROP TABLE (Transact-SQL)
Удаляет одно или больше определений таблиц и все данные, индексы, триггеры, ограничения и разрешения для этих таблиц. Любое представление или хранимая процедура, ссылающаяся на удаленную таблицу, должна быть явно удалена с помощью инструкции DROP VIEW или DROP PROCEDURE. Отчет о зависимостях в таблице можно получить из представления sys.dm_sql_referencing_entities.
Синтаксис
-- Syntax for SQL Server, Azure SQL Database, Warehouse in Microsoft Fabric DROP TABLE [ IF EXISTS ] < database_name.schema_name.table_name | schema_name.table_name | table_name >[ . n ] [ ; ] -- Syntax for Azure Synapse Analytics and Parallel Data Warehouse DROP TABLE < database_name.schema_name.table_name | schema_name.table_name | table_name >[;] Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
database_name
Имя базы данных, в которой создана таблица.
База данных SQL Azure поддерживает формат трехкомпонентного имени имя_базы_данных.[имя_схемы].имя_объекта, если имя_базы_данных — это текущая база данных или tempdb, а имя_объекта начинается с символа #. База данных SQL Azure не поддерживает четырехкомпонентные имена.
IF EXISTS
Применимо к: SQL Server (SQL Server 2016 (13.x) до текущей версии.
Условное удаление таблицы только в том случае, если она уже существует.
schema_name
Имя схемы, которой принадлежит таблица.
table_name
Имя таблицы, предназначенной для удаления.
Замечания
Инструкцию DROP TABLE нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY. Сначала следует удалить ссылающееся ограничение FOREIGN KEY или ссылающуюся таблицу. Если и ссылающаяся таблица, и таблица, содержащая первичный ключ, удаляются с помощью одной инструкции DROP TABLE, ссылающаяся таблица должна быть первой в списке.
Несколько таблиц можно удалить из любой базы данных. Если удаляемая таблица ссылается на первичный ключ другой таблицы, которая также удаляется, ссылающаяся таблица с внешним ключом должна стоять в списке перед таблицей, содержащей указанный первичный ключ.
При удалении таблицы относящиеся к ней правила и значения по умолчанию теряют привязку, а любые связанные с таблицей ограничения или триггеры автоматически удаляются. Если таблица будет создана заново, нужно будет заново привязать все правила и значения по умолчанию, заново создать триггеры и добавить необходимые ограничения.
При удалении всех строк в таблице с помощью инструкции DELETE tablename или TRUNCATE TABLE таблица продолжает существовать, пока она не будет удалена.
Большие таблицы и индексы, использующие более 128 экстентов, удаляются в два этапа: логически и физически. На логическом этапе существующие единицы распределения, используемые в таблице, отмечаются для освобождения и остаются заблокированными до фиксации транзакции. В физической фазе страницы IAM, помеченные для освобождения, физически удаляются пакетами.
При удалении таблицы, которая содержит столбец VARBINARY(MAX) с атрибутом FILESTREAM, не будут удалены никакие данные, которые хранятся в файловой системе.
При удалении таблицы реестра ее зависимые объекты (таблица журнала и представление реестра) также удаляются. Таблицу журнала или представление реестра нельзя удалить напрямую. При удалении таблиц реестра и их зависимых объектов система применяет семантику обратимого удаления — в действительности они не удаляются, а помечаются как удаленные в представлениях системного каталога и переименовываются. Дополнительные сведения см. в разделе Рекомендации и ограничения для реестра.
Инструкции DROP TABLE и CREATE TABLE нельзя выполнять для одной таблицы в одном пакете. В противном случае может произойти непредвиденная ошибка.
Разрешения
Необходимо разрешение ALTER на схему, к которой принадлежит эта таблица, разрешение CONTROL для этой таблицы или членство в предопределенной роли базы данных db_ddladmin .
Если инструкция предусматривает удаление таблицы реестра, требуется разрешение ALTER LEDGER .
Примеры
А. Удаление таблицы из текущей базы данных
Следующий пример удаляет таблицу ProductVendor1 , ее данные и индексы из текущей базы данных.
DROP TABLE ProductVendor1 ; B. Удаление таблицы из другой базы данных
Следующий пример удаляет таблицу SalesPerson2 из базы данных AdventureWorks2022 . Пример может быть выполнен из любой базы данных на экземпляре сервера.
DROP TABLE AdventureWorks2022.dbo.SalesPerson2 ; C. Удаление временной таблицы
Следующий пример создает временную таблицу, проверяет ее наличие, удаляет ее и снова проверяет ее наличие. В этом примере не используется синтаксис IF EXISTS, доступный начиная с SQL Server 2016 (13.x).
CREATE TABLE #temptable (col1 INT); GO INSERT INTO #temptable VALUES (10); GO SELECT * FROM #temptable; GO IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL DROP TABLE #temptable; GO --Test the drop. SELECT * FROM #temptable; D. Удаление таблицы с помощью IF EXISTS
Применимо к: SQL Server (SQL Server 2016 (13.x) до текущей версии.
В следующем примере создается таблица с именем T1. Затем вторая инструкция удаляет таблицу. Третья инструкция не выполняет никаких действий, так как таблица уже удалена, однако это не вызывает ошибку.
CREATE TABLE T1 (Col1 INT); GO DROP TABLE IF EXISTS T1; GO DROP TABLE IF EXISTS T1; TRUNCATE TABLE SQL Server
Оператор TRUNCATE TABLE используется для очистки всех записей из таблицы в SQL Server. Он выполняет ту же функцию, что и оператор DELETE, без предложения WHERE.
Синтаксис
Синтаксис оператора TRUNCATE TABLE в SQL Server (Transact-SQL):
TRUNCATE TABLE [database_name.] [schema_name.] table_name
[ WITH ( PARTITIONS ( partition_number
| partition_number TO partition_number ) ] ;
Параметры или аргументы
database_name — необязательный. Если указано, это имя базы данных.
schema_name — необязательный. Если указано, это имя схемы, к которой принадлежит таблица.
table_name — таблица, которую вы хотите очистить.
WITH (PARTITIONS (partition_number | partition_number TO partition_number) — необязательно и может использоваться только с многораздельными таблицами. Если указано, partition_number — это номер раздела, который вы хотите усечь в многораздельной таблице. Чтобы перечислить несколько разделов, запятые разделяют значения или диапазоны номеров разделов. Если вы попытаетесь использовать этот раздел с таблицей, которая не разбита на разделы, SQL Server вернет ошибку. Эта функция недоступна во всех версиях SQL Server.
Примечание
- Вы не можете очистить таблицу, на которую ссылается внешний ключ.
- Прежде чем вы сможете очистить таблицу, вы должны обладать необходимыми привилегиями, такими как ALTER TABLE.
Пример
В SQL Server очистка таблицы — это быстрый способ очистить записи из таблицы, если вам не нужно беспокоиться об откате. Когда таблица очищается, удаление строк не регистрируется, поэтому откатывание невозможно. Очистка таблицы также намного проще, чем удаление таблицы и ее воссоздание.
Рассмотрим пример использования оператора TRUNCATE TABLE в SQL Server.
Например:
TRUNCATE TABLE (Transact-SQL)
Удаляет все строки в таблице или указанные секции таблицы, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.
Синтаксис
-- Syntax for SQL Server and Azure SQL Database TRUNCATE TABLE < database_name.schema_name.table_name | schema_name.table_name | table_name >[ WITH ( PARTITIONS ( < | > [ , . n ] ) ) ] [ ; ] ::= TO
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse TRUNCATE TABLE < database_name.schema_name.table_name | schema_name.table_name | table_name >[;] Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
database_name
Имя базы данных.
schema_name
Имя схемы, которой принадлежит таблица.
table_name
Имя таблицы, которая должна быть усечена, или таблицы, из которой удаляются все строки. table_name должно быть литералом. table_name не может быть функцией OBJECT_ID() или переменной.
WITH ( PARTITIONS ( < partition_number_expression> | range> > [ , . n ] ) )
Область применения: SQL Server (SQL Server 2016 (13.x) до текущей версии)
Указывает секции для усечения или секции, из которых удаляются все строки. Если таблица не секционирована, аргумент WITH PARTITIONS приведет к возникновению ошибки. Если предложение WITH PARTITIONS не указано, будет усечена вся таблица.
можно указать одним из следующих способов:
- Указав номер секции, например WITH (PARTITIONS (2))
- Указав номера нескольких секций, разделив их запятыми, например WITH (PARTITIONS (1, 5))
- Указав диапазоны секций и отдельные секции, например WITH (PARTITIONS (2, 4, 6 TO 8))
- можно указать номерами секций, разделенными ключевым словом TO, например: WITH (PARTITIONS (6 TO 8)) .
Для усечения секционированной таблицы таблицы и индексы должны быть выровнены (секционированы одной функцией секционирования).
Замечания
Инструкция TRUNCATE TABLE обладает следующими преимуществами по сравнению с инструкцией DELETE.
- Используется меньший объем журнала транзакций. Инструкция DELETE производит удаление по одной строке и заносит в журнал транзакций запись для каждой удаляемой строки. Инструкция TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблиц, и в журнал транзакций записывает только данные об освобождении страниц.
- Обычно используется меньшее количество блокировок. Если инструкция DELETE выполняется с блокировкой строк, для удаления блокируется каждая строка таблицы. Инструкция TRUNCATE TABLE всегда блокирует таблицу (включая блокировку схемы (SCH-M)) и страницу, но не каждую строку.
- В таблице остается нулевое количество страниц, без исключений. После выполнения инструкции DELETE в таблице могут все еще оставаться пустые страницы. Например, чтобы освободить пустые страницы в куче, необходима, как минимум, монопольная блокировка таблицы (LCK_M_X). Если операция удаления не использует блокировку таблицы, таблица (куча) будет содержать множество пустых страниц. В индексах после операции удаления могут оказаться пустые страницы, хотя эти страницы будут быстро освобождены процессом фоновой очистки.
Инструкция TRUNCATE TABLE удаляет все строки таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т. п. сохраняются. Чтобы удалить не только данные таблицы, но и ее определение, следует использовать инструкцию DROP TABLE .
Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.
Для операции TRUNCATE TABLE можно выполнить откат.
Ограничения
Нельзя использовать TRUNCATE TABLE в таблицах в следующих случаях.
- На таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.
- Таблица является частью индексированного представления.
- Таблица опубликована с использованием репликации транзакций или репликации слиянием.
- Это темпоральная таблица с управлением версиями.
- На таблицу ссылается ограничение EDGE.
Для таблиц с какими-либо из этих характеристик следует использовать инструкцию DELETE.
Инструкция TRUNCATE TABLE не может активировать триггер, поскольку она не записывает в журнал удаление отдельных строк. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).
В Azure Synapse Analytics and Analytics Platform System (PDW):
- Инструкцию TRUNCATE TABLE нельзя использовать в инструкции EXPLAIN.
- Инструкцию TRUNCATE TABLE невозможно выполнить внутри транзакции.
Усечение больших таблиц
В Microsoft SQL Server существует возможность удалять или усекать таблицы, которые имеют больше 128 экстентов, не удерживая одновременные блокировки для всех экстентов, предназначенных для удаления.
Разрешения
Минимально необходимым разрешением является ALTER для table_name. Разрешения TRUNCATE TABLE назначаются по умолчанию владельцу таблицы, членам предопределенной роли сервера sysadmin , а также предопределенных ролей базы данных db_owner и db_ddladmin , и они не могут быть переданы. Тем не менее инструкцию TRUNCATE TABLE можно встроить в модуль, например в хранимую процедуру, и предоставить соответствующие разрешения этому модулю с помощью предложения EXECUTE AS .
Примеры
А. Усечение таблицы
В ходе выполнения следующего примера удаляются все данные таблицы JobCandidate . Инструкции SELECT включены до и после инструкции TRUNCATE TABLE для сравнения результатов.
USE AdventureWorks2022; GO SELECT COUNT(*) AS BeforeTruncateCount FROM HumanResources.JobCandidate; GO TRUNCATE TABLE HumanResources.JobCandidate; GO SELECT COUNT(*) AS AfterTruncateCount FROM HumanResources.JobCandidate; GO B. Усечение секций таблицы
Область применения: SQL Server (SQL Server 2016 (13.x) до текущей версии)
В следующем примере выполняется усечение указанных секций секционированной таблицы. Синтаксис WITH (PARTITIONS (2, 4, 6 TO 8)) задает усечение секций с номерами 2, 4, 6, 7 и 8.
TRUNCATE TABLE PartitionTable1 WITH (PARTITIONS (2, 4, 6 TO 8)); GO Как полностью очистить таблицу sql
Полностью очистить таблицу, не удаляя её можно с помощью запроса DELETE . Мы указываем имя таблицы, но опускаем блок WHERE . Тогда удаляются абсолютно все записи, вне зависимости от условий, ведь их нет. 🙂
DELETE FROM wiped_table_name; -- Мы очистили таблицу wiped_table_name Кроме DELETE без условий можно использовать TRUNCATE . У неё есть свои особенности. Самая главная, она быстрее DELETE , так как не сканирует таблицы перед очисткой и наиболее полезна для очистки больших таблиц.
TRUNCATE cars, cups, parrots; -- Тут можно перечислять сколько угодно таблиц