Auto Incremented field in SQL Server 2008

The likeliest reason is that the newly generated autoincrement value is outside the scope. For example, you execute the insert statement that generates the new autoincrement value in your code first (as a separate statement), after that you execute the statement above.

The scope_identity call must be in the same scope where the autoincremented value was generated. This means that it's in the same stored procedure, function or batch.

But furthermore, in my opinion, you should never use the autoincrement value as part of some other data. Autoincrements are used only for creating unique, nondescriptive values so they should never have any other meaning. Concatenating identity value, converting it to string etc. isn't the correct way to use an identity value. You can use scope_identity for example to add a foreign key column value in a child table but also in that case the value should be unmodified.

You can do nothing in C# and let a computed field do the job for you ,
you can define code filed as a computed field that its formula is like this :
(substring(CONVERT([varchar](max),(10000)+[id],0),(2),(4)))
and then you can use it whenever you want. also you can ignore it during the insertions.

remember that this formula is for IDs less than 10000 if you predict it may go further than 10000 please change it to a proper one for example for 1000000 we have this one :

(substring(CONVERT([varchar](max),(1000000)+[id],0),(2),(6)))


Hope it helps.

http://howtoideas.net/how-to-create-auto-increment-column-in-a-sql-server-table[^]


or

create trigger update_increment for update as
if not update(incrementID)
  UPDATE tb_users SET incrementID = incrementID + 1
    from inserted WHERE tb_users.id = inserted.id