Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2008-03-05 : 12:10:11
|
| 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 adviseThank you very much! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-05 : 12:18:14
|
| If your columns are identity, they cannot be updated without reloading the tables.Changing the columns to BIGINT is probably a better option.It’s too bad to have to do that, but that is what happens when the number of rows the table needs to contain is not considered when the database is designed. On the bright side, you have 6 months before you have a problem.CODO ERGO SUM |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2008-03-05 : 14:07:02
|
| Michael,Our columns are in fact IDENTITY. I was hoping there is something like IDENTITY UPDATE ON ... but I guess not.Yeah, we did not expect 3 years ago, when the design was originally made, that we'd be using so much of the integer space.Thanks for the suggestions - I'll explore further as well! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-05 : 23:07:10
|
| Take look at 'dbcc checkident'. |
|
|
|
|
|
|
|
|
Running out of the integer space ...