CODEWITHSUNDEEP
c#sqlsql-serverdatabaselimit
lawir
lawir

Reputation: 55

how to limit number of line in my sql database and then overwrite a line when the limit is reached?

I'm using an SQL database with C# to store events. My table name is TabOfEvents; it has the code of the event and the date and time of this event.

CREATE TABLE [dbo].[TabOfEvents] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [cta] NCHAR (10) NOT NULL,
    [code] NVARCHAR (MAX) NOT NULL,
    [date] DATETIME2 (7) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
)

Each time an event happens I save it in my table, ordered by date (from the newest to oldest).

I want to limit the number of lines in my database say 100.000 lines, (or by the size of my database say 300Mo for example, but I'm not sure about this one if it's possible) and when the limit number is reached, I want to overwrite the oldest events and replace them with the new ones.

How can I do that with C#?

Upvotes: 2

Views: 774

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112682

You could use a sequence with the CYCLE option used as pointer in the table used as ring buffer. This creates a sequence object in the database:

CREATE SEQUENCE BufferPtr  
    START WITH 1
    MINVALUE 1
    MAXVALUE 100000
    CYCLE;

Create the table differently to allow empty entries and to allow entering Ids

CREATE TABLE dbo.TabOfEvents (
    [Id] INT NOT NULL,
    [cta] NCHAR (10) NULL,
    [code] NVARCHAR (MAX) NULL,
    [date] DATETIME2 (7) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
)

Then fill the table with 100000 empty records and Ids 1 .. 100000. We do this to know the number of records in advance and to save ourselves a query. I.e., we do not have to query the number of records with SELECT COUNT (*) FROM TabOfEvents to know if we must make an insert or an update. The Sequence is used in the UPDATE command itself to determine which record we update. A new sequence number is generated each time and after the limit 100,000 is reached the sequence starts over at 1.

-- This creates 100000 empty records with an Id. The code is quite tricky and I won't explain
-- the details here. If you prefer, create a stored procedure with a FOR-loop or do it in an
-- external application (e.g. C# Console app). Performance is not important, since we are
-- doing it only once.

;WITH e1(n) AS
(
    SELECT 1 FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0)) t(n)
)
INSERT INTO dbo.TabOfEvents (Id)
SELECT ROW_NUMBER() OVER (ORDER BY n10.n) AS n
FROM
e1 n10
CROSS JOIN e1 n100
CROSS JOIN e1 n1000
CROSS JOIN e1 n10000
CROSS JOIN e1 n100000

Now the table and the sequence are set up and you can update the records with

UPDATE TabOfEvents
SET cta = 'test', code = 'xxx', [date] = SYSDATETIME()
FROM
    TabOfEvents
    INNER JOIN
    (SELECT
        NEXT VALUE FOR BufferPtr AS Ptr
        FROM (VALUES (1)) t(n)
    ) a
    ON TabOfEvents.Id = a.Ptr;

You can see a test here (http://sqlfiddle.com/#!6/2679e/5/2) using only 4 records. Click Run SQL repeatedly and you will see how events are added in a cycle.

Note: My first attempt was to use UPDATE TabOfEvents SET cta = 'test', code = 'xxx', [date] = SYSDATETIME() WHERE Id = NEXT VALUE FOR BufferPtr, but SQL-Server refuses to query a sequence in the WHERE clause. Can my UPDATE statement be simplified?

Upvotes: 0

user9023555
user9023555

Reputation:

Don't do it this way. You can do this instead:

  • Put a retention policy and/or an archive policy for your data and your events. So for example, you can archive or delete events older than x months or days. You can archive them in a different tables/ or a different database. Or,

  • You can put rules on your application's business layer for how much events are allowed. This way you can control and limit the events and your data the way you like.

So for the second option, the max events count can be stored in something like config table in your database or in config file (in app.config, or web.config) something. If you search online you can find how to read and write in config file.

Then in your application before doing the insert, get the count of the events from the database like:

SELECT COUNT(*) FROM events;

Then compare this result with the value from the web.config. If the value >= the max value reject the insert with an error message otherwise do the insert.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can do this with a trigger. However, it probably is not necessary.

Deleting rows in a table does not automatically reclaim space. Database tables do not work the same way the files do. Deleting rows can also be a (relatively) time-consuming operation, because of the logging, locking, and index re-organization.

From a performance perspective, a well-designed database should have no trouble handling millions of rows of data -- for many common types of queries.

If you do want to limit the size of the table, I would recommend using a partitioning scheme. Then use a scheduled job to drop old partitions. Dropping partitions is much more efficient that dropping individual rows. And, recovering space from dropped partitions is trivial.

Upvotes: 0

Related Questions