/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

How to manually generate "Pseudo-identity" values
2010-10-03
*****************************************************/

/* Method 1: Counters table */
create table dbo.Counters
(
	EntityName sysname not null,
	Value bigint not null
		constraint DEF_Counters_Value
		default 0,
		
	constraint PK_Counters
	primary key clustered(EntityName)
)
go

insert into dbo.Counters(EntityName)
	select 'ORDERS' union all
	select 'ORDERITEMS'
go

/* SP returns:
0 - success
-1 - invalid entity name
*/
create proc dbo.GetCounterValue
(
	@EntityName sysname
	,@PacketSize int = 1
	,@Value bigint output
)
as
begin
	declare
		@InTran bit
		,@ErrorCode int
		
	select @InTran =	
		CASE
			WHEN @@TRANCOUNT > 0
			THEN 1
			ELSE 0
		END
		
	if @InTran = 1 begin tran
		update dbo.Counters
		set
			@Value = Value = Value + @PacketSize
		where
			EntityName = @EntityName
		
		if @@ROWCOUNT = 0
		begin	-- invalid EntityId
			select @ErrorCode = -1
			select @Value = null
		end
		else
			select @ErrorCode = 0
	if @InTran = 1 commit
	
	select @Value = @Value - @PacketSize + 1
	return @ErrorCode
end
go

declare
	@ResCode int
	,@Value bigint
	
exec @ResCode = dbo.GetCounterValue 
	@EntityName = 'ORDERS'
	,@PacketSize = 10
	,@Value = @Value output
	
select @ResCode as [Error Code], @Value as [Value]
select * from dbo.Counters
go

/* METHOD 2 - identity from the dummy table*/
create table dbo.Dummy
(
	Value bigint not null identity(1,1),
	DummyField bit not null
		constraint DEF_Dummy_DummyField
		default 0
)
go

/* Single row identity value */
declare
	@ID bigint

begin tran
	insert into dbo.Dummy default values
	select @ID = SCOPE_IDENTITY()
rollback
select @ID as [Value]
go
	
/* Assuming you need to get new values for 10 new rows
It could be done in the following code: */
declare
	@IDList table
	(
		ID bigint not null
	)

begin tran
	;with CTE(RowNum)
	as
	(
		select 1
		
		union all
		
		select RowNum + 1
		from CTE		
		where RowNum < 10
	)
	insert into dbo.Dummy(DummyField)
	output inserted.Value into @IDList(ID)
		select 0
		from CTE
rollback
select * from @IDList
go
