Truncate table (transact-sql)truncate table (transact-sql)

What’s the Difference Between TRUNCATE and DELETE?

So what’s the difference between the TRUNCATE and DELETE statements?


DELETE can delete specific records, TRUNCATE deletes all records

You may have noticed that there is no WHERE clause on the TRUNCATE statement. This is because all of the data in the table is removed when you run a TRUNCATE statement.

With the DELETE statement, you can delete all records, or use the WHERE clause to delete some records.

DELETE Allows You To Rollback, TRUNCATE Does Not Allow Rollback

With the DELETE statement, you can run a ROLLBACK statement and effectively restore the data you have deleted. However, if you run the ROLLBACK statement after a TRUNCATE statement, then you won’t be able to recover the data. You can’t rollback a TRUNCATE statement.

DELETE generates an undo log, TRUNCATE does not

This is what makes TRUNCATE statements faster than DELETE statements. When you run a DELETE statement, the undo log is generated so that the ROLLBACK statement can be run.

DELETE takes locks on rows, TRUNCATE does not

When you run a DELETE statement, the rows are locked while they are deleted. With the TRUNCATE statement, they are not locked.

DELETE causes delete triggers to fire, TRUNCATE does not

If you use triggers, this might cause different behaviour in your database.

DELETE is a DML command, TRUNCATE is a DDL command.

This difference means that DELETE can be rolled back and TRUNCATE cannot.

Практическое упражнение № 2:

На основании таблицы suppliers и customers, заполненной следующими данными, обновите city в таблице suppliers значением поля city в таблице customers, когда supplier_name таблицы suppliers соответствует customer_name в таблице customers.

Oracle PL/SQL

CREATE TABLE suppliers ( supplier_id number(10) not null, supplier_name varchar2(50) not null, city varchar2(50), CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) );

INSERT INTO suppliers (supplier_id, supplier_name, city) VALUES (5001, ‘Microsoft’, ‘New York’);

INSERT INTO suppliers (supplier_id, supplier_name, city) VALUES (5002, ‘IBM’, ‘Chicago’);

INSERT INTO suppliers (supplier_id, supplier_name, city) VALUES (5003, ‘Red Hat’, ‘Detroit’);

INSERT INTO suppliers (supplier_id, supplier_name, city) VALUES (5005, ‘NVIDIA’, ‘LA’);

CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50) not null, city varchar2(50), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );

INSERT INTO customers (customer_id, customer_name, city) VALUES (7001, ‘Microsoft’, ‘San Francisco’);

INSERT INTO customers (customer_id, customer_name, city) VALUES (7002, ‘IBM’, ‘Toronto’);

INSERT INTO customers (customer_id, customer_name, city) VALUES (7003, ‘Red Hat’, ‘Newark’);

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

CREATETABLEsuppliers (supplier_idnumber(10)notnull,

supplier_namevarchar2(50)notnull,

cityvarchar2(50),

CONSTRAINTsuppliers_pkPRIMARYKEY(supplier_id) );  

INSERTINTOsuppliers(supplier_id,supplier_name,city)

VALUES(5001,’Microsoft’,’New York’);  

INSERTINTOsuppliers(supplier_id,supplier_name,city)

VALUES(5002,’IBM’,’Chicago’);  

INSERTINTOsuppliers(supplier_id,supplier_name,city)

VALUES(5003,’Red Hat’,’Detroit’);  

INSERTINTOsuppliers(supplier_id,supplier_name,city)

VALUES(5005,’NVIDIA’,’LA’);  

CREATETABLEcustomers (customer_idnumber(10)notnull,

customer_namevarchar2(50)notnull,


cityvarchar2(50),

CONSTRAINTcustomers_pkPRIMARYKEY(customer_id) );  

INSERTINTOcustomers(customer_id,customer_name,city)

VALUES(7001,’Microsoft’,’San Francisco’);  

INSERTINTOcustomers(customer_id,customer_name,city)

VALUES(7002,’IBM’,’Toronto’);  

INSERTINTOcustomers(customer_id,customer_name,city)

VALUES(7003,’Red Hat’,’Newark’);

Решение для практического упражнения № 2:

Следующий оператор UPDATE будет выполнять это обновление в Oracle.

Oracle PL/SQL

UPDATE suppliers SET city = (SELECT customers.city FROM customers WHERE customers.customer_name = suppliers.supplier_name) WHERE EXISTS (SELECT customers.city FROM customers WHERE customers.customer_name = suppliers.supplier_name);

1 2 3 4 5 6 7

UPDATEsuppliers

SETcity=(SELECTcustomers.city

FROMcustomers

WHEREcustomers.customer_name=suppliers.supplier_name)

WHEREEXISTS(SELECTcustomers.city

FROMcustomers

WHEREcustomers.customer_name=suppliers.supplier_name);

Таблица suppliers будет выглядеть следующим образом:

SUPPLIER_ID SUPPLIER_NAME CITY
5001 Microsoft San Francisco
5002 IBM Toronto
5003 Red Hat Newark
5004 NVIDIA LA

IDENTITY Property during TRUNCATE TABLE

IDENTITY property is used in a table when you need to auto increase a number for a column. This means that when the first record is inserted IDENTITY column will become 1 and the next record will be 2 and so on. When entire data in the table is deleted what will happen to the next number.

When the DELETE statement is executed, previous records are counted. Let us execute the following T-SQL to get the record count and maximum number for the identity column.

1 2 3

SELECTMAX(ID)CurrentNumber,

COUNT(*)RecordCount

FROMSampleTable

Following is the output for the above query.

You will see that though the record count is 1, the current maximum number of the identity column is 500001. This means that DELETE statement will not be impacted to IDENTITY column’s next value and DELETE statement will NOT reset the IDENTITY column even though there are no records.

Let us look at the same behavior with the TRUNCATE statement.

You can see that with the TRUNCATE statement, IDENTITY column is reset.

SQL INSERT

Команда INSERT добавляет строки в таблицу или представление основной таблицы.

Синтаксис команды Sql INSERT

Синтаксис команды Insert

Основные ключевые слова и параметры команды INSERT

  • schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя
  • table view — имя таблицы, в которую строки должны быть вставлены; если указано представление, то строки вставляются в основную таблицу представления
  • subquery_1 — подзапрос, который сервер обрабатывает тем же самым способом как представление
  • column — столбец таблицы или представления, в который для каждой вставленной строки вводится значение из фразы VALUES или подзапроса; если один из столбцов таблицы опускается из этого списка, значением столбца для вставленной строки является значение по умолчанию столбца, определенное при создании таблицы. Если полностью опускается список столбца, предложение VALUES или запрос должен определить значения для всех столбцов в таблице
  • VALUES — определяет строку значений, которые будут вставлены в таблицу или представление; значение должно быть определено в предложении VALUES для каждого столбца в списке столбцов
  • subquery_2 — подзапрос, который возвращает строки, вставляемые в таблицу; выборочный список этого подзапроса должен иметь такое же количество столбцов, как в списке столбцов утверждения INSERT

Утверждение INSERT с фразой VALUES добавляет одиночную строку к таблице. Эта строка содержит значения, определенные фразой VALUES. Утверждение INSERT с подзапросом вместо фразы VALUES добавляет к таблице все строки, возвращенные подзапросом. Сервер обрабатывает подзапрос и вставляет каждую возвращенную строку в таблицу. Если подзапрос не выбирает никакие строки, сервер не вставляет никакие строки в таблицу.Подзапрос может обратиться к любой таблице или представлению, включая целевую таблицу утверждения INSERT. Сервер назначает значения полям в новых строках, основанных на внутренней позиции столбцов в таблице и порядке значений фразы VALUES или в списке выбора запроса. Если какие-либо столбцы пропущены в списке столбцов, сервер назначает им значения по умолчанию, определенные при создании таблицы. Если любой из этих столбцов имеет NOT NULL ограничение то сервер возвращает ошибку, указывающую, что ограничение было нарушено и отменяет утверждение INSERT. При выдаче утверждения INSERT включается любой INSERT — триггер, определенный на таблице.

INSERT INTO

INSERT INTO Пример 1

INSERT INTO Пример 2 Нижеприведенная команда копирует данные сотрудников фирмы, комисионные которых превышают 25% от дохода в таблицу bonus:

INSERT INTO Пример 3 Если нужно вставить NULL-значение, необходимо указать его как обычное значение следующим образом:

INSERT INTO Пример 4 Команду INSERT можно применить для того, чтобы извлечь значения из одной таблицы и разместить их в другой, воспользовавшись для этого запросом. Для этого достаточно заменить предложение VALUES на соответствующий запрос:

Introduction to the SQL TRUNCATE TABLE statement

To delete all data from a table, you use the statement without a  clause. For a big table that has few million rows, the statement is slow and not efficient.

To delete all rows from a big table fast, you use the following statement:


In this syntax, you specify the that you want to delete data after the clause.

Some database systems such as MySQL and  PostgreSQL allow you to skip the keyword so the statement is as simple as follows:

When you issue the statement, the database system deletes all rows from the table by deallocating the data pages allocated by the table. By doing this, the RDBMS can reduce the resources for logging and the number of locks that need to acquire.

To truncate multiple tables at a time, you can use a list of comma-separated table names after the clause as follows:

Not all database systems support this form of the statement. If you are using the one that does not, you must issue multiple statements to truncate multiple tables.

Синтаксис

Синтаксис для оператора TRUNCATE TABLE в PostgreSQL:

TRUNCATE table_name ;

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

TABLE
Необязательный. Оператор truncate будет вести себя одинаково независимо от того, указали ли вы TABLE или нет.
ONLY
Необязательный. Если указано, только table_name будет усечено. Если не указано, table_name и все его дочерние таблицы будут усечены.
schema_name
Имя схемы, к которой принадлежит таблица.
table_name
Таблица, которую вы хотите усечь.
*
Необязательный. Если указано, table_name и все его дочерние таблицы будут усечены.
RESTART IDENTITY
Необязательный. Если указано, все последовательности в усеченных таблицах будут сброшены.
CONTINUE IDENTITY
Необязательный. Если указано, все последовательности в усеченных таблицах будут оставлены и не будут сброшены. Это поведение по умолчанию.
CASCADE
Необязательный. Если указано, все таблицы, имеющие ссылку внешнего ключа на table_name, также будут усечены.
RESTRICT
Необязательный. Если указано, все таблицы, имеющие ссылку внешнего ключа на table_name, не будут усечены, если они не были перечислены в операторе TRUNCATE TABLE. Это поведение по умолчанию.

Notes

You must have the TRUNCATE privilege on a table to truncate it.

TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table. When RESTART IDENTITY is specified, any sequences that are to be restarted are likewise locked exclusively. If concurrent access to a table is required, then the DELETE command should be used instead.

TRUNCATE cannot be used on a table that has foreign-key references from other tables, unless all such tables are also truncated in the same command. Checking validity in such cases would require table scans, and the whole point is not to do one. The CASCADE option can be used to automatically include all dependent tables — but be very careful when using this option, or else you might lose data you did not intend to!

TRUNCATE will not fire any ON DELETE triggers that might exist for the tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers are defined for any of the tables, then all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation is performed and any sequences are reset. The triggers will fire in the order that the tables are to be processed (first those listed in the command, and then any that were added due to cascading).

TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. See Section 13.5 for more details.

TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Практическое упражнение № 2:

На основании таблицы contacts и customers, вставить в таблицу contacts всех customers, которые проживают в штате Nevada.

Oracle PL/SQL

—создаем contacts CREATE TABLE contacts ( contact_id number(10) not null, last_name varchar2(50) not null, first_name varchar2(50) not null, address varchar2(50), city varchar2(50), state varchar2(20), zip_code varchar2(10), CONSTRAINT contacts_pk PRIMARY KEY (contact_id) ); —создаем customers CREATE TABLE customers ( customer_id number(10) not null, last_name varchar2(50) not null, first_name varchar2(50) not null, address varchar2(50), city varchar2(50), state varchar2(20), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

—создаем contacts

CREATETABLEcontacts (contact_idnumber(10)notnull,

last_namevarchar2(50)notnull,

first_namevarchar2(50)notnull,

addressvarchar2(50),

cityvarchar2(50),

statevarchar2(20),

zip_codevarchar2(10),

CONSTRAINTcontacts_pkPRIMARYKEY(contact_id) ); —создаем customers

CREATETABLEcustomers (customer_idnumber(10)notnull,

last_namevarchar2(50)notnull,

first_namevarchar2(50)notnull,

addressvarchar2(50),

cityvarchar2(50),

statevarchar2(20),

zip_codevarchar2(10),

CONSTRAINTcustomers_pkPRIMARYKEY(customer_id) );

Решение для практического упражнения № 2:

Следующий оператор Oracle INSERT вставит запись в таблицу customers:


Oracle PL/SQL

INSERT INTO contacts (contact_id, last_name, first_name, address, city, state, zip_code) SELECT customer_id, last_name, first_name, address, city, state, zip_code FROM customers WHERE state = ‘Nevada’;

1 2 3 4 5 6 7 8 9 10 11

INSERTINTOcontacts (contact_id,last_name,first_name,address,city,state,zip_code)

SELECTcustomer_id,

last_name,

first_name,

address,

city,

state,

zip_code

FROMcustomers

WHEREstate=’Nevada’;

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

Oracle PL/SQL

INSERT INTO contacts SELECT * FROM customers WHERE state = ‘Nevada’;

1 2 3 4

INSERTINTOcontacts

SELECT*

FROMcustomers

WHEREstate=’Nevada’;

Setting up the Environment

Let us create a sample table and populate a few records to demonstrate different aspects of TRUNCATE by using the following T-SQL code.

1 2 3 4 5 6 7 8 9

CREATETABLESampleTable

(IDINTIDENTITYPRIMARYKEYCLUSTERED,

NAMECHAR(200),

ADDRESSCHAR(1000))

GO

INSERTINTOdbo.SampleTable

(Name,Address)

VALUES

(‘AAA’,’CCCC’)

From the above T-SQL code, a sample table called SampleTable is created and an adequate number of sample records were populated.

To truncate the table, following T-SQL command can be executed.

1 TRUNCATETABLESampleTable

It is important to note that there is no WHERE clause in the TRUNCATE statement. TRUNCATE option is available in SQL Server all editions, Azure SQL Server and Azure data warehouse instances.

Remove all data from a table that has foreign key references

In practice, the table you want to truncate often has the foreign key references from other tables that are not listed in the  statement.

By default, the  statement does not remove any data from the table that has foreign key references.

To remove data from a table and other tables that have foreign key reference the table, you use option in the statement as follows :

The following example deletes data from the table and other tables that reference the table via foreign key constraints:

The option should be used with further consideration or you may potentially delete data from tables that you did not want.

By default, the statement uses the option which prevents you from truncating the table that has foreign key constraint references.

TRUNCATE TABLE Syntax

The syntax for the SQL TRUNCATE statement is slightly different in each database.

The basic TRUNCATE statement looks the same:

  • schema_name: This is optional. If your table belongs to a different schema, you specify it here.
  • table_name: The name of the table to remove the data from.

However, there are several options unique to each database.

Database SQL
Oracle
SQL Server
MySQL
PostgreSQL

So what do all of these parameters mean?

Oracle:

  • PRESERVE MATERIALIZED VIEW LOG: Optional, and default. If you specify this, then the materialized view log will be preserved when you truncate the table.
  • PURGE MATERIALIZED VIEW LOG: Optional. If you specify this, then the materialized view log will be purged when you truncate the table.
  • DROP STORAGE: Optional, and default. If you specify this, all storage that is allocated to the truncated rows will be deallocated (except the space allocated by MINEXTENTS).
  • REUSE STORAGE: If you specify this, all storage that is allocated to the truncated rows will remain allocated to the table.

SQL Server:

WITH PARTITIONS: Specifies the partitions to truncate or from which all rows are removed.

PostgreSQL:

  • RESTART IDENTITY: Automatically restarts the sequences owned by columns of this table.
  • CONTINUE IDENTITY: Does not change the values of sequences. This is the default.
  • CASCADE: Automatically truncate all tables that have foreign key references to this table.
  • RESTRICT: Does not truncate any tables that have foreign key references to this table. This is the default.

Comparison of Transaction Log File

Let us compare the usage of transaction log file during the TRUNCATE and DELETE statements.

Following screenshot shows the log size after the inserts are done to the sample table which was created before.

From the above screenshot, it can be seen that transaction log usage percentage is 0.3%.

Following is the log file usage after the DELETE statement is completed.

As you can see from the above screenshot, transaction log usage has increased to 0.7% when those records are deleted via DELETE statement.

Following is the screenshot for the log usage percentage when the data is deleted from the TRUNCATE statement.

You will see from the above screenshot that, log usage has not grown at all. This means that during the TRUNCATE will be minimally logged in the transaction log than the DELETE statement.

Since TRUNCATE statement minimally logged in the Transaction Log, TRUNCATE is faster than DELETE statement. This is due to the fact that TRUNCATE is equivalent to dropping the table and recreating it. Since TRUNCATE will not consume large transaction log resources, transaction log backup file will be less in size and will improve the performances of recovery options such as mirroring and log shipping.


С этим читают