CODEWITHSUNDEEP
sqlsql-servert-sql
IVR Avenger
IVR Avenger

Reputation: 15494

Auto decrement T-SQL ID when deleting records?

In one of our apps, we read in data from a file and expand it into several tables. If any part of the file is corrupt, we halt the read, and delete whatever data got inserted.

The issue, here, is that we have an auto-increment ID on one of the import tables, and when we remove a problem file, the ID continues from its post-import value, rather than its pre-import value.

In other words...

We've "lost" the range of 100 records. Is there an "auto decrement" equivalent to go with the auto increment?

Upvotes: 0

Views: 4336

Answers (4)

Bilbo
Bilbo

Reputation: 380

In my experience, a best practice for this portion of an ETL (Extract Transform Load) process is to perform the bulk load in more than one step:

  1. Load data from file(s) into empty "loading" or "staging" table(s) which exist just for this purpose. This detects file-level corruption.
  2. Check data for referential integrity and other validations. This detects data-level errors.
  3. Insert only valid data into the "real" tables. This avoids unnecessary deletes and avoids wasting auto increment values.
  4. Log or report data which failed the import checks.
  5. Immediately before the next run of this process, truncate the "loading" tables.

Upvotes: 0

Darren
Darren

Reputation: 70748

You can reseed the AutoIncrement ID by doing:

DBCC CHECKIDENT
(
  tablename

  [, [NORESEED | RESEED [, newreseedvalue]]]
)

However I would not recommend this as a best practice. Your query should be atomic (It commits and updates the table or Rollsback if it fails (leaving the ID untouched). To implement an atomic query you could use a TRANSACTION.

BEGIN TRY
   BEGIN TRANSACTION @TranName;

     -- Your database logic here

    COMMIT TRANSACTION @TranName;

END TRY

BEGIN CATCH

   ROLLBACK TRAN @TranName;

END CATCH

GO

Sources:

http://msdn.microsoft.com/en-us/library/ms188929.aspx

http://www.techrepublic.com/blog/datacenter/how-do-i-reseed-a-sql-server-identity-column/406

Upvotes: 2

JonH
JonH

Reputation: 33163

Autonumbers shouldn't be that meaningful to you. Their guarantee is they provide uniqueness nothing more. You can still reseed if you are using sql server DBCC CHECKIDENT.

Checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new seed value for the identity column.

From BOL: The following example forces the current identity value in the Employee table in the AdventureWorks database to a value of 30.

USE AdventureWorks;
GO
DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30);
GO

I am not recommending this but just pointing it out. DBCC CHECKIDENT can throw an error if you try to reseed to a value that is already being used, in that case you'd have to have logic if you were relying upon such a task.

I question the thought process of what makes these numbers so important? It sounds like you want one additional field called LineNumber that is incremented or decremented, etc. But even in this case you have to handle the rows that come after the deleted record. So if you have 50 rows and you delete row 25 you have to renumber anything greater then 25:

UPDATE 
       MyTable 
SET LineItemNumber = LineItemNumber - 1 
WHERE 
       LineItemNumber > @LineItemNumberToBeDeleted

Upvotes: 3

Thinking Sites
Thinking Sites

Reputation: 3542

Auto decrementing on delete sounds like a bad idea. If done incorrectly, you can start injecting much bigger bugs into your code. If the IDs are a big deal, try giving it a batch number, and an incremented id for every item in the batch. You could also use guids, though they're not sequential.

Upvotes: 2

Related Questions