Following error message would be generated if more than one identity column is tried to define in a table.
Multiple identity columns specified for table ‘%’. Only one identity column per table is allowed
SQL Server does not allow to have more than one identity column in a single table. Consider following design for generating error message.
CREATE TABLE identTest (ID1 SMALLINT IDENTITY(1,1), ID2 SMALLINT IDENTITY(1,1)) GO
There would be hardly any scenario when such a design is required. However such functionality may be achieved by a couple of work around
- Use trigger to insert values in second column
- Configure second column as computed column and reflect values from identity column