Auto ID Primary Key specified as int and table data is deleted periodically--How to handle this?

• kathy.plamann

  Mr or Mrs. 500

  Points: 596

  More actions

  June 25, 2015 at 12:15 pm

  #305397

  Hi

  I have a table that has a primary key that is auto incremented by 1. This table's data is cleared out periodically and as data gets added the auto id primary key continues to increase in numeric value. Once the data is cleared from the table the auto id names could be used again(the eventId is not stored) Currently the eventID is at 26,581,399. I know the maximum int value is 2,147,483,647.

  How should I handle this? Does anyone just rebuild the table every time the data is cleared(problematically)?

  Thanks

  Kathy

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  June 25, 2015 at 12:19 pm

  #1808192

  kathy.plamann (6/25/2015)

  ---

  Hi

  I have a table that has a primary key that is auto incremented by 1. This table's data is cleared out periodically and as data gets added the auto id primary key continues to increase in numeric value. Once the data is cleared from the table the auto id names could be used again(the eventId is not stored) Currently the eventID is at 26,581,399. I know the maximum int value is 2,147,483,647.

  How should I handle this? Does anyone just rebuild the table every time the data is cleared(problematically)?

  Thanks

  Kathy

  The SEED value that the table is defined with can become the starting value again when the data is cleared by using TRUNCATE instead of DELETE at the time you want to remove all the records. Look at the entry in Books Online for TRUNCATE TABLE.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• kathy.plamann

  Mr or Mrs. 500

  Points: 596

  More actions

  June 25, 2015 at 1:03 pm

  #1808209

  Thanks Steve!

  I found out that the table's data is deleted based on date so all the data may not necessarily be deleted. I suggested that when select count(*) from this table is 0 that the table is truncated. If you have a better way to do this let me know.

  Thanks

  Kathy

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  June 25, 2015 at 1:18 pm

  #1808218

  kathy.plamann (6/25/2015)

  ---

  Thanks Steve!

  I found out that the table's data is deleted based on date so all the data may not necessarily be deleted. I suggested that when select count(*) from this table is 0 that the table is truncated. If you have a better way to do this let me know.

  Thanks

  Kathy

  Okay, if not all the records are deleted, then TRUNCATE won't help, as it deletes ALL records. You'll need DBCC CHECKIDENT, which is documented here:

  https://msdn.microsoft.com/en-us/library/ms176057.aspx

  Be sure you choose a value that is larger than the maximum remaining value for the IDENTITY column.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)