Statements that Manipulate Identity Columns

Identity columns can be created, changed, and dropped: 

• CREATE TABLE (within the column definition)
• ALTER TABLE (Column)
  • ADD COLUMN (within the column definition)
  • MODIFY COLUMN (within the column definition)
  • ALTER COLUMN IDENTITY using SET IDENTITY or DROP IDENTITY

In the following statements, the dynamically created values of an identity column are explicitly or implicitly used:

• INSERT: DEFAULT as 'value' for a column (INSERT INTO t(i, j, k) VALUES (1, DEFAULT, 5) or implicitly in case of a column selection where the identity column is not included
• IMPORT: implicitly in case of a column selection where the identity column is not included.
• UPDATE: DEFAULT as 'value' for a column (SET i=DEFAULT)
• MERGE: DEFAULT as 'value' for column within the INSERT and UPDATE operation or implicitly in the INSERT operation in case of a column selection where the identity column is not included.
• ADD COLUMN: if the column being added is an identity column