Say I have a table with an auto-incremented primary key. In Oracle, the auto-increment is done through a sequence, and in SQL Server, this is pre-defined in the table creation using the IDENTITY keyword.
I manually insert a record into this table and include the primary key value. This causes problems in Oracle because the sequence is still referencing the last value prior to this insert.
What would the behavior be in SQL Server?
From the fine manual:
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value. The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
By default, the Identity starts with 1 and increments with 1. However, it is not quaranteed to be a sequence. For example, if an insert fails, you may "lose" a number.
For SQL Server, it doesn't matter. You should view Identity as a helper function. In the end, it is the Primary Key (or Unique constraint) that determines whether is row is acceptable.
So if you manually created a row with a "lost" number or a number the Identity will never reach (such as 0 or -1 if you use the default starting number and increment), there will not be an issue at all.
If you inserted the next Identity, then the Primary Key will cause the next regular insert to fail. After that, you will not have any problem anymore, because the Identity will move on.
