Trigger to change autoincremented value

I have a simple table with tax rates

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TaxRates](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_TaxRates] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

If user d开发者_如何学运维eleted record I want to not change autoincrementer while next insert.

To have more clearance.

Now I have 3 records with id 0,1 and 2. When I delete row with Id 2 and some time later I add next tax rate I want to have records in this table like before 0,1,2. There shouldn't be chance to have a gap like 0,1,2,4,6. It must be trigger.

Could you help with that?

---

You need to accept gaps or don't use IDENTITY

1. id should have no external meaning
2. You can't update IDENTITY values
3. IDENTITY columns will always have gaps
4. In this case you'd update the clustered Pk which will be expensive
5. What about foreign keys? you'd need a CASCADE
6. Contiguous numbers can be generated with ROW_NUMBER() at read time
7. Without IDENTITY (whether you load this table or another) won't be concurrency-safe under load
8. Trying to INSERT into a gap (by an INSTEAD OF trigger) won't be concurrency-safe under load
9. (Edit) History tables may have the deleted values anyway

---

An option, if the identity column has become something passed around in your organization is to duplicate that column into a non-identity column on the same table and you can modify those new id values at will while retaining the actual identity field.

turning identity_insert on and off can allow you to insert identity values.