None of the two methods will yield a continuous numbering. Only a monotonous increasing one. For internal IDs, that's fine. If you need financially acceptable continuous numbering (let's say for invoices), you need an other approach:
0) Let's say you have an invoices table
1) Use an identity field (or a sequence) for technical ID (TID)
2) Have a different field for the continuous ID. (CID)
3) Create a table that has a value in it, and put the starting number form the continuous ID in that cell
4) When reaching the state where you are sure that the entity you are dealing with won't be deleted (let's say you have finished completing the invoice) start a transaction that can do the magic. See the SP below.
Here is the demo database:
USE [_playground_]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FinalizeInvoice]
@TID int
AS
BEGIN
SET NOCOUNT ON;
SET LOCK_TIMEOUT 100;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION
BEGIN TRY
update dbo.CID_sequence set V=V+1 where id=0;
update dbo.Invoices
set CID = (select V from dbo.CID_sequence where id=0)
where TID = @TID
COMMIT
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
ROLLBACK
END CATCH;
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CID_sequence](
[V] [int] NOT NULL,
[id] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Invoices](
[TID] [int] NOT NULL,
[CID] [int] NULL,
[customer] [nvarchar](10) NOT NULL,
CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
(
[TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
If you run following script, you will see that the last sequence is rolled back:
insert into dbo.Invoices(tid, customer) values (1, 'joe');
insert into dbo.Invoices(tid, customer) values (2, 'bill');
insert into dbo.CID_sequence(id, V) values(0,1);
exec dbo.FinalizeInvoice 1;
begin transaction
exec dbo.FinalizeInvoice 2;
select * from dbo.Invoices;
select * from dbo.CID_sequence
rollback
select * from dbo.Invoices;
select * from dbo.CID_sequence
delete from dbo.Invoices;
delete from dbo.CID_sequence
If you decomment the wait statement in the SP and you put the second exec in a separate query window and you start the script than immediately the second exec, you will see, that the later one will fail instantaneously. Actually after 100ms as the SP is written like this. You can decide how long you are willing to wait. As the two updates should terminate quickly, waiting that long is more than enough. Still, you need to be prepared in the application to handle the situation when the concurrent CID generation can't get the lock.