SQLTeam.com | Weblogs | Forums

Identity reseed issue

sql2008

#1

hi team ,

need your suggestion in following scenario.

I have table where on daily basis some records interested and deleted and the table structure like below.

create table tbl_loandetail_agg
(
id int identity,
detail_value int
)

Records inserted on the baasis of some rules and delete only 15 days backs records each time.

i got a error message that range on int is full and no new records can be inserted, please help me how to resolve the issue.


#2

Assuming

SELECT MIN(id) FROM tbl_loandetail_agg

is a large number?? then re-seed back to 1 until it grows again.

Beware of it overlapping existing rows, if they are not deleted in time (at the least make sure you have a UNIQUE INDEX on [id])

If that is not possible just now then reseed to largest possible NEGATIVE number for an INT. Beware that if you use [id] on reports etc. that users will have to be careful to see, and use, the minus-sign on the ID number, and it may make the display field wider than reports etc. are used to.

There are other possible side effects - e.g. if the number is used in Web URLs it may get changed from Hyphen/Minus sign to a %99 encoded value, copy&paste to Word etc can change it to an EM dash, etc etc.

Reseed as follows:

DBCC CHECKIDENT ('dbo.YourTableName', RESEED, YourStartingValue)

#3

For a bandaid, you could change the id to BIGINT until you get the identity problem solved.

To add to what @Kristen said, watch for any links in other tables that might use the ID.


#4

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
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2000000
CYCLE;

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.

BEGIN
DELETE FROM Something_Log
WHERE DATEDIFF (DAY, posting_date,
CAST(CURRENT_TIMESTAMP AS DATE)) > 15
ALTER SEQUENCE Invoice_Seq RESTART WITH 1;
END;