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.
Our counter starts with identity seed of 5 and has increment of 5 also. As a result, we get
Let's delete rows with DELETE statement, and insert these in the table once more thereafter.
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:
By the way, SQL Standard presumes somewhat different behaviour. The standard syntax is the following
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
truncate table 'Truncate_test' because it is being referenced by a
FOREIGN KEY constraint.
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.