May 31, 2006 at 7:30 am
I'm using an Identity column for my PK field in a table. This table will be populated and cleared and populated again on a daily basis and the number of records each time could be up to 1 million.
Using an INT datatype according to my calcs, that would be good for about 5 yrs. Pretty safe bet that this program won't be used by then anyways. But what would be the safest thing to do? Should I use bigint just in case or should there be some process in place that alerts before the max is reached? Just wondering what 'common practice' is for this type of situation.
Thanks.
May 31, 2006 at 7:52 am
How are you "clearing" the table? If you use TRUNCATE, the identity will re-set to your original seed value.
May 31, 2006 at 8:03 am
oooohhh - I think I may have even known that at one time. Thank you. I'm sure I'm just deleting.
Now I think there can be some user rights issues when using Truncate in stored procs. I remember in one company we could not use it because the users did not have permissions to do that ... but I think for my current application, that is not an issue.
Thanks again.
May 31, 2006 at 8:22 am
You are correct. There are security considerations with TRUNCATE:
"The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause. For more information, see Using EXECUTE AS to Create Custom Permission Sets."
May 31, 2006 at 8:27 am
Will do and thank you very much!
June 1, 2006 at 10:24 am
You could setup a job that would reset (RESEED) the identity value using the DBCC CHECKIDENT command. This can only be done by the table owner, sysadmin, or members of the db_owner or db_ddladmin database roles but you could setup the job to be run by a sysadmin at night.
Run it on whatever schedule you want but when you run it, reseed the identify value back to 0 (zero) and the next time you do an insert, it will start with the next value from what you reseeded it at.
Use the command in the following manner:
DBCC CHECKIDENT ('dbo.mytable', RESEED, 0)
Good luck!
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
June 1, 2006 at 10:33 am
Thanks, I was wondering about the option of reseeding and I think that is my best solution at this point. Very good info all around!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply