Oracle sequences are not related to any given table directly. Therefore, Oracle does not enforce any rules when working with sequences. However, Microsoft® SQL Server™ is strict when using identity columns: the DEFAULT keyword cannot be used when working with identity columns. Additionally, values in identity columns cannot be updated.
By default, data cannot be inserted directly into an identity column. The identity column generates a unique, sequential number automatically for each new row inserted in the table. This default can be overridden using the following SET statement:
SET IDENTITY_INSERT table_name ON
With IDENTITY_INSERT set to ON, the user is allowed to insert any value into a new row with an identity column. To prevent the entry of duplicate numbers, a unique index must be created against the column. The purpose of this statement is to allow a user to re-create a value for a row that has been deleted accidentally. The @@IDENTITY global variable can be used to obtain the last identity value.
The TRUNCATE TABLE statement resets an identity column to its original SEED value. This can affect any type of synchronization that exists with Oracle tables using sequences because sequences are not reset following the TRUNCATE TABLE command.
If you do not want to reset the identity value for a column, use the DELETE statement without a WHERE clause instead of the TRUNCATE TABLE statement.
You can only perform inserts or deletes when working with timestamp columns. If you attempt to update a timestamp column, you receive this error message:
Msg 272, Level 16, State 1 Can’t update a TIMESTAMP column.