I have table where on daily basis some records [sic: rows are not records] are inserted and deleted and the table structure like below. <<
The affix “tbl-” is a design flaw called a tibble and we laugh at it. Read Phil Factor's humor pieces on this. Did you know that a table has to have a key? A row is nothing like a record. Your narrative is for 1950's punch cards and mag tape files, not SQL.
Did you know that IDENTITY is a table property and not a column at all? It replaces the record numbering the first SQL Server had from UNIX. It courts the insertion attempts to one table on one machine, not even the successes!
We do not have generic “id” in RDBMS; the Law of Identity from Logic says identifiers must be for something in particular.
Records [sic] inserted on the basis of some rules and delete only 15 days backs records [sic] each time. <<
Please read Dr. Codd's 12 rules for RDBMS. “All relations are shown as scalar values in the columns of the rows in a table”. Where is the date for your deletion rule?
I got an error message that range on INTEGER is full and no new records [sic] can be inserted, please help me how to resolve the issue. <<
Since that non-RDBMS counter never resets on its own, it keeps going until it overflows. Look at the size of the INTEGER data type. You can reseed the counter with proprietary features. This is a bad idea.
Look up what a CREATE SEQUENCE statement does.
CREATE SEQUENCE Posting_Seq
START WITH 1
INCREMENT BY 1
You can have the SQL engine sequentially number rows within each day.
CREATE TABLE Something_Log
(posting_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
posting_seq INTEGER DEFAULT NEXT VALUE FOR Posting_Seq NOT NULL,
PRIMARY KEY (posting_date, posting_seq),
vague_generic_value INTEGER NOT NULL);
You will need to start each day with this code. Clean out the old data and re-set the counter.
DELETE FROM Something_Log
WHERE DATEDIFF (DAY, posting_date,
CAST(CURRENT_TIMESTAMP AS DATE)) > 15
ALTER SEQUENCE Invoice_Seq RESTART WITH 1;