March 5, 2008 at 10:35 am
Guys,
We have many tables in our environment [SQL Server 2000] with UID field of INTEGER data type, which are running out of numbers. That is, the largest table's UID field has used up 60% of the positive integer space and is growing at such a rate that within the next 6 months we will run out of numbers.
We are currently exploring many options to figure out how to resolve this issue best. I just wanted to hear some opinions of those who have gone through this and have suggestions. I am aware that there could be many things involved. For example, we could change from INT to BIGINT, but that would make stored procedures and quering those tables less efficient, and we'd need to change many stored procedure variable's data types as well. On top of it we have transactional replication set up for certain tables, so that could be an added headache.
Another thought I had was to just subtract MAX_INT from all the UIDs, thereby scaling everything down to use up the negative integer space, leaving the positive integer space totally free. That option carries its own problems, although probably better than the first one.
Please advise
Thank you very much!
March 5, 2008 at 11:00 am
I am opposed to messing with the values of UID's (assuming they're being used as Primary keys, etc...) The ripple effect of that kind of update on all foreign key locations that might be stored in, is just altogether too much work, and very risky IMO.
I would take a slightly different approach. Wait until you "get close" to +MAX_INT, then change the seeding process to start at -1, step -1, and let that ride until you get to -Max_INT. If you happen to be clustered on the UID, you'd want to change your clustered index about to be based on UID DESC so that the inserts continue to be "at the end". But - even that rebuild of the table would be less destructive than the rebuild of this table and all FK's relating to it.
If course - although somewhat "less efficient" theoretically - it might still be worth just upsizing that to just be BIGINT. I'm not sure that the performance penalty is that big at all as to avoid it altogether.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 5, 2008 at 11:12 am
Still, going the negative number route will likely only buy an addition year of time according to what was forecast by the OP. (18 months total... 6 months for +, 12 for - ).
I think you're just going to have to bite the bullet and make the change to a larger data type. Recommend you start now so when you get there, you don't have to grow a second set of hands to get it done.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply