CODEWITHSUNDEEP
sqlsql-server
Denis
Denis

Reputation: 12087

Implementing a stack (LIFO) in SQL Server using stored procs/functions

I am looking at writing a stack (LIFO) implementation in SQL Server. I would like to have a function that pushes a string onto the stack, a function that would pop the string off the stack and a function that gets the current value on the stack. So far am looking at using IDENTITY to keep track of stacks but does anyone have a better implementation or some hints on how to implement this in sql server?

Upvotes: 2

Views: 2956

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103525

I would use a table like this:

create table Stack(id int not null identity primary key, value nvarchar(max))

Push:

insert into Stack (value) values ('pushed value')

Peek:

select top 1 value from Stack order by id desc

Pop:

declare @id int, @value nvarchar(max)
select top 1 @id=id, @value=value from Stack order by id desc
if @id is null begin
  -- handle stack-underflow here
end
delete from Stack where id=@id
select @value

It's not strictly a stack internally, because SQL Server has to do an index scan in order to get the most recently added item, instead of just using a stack pointer.

However, from my testing, it appears that SQL Server is smart and does the scan "backwards", and therefore only hits one row anyway. Therefore, practically speaking all operations are O(1).

Upvotes: 3

Related Questions