Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IDENTITY value becoming too big Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 5:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 4:09 AM
Points: 125, Visits: 1,050
I have a table that gets a fresh set of data from our source each day. This mean I DELETE almost all the records each day and repopulate it with new data.
I do not TRUNCATE the table because according to business rules all the records can't be deleted, so my script is:
DELETE FROM Table1
WHERE .....

This deletes almost all the records each day.

Being a DELETE, this means the primary key column, which is an IDENTITY, does not get reseeded but rather this values just keeps getting bigger and bigger.

I am running into a scenario where the INT data type on this IDENTITY column is going to become too small.
My ID column is on 1,500,254,112 and the maximum for an INT is 2,147,483,647.

Now before I just go and change the data type to a bigint, I was hoping someone could give me a better solution to keep the ID value in check?

Any suggestions?

Post #1434214
Posted Friday, March 22, 2013 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 5,383, Visits: 9,953
Presumably your identity seed is 1? You could set it to -2,147,483,647. That would double your capacity - I don't know whether that would be enough?

John
Post #1434220
Posted Friday, March 22, 2013 6:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:08 AM
Points: 1,335, Visits: 658
You can use DBCC CHECKINDENT to reseed identity value.

DBCC  CHECKIDENT ('Production.Product' , RESEED, 1001)



Vishal Gajjar
http://SqlAndMe.com
Post #1434222
Posted Friday, March 22, 2013 6:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:31 AM
Points: 1,191, Visits: 9,882
Presumably, the rows you're not deleting each day are the highest ID values in the set? If so, simply reseeding wouldn't help. There are obviously convoluted solutions involving deleting, updating the IDs of the existing rows back to lower IDs, then reseeding, or moving rows out and truncating/reinserting but seems like overkill.

Bigint isn't so terrible, I'd just check whether you actually need an identity on the table at all first. Could another column serve as the PK instead (assuming you're using the identity as a PK)?
Post #1434224
Posted Friday, March 22, 2013 6:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:10 AM
Points: 1,976, Visits: 3,337
Hi,
You can use DBCC CheckIdent to reseed the column, assuming the values aren't used elsewhere in the database?

To avoid this in the future you could remove the identity property and insert with rownumber(), that way each insert will start at 1.
Post #1434227
Posted Friday, March 22, 2013 6:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 4:09 AM
Points: 125, Visits: 1,050
Hi,

Yes HowardW, you are correct - reseeding won't work because of the records and their relevant ID values that remain after the DELETE.
I was also thinking of the possibility of updating the ID values etc as suggested by you, but just did not know if all that work is really the best solution.

Thanks for your feedback! :)
Post #1434242
Posted Friday, March 22, 2013 9:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 5,383, Visits: 9,953
Do you have any other tables that reference the identity column on this table? For example, are there any foreign key constraints?

John
Post #1434319
Posted Friday, March 22, 2013 10:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:41 PM
Points: 31,080, Visits: 15,525
Answer John's question, and then go to BIGINT. I had to do this in a system doing scans of our nodes every hour. We ran out of INTs in about 8 months.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1434367
Posted Monday, March 25, 2013 12:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 4:09 AM
Points: 125, Visits: 1,050
Hi,

Yes I do have tables that reference this ID Primary key column.
So I guess I will go to BIGINT then?

Thanks again everyone
Post #1434754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse