Database as ringbuffer

Hello
I'm using a MS SQL Server 2000 database to log data from a datalogger. What
is the best way to make the database work as a ringbuffer, so when it has
stored say 5000 records it will start overwriting the oldest ones? (FIFO)
I need this ringbuffer functionality to maintain a static database size,
while still being able to log new data.
Are stored procedures the way to go, or are there any settings or tools
available in SQL Server 2000 to make this easy'
Regards,
AllanWhy not base your data retention on a time period rather than a fixed
number of rows:
CREATE TABLE foo (dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
etc)
As a scheduled process you can delete the old data for whatever time
period is required:
DELETE FROM foo
WHERE dt < DATEADD(HOUR,-24,CURRENT_TIMESTAMP)
If you really wanted to constrain the table to 5000 rows then you could
try this:
CREATE TABLE foo (seq INTEGER NOT NULL PRIMARY KEY, ...)
insert each new row:
INSERT INTO foo (seq, ...)
SELECT COALESCE(MAX(seq)+1,1), ...
FROM foo
remove old data:
DELETE FROM foo
WHERE seq <=
(SELECT MAX(seq)-5000
FROM foo)
but don't assume that this would guarantee a static database size.
Database size will still be determined by the recovery model and log
backup policy whatever method you use. Is database growth really that
much of a problem at 5000 rows? Seems like this should be well within
the spec of any system that meets the minimum hardware requirements for
SQL Server.
David Portas
SQL Server MVP
--|||Thank you very much for a very good and helpful reply, David!
Yes, data size is important in this specific case. However, the 5,000 was
just an example, it could be 10,000 or 100,000 for that matter. The thing
is, I only have limited resources available, since this is for a prototype
of an embedded system. I need to make sure that the database does not grow
beyond a certain point.
(SQL Server won't be used for the final embedded system of course - but for
timing reasons I have to go with what I know for now)
Again, thank you!
Allan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112346631.506210.317100@.g14g2000cwa.googlegroups.com...
> Why not base your data retention on a time period rather than a fixed
> number of rows:
> CREATE TABLE foo (dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
> etc)
> As a scheduled process you can delete the old data for whatever time
> period is required:
> DELETE FROM foo
> WHERE dt < DATEADD(HOUR,-24,CURRENT_TIMESTAMP)
> If you really wanted to constrain the table to 5000 rows then you could
> try this:
> CREATE TABLE foo (seq INTEGER NOT NULL PRIMARY KEY, ...)
> insert each new row:
> INSERT INTO foo (seq, ...)
> SELECT COALESCE(MAX(seq)+1,1), ...
> FROM foo
> remove old data:
> DELETE FROM foo
> WHERE seq <=
> (SELECT MAX(seq)-5000
> FROM foo)
> but don't assume that this would guarantee a static database size.
> Database size will still be determined by the recovery model and log
> backup policy whatever method you use. Is database growth really that
> much of a problem at 5000 rows? Seems like this should be well within
> the spec of any system that meets the minimum hardware requirements for
> SQL Server.
> --
> David Portas
> SQL Server MVP
> --
>|||> SQL Server won't be used for the final embedded system of course
Why "of course"? I'm not an expert on embedded systems but SQL Server
CE is intended for that market:
http://www.microsoft.com/sql/ce/default.asp
David Portas
SQL Server MVP
--|||Win CE wouldn't be a bad option. However, it is a decision that isn't in my
hands, but I will be sure to mention it as an alternative in my report. I'm
not sure what the system specs will be exactly.
I just need to get this prototype running, and we will see what happends.
:-)
Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112354491.449183.150370@.f14g2000cwb.googlegroups.com...
> Why "of course"? I'm not an expert on embedded systems but SQL Server
> CE is intended for that market:
> http://www.microsoft.com/sql/ce/default.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Allan,
You could use an after insert trigger similar to that provided in the
following example:
create table table1 (col1 numeric(10,0) not null identity, col2 char(1))
create trigger table1_mytrigger
on table1 after insert as
begin
delete from table1
where table1.col1 in (select (col1 - 2) from inserted)
end
insert into table1 (col2) values ('A')
insert into table1 (col2) values ('B')
insert into table1 (col2) values ('C')
insert into table1 (col2) values ('D')
insert into table1 (col2) values ('E')
insert into table1 (col2) values ('E')
insert into table1 (col2) values ('F')
select * from table1
The trigger allows only 2 rows to exist in the table at anytime as long as
the identity column doesn't get out of wack. Of course you could to do any
type of select statement in the subquery to return a single value and then
change the where clause to use a less than sign on your key column.
HTH,
James K.
"Allan Nielsen" <ace-allan@.rocketmail.com> wrote in message
news:O4HASPpNFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Hello
> I'm using a MS SQL Server 2000 database to log data from a datalogger.
> What
> is the best way to make the database work as a ringbuffer, so when it has
> stored say 5000 records it will start overwriting the oldest ones? (FIFO)
> I need this ringbuffer functionality to maintain a static database size,
> while still being able to log new data.
> Are stored procedures the way to go, or are there any settings or tools
> available in SQL Server 2000 to make this easy'
> Regards,
> Allan
>|||On Fri, 1 Apr 2005 10:29:07 -0500, -=JLK=- wrote:
(snip)
>The trigger allows only 2 rows to exist in the table at anytime as long as
>the identity column doesn't get out of wack.
Hi James,
Neat trick - but not very reliable. An IDENTITY column should not be
used like this, as there is no guarantee that the identity values will
always be contiguous. Indeed - if ever an insert is rolled back, there
WILL be gaps in the range of identity values, and then your code will
fail.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Kind of crude, but preallocate a table with a row number between 0 and
4999, then set up a one-row,one column table with the "current pointer"
in it. Use a trigger on RingBuffer to update that value to MOD(pointer
+1, 5000).
UPDATE RingBuffer
SET ...
WHERE RingBuffer.row_nbr
= (SELECT current_ptr FROM Pointer);
Add some CHECK() constraints to both tables to keep them the right
size.|||Thank you all for the input, it is a great help. I really appreciate it.|||Agreed, for a production database it is not reliable, but for what he was
doing for development probably sufficient to get an approximate result
quickly and without jumping through hoops. Thanks for pointing out the
deficiency, as I should have done :-)
James K.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:j6br419b315b7tdp3526kdua5bqsb2cr8k@.
4ax.com...
> On Fri, 1 Apr 2005 10:29:07 -0500, -=JLK=- wrote:
> (snip)
> Hi James,
> Neat trick - but not very reliable. An IDENTITY column should not be
> used like this, as there is no guarantee that the identity values will
> always be contiguous. Indeed - if ever an insert is rolled back, there
> WILL be gaps in the range of identity values, and then your code will
> fail.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)