loading..
Русский    English
15:30
go

DELETE statement page 2

TRUNCATE TABLE statement

As mentioned above, upon executing this statement, the counter (IDENTITY) will be reset in its initial value. Let's check up this contention in MS SQL Server. First we shall create the table with identity column, and we shall add three rows to this table.

  1. CREATE TABLE Truncate_test (id INT IDENTITY(5,5) PRIMARY KEY, val INT);
  2. GO
  3. INSERT INTO Truncate_test(val) 
  4. VALUES (1),(2),(3);
  5. SELECT * FROM Truncate_test;
  6. GO

Our counter starts with identity seed of 5 and has increment of 5 also. As a result, we get

id   val
5    1
10    2
15    3

Let's delete rows with DELETE statement, and insert these in the table once more thereafter.

  1. DELETE FROM Truncate_test; 
  2. GO
  3. INSERT INTO Truncate_test(val) 
  4. VALUES (1), (2), (3);
  5. SELECT * FROM Truncate_test;
  6. GO

id   val
20   1
25    2
30    3

As the result indicates, the counter has not been reset, and identity values proceeded to increment from 15 and so on, whereas TRUNCATE statement gives:

  1. TRUNCATE TABLE Truncate_test; 
  2. GO
  3. INSERT INTO Truncate_test(val) 
  4. VALUES (1),(2),(3);
  5. SELECT * FROM Truncate_test;
  6. GO

id   val
5    1
10   2
15    3

By the way, SQL Standard presumes somewhat different behaviour. The standard syntax is the following

  1. TRUNCATE TABLE < TABLE name > [{CONTINUE IDENTITY} | {RESTART IDENTITY}]
i.e. the counter can be reset (RESTART IDENTITY option) or proceeded (CONTINUE IDENTITY option).  It should be mentioned that default value is just CONTINUE IDENTITY - the behaviour identical to use of DELETE statement (without WHERE clause).

TRUNCATE TABLE statement can't be used if the table is being referenced by a foreign key. This standard behaviour is inherent to SQL Server. If we'll create the following referencing table even without data at all

  1. CREATE TABLE Trun_Ref(id INT REFERENCES Truncate_test);
TRUNCATE TABLE statement being considered above leads us to error:

Cannot truncate table 'Truncate_test' because it is being referenced by a FOREIGN KEY constraint.


Let's check available SQL dialects for corresponding to Standard.
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.