When delete a row from database, why not "IsIdentity" decrement the row id?
I have 3 records in my Products Table, with ProducID 1,2,3 respectively. When I delete the record number 3 and after that insert a new record, the ProdcutID will 4. My question is why not the index 3 given to that row?
Because an artifcial (aka "surrogate") primary key value - and that's what an autoincrement column is - has no meaning whatsoever.
So it does not matter whether the PK value for a new row is 4, 40, 400, 40000 or 365623563
If your code relies on incrementing, gapless values in the primary key you have to rethink your code.
There is also a technical reason why this happens: in order for an autoincrement (or a sequence in most databases) to be fast and correct in an environment with concurrent transaction there have to be certain limitations to what sequences/autoincremnts can do. One of them is that they never decrement (or to be more precise: never change direction as there can be decrementing sequences) and they are not part of the transaction control. So once a value has been acquired it's "gone" from the underlying generator.
Because identity number is stored in table or database (I'm not sure now) and is not "reverted", but only incremented for every insert operation.
Because 3 was allocated to a record. It will evermore hold this allocation, even in death. Think of it like being the Twilight Saga, only with IDs.
Seriously though, a lot of enterprise applications no longer hard delete rows anyway, preferring a soft delete for historical purposes.
If you haven't got a fully constrained database (bad practice) then you could end up with this rolled back ID inadvertantly inheriting the old #3's children etc. Also it would make things like auditing and transaction history confusing.
What would happen if one transaction deleted a row, while another inserted? In an auto increment world, there's no locking implied, in the id reuse world, things get messy quickly.
When the IDENTITY property is used with CREATE TABLE, Microsoft® SQL Server™ uses the NOT FOR REPLICATION option of CREATE TABLE to override the automatic incrementing of an identity column. Usually, SQL Server assigns each new row inserted in a table a value that is some increment greater than the previous highest value. However, if the new rows are replicated from another data source, the identity values must remain exactly as they were at the data source.
Here is a reference http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx