RingBuffer in t-sql

RingBuffer in t-sql

/*
-- create utility Number table (just simplifies the initialize below)
if object_id('dbo.Number') is not null
	drop table dbo.Number;
go
create table dbo.Number
(
	N int not null primary key clustered
);
go

insert into dbo.Number(N)
	select top 10000 row_number() over(order by t1.number) as n
	from master..spt_values t1 
	cross join master..spt_values t2
go

if object_id('dbo.RingBuffer') is not null
	drop table dbo.RingBuffer;
go
create table dbo.RingBuffer
(
	Position int not null primary key clustered, 
	Payload varbinary(max) not null, 
	InsertedOn datetime not null
);
go

--initialize an example ring with MaxTableSize = 10
insert into dbo.RingBuffer (Position, Payload, InsertedOn)
	select	-1 * N, 0x, getdate()
	from	dbo.Number
	where	N <= 10
*/


--store payloads
declare @Payload varbinary(max) = cast(convert(varchar, getdate(), 126) as varbinary(max)),
		@MaxTableSize int = 10;

;with oldest (Position, Payload, InsertedOn) as 
(
	select	top(1) Position, Payload, InsertedOn
	from	dbo.RingBuffer
	order
	by		Position asc
)
update oldest
set Position = Position + @MaxTableSize,
	Payload = @Payload,
	InsertedOn = getdate();

-- view the values
select	* 		
from	dbo.RingBuffer
order 
by		Payload desc;