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!

Unless you know the application thouroughly well, its best to change the datatype to BIGINT. There could be some queries performing aggregates on these columns that will behave weirdly if you go to negative numbers..

Changing INT to BIGINT is a pain but is a one time pain. And dont even try to do an ALTER TABLE to change it to BIGINT. Your server might crash. Create a similar table T2, bcp out the table in smaller files (perhaps 1 mil records per file) depending on your storage/processor power, bcp in into T2, validate the data, script out all indexes, cosntraints from T1, drop T1 and add the indexes, constraints to T2. Also monitor log file during this bcp out/in. It will most likelye xplode. Test this entire proces 1-2 times in a test environment to get an estimate of time and document the steps..

And yes you need to change all stored procs to use BIGINT.

As for replication, not much to worry about it. You can just back up the DB, restore on the subscriber and re-implement replication.

Finally, if you have any views or any queries with UNIONS against tables with BIGINT you might end up with bad query plans

Example:

Select col1,col2 from TAble1 WHERE ....

UNION

Select col1,col2 FROM Table2 WHERE...

Suppose your col1 in Table1 is BIGINT, SQL Server will do an implicit conversion of col1 from Table2 to BIGINT BEFORE the join.. So you will see it simulates an ALTER TABLE alter column bigint for Table2 to convert col1 to bigint before doing the join. This can kill the queries. I have seen it happen. which is why I am takng the pain to type all this .. You'd think you are done after conversion but not yet...

Excellent post by ndinakar! 

If you're worried about running into INT's limitation, then BIGINT may not be enough. Why not use DECIMAL(38,0)?

You can use IDENTITY for a DECIMAL column (as long as scale is 0).

 An excellent post indeed.  Thanks a lot.  I'll be using that information during our migration process.

As for the DECIMAL(38,0), I am worried about the hit it will have on the performance of our stored procedures.  Would you know of its affect?

Thank you! 

Adriaan:

If you're worried about running into INT's limitation, then BIGINT may not be enough. Why not use DECIMAL(38,0)?

running out of BIGINT? I am not sure if any one of us will be alive until then