[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Wrong Data type

Posted on 2011-09-02
9
Medium Priority
?
221 Views
Last Modified: 2012-06-21
Hi

I have a program that works off an SQL Server, I just lern that one of the primary key need to go bigger. The key is data type Int, and max size of an int is 2147483647 however the program now needs to go higher, asin to about 10 time that.

What the easiest/best way to correct this?

Cheers

Matt
0
Comment
Question by:Matb396
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 2000 total points
ID: 36472232
0
 

Author Comment

by:Matb396
ID: 36472312
Am i right in thinking that ever SQL statement which contain this field has to be change?


 any other ideas?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36472324
No, not unless it relies on it being an int field.

Back your database up, test it and then if it fails for any reason, restore it and rethink.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36472345
The page I gave as an example performs sums on the data in the table which returns integers that are larger than the int range. You're table has an ID that is exceeding the range and I doubt you're doing sums on that ID number.

You may have to alter the stored procedure that generates the ID's as this will possibly have an integer variable which will need to be changed to a bigint.

Lee
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36472348
Any ta bles that reference that primary key will also have to be changed to bigints.
0
 

Author Comment

by:Matb396
ID: 36472406
ok thanks giving it ago now however the change of each table is taking its time...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36476535
It will take a very long time.  As a stop gap method until you get it resolved, consider resetting the IDENTITY Seed to -2147483648.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36485054
acperkins is suggesting that you reset the seed to generate Negative numbers...
the actual range of an integer is -2 billion to + 2 billion

I wasn't sure given the way his solution appears to flow on my screen if that was apparent to you.

if your applications can handle negative numbers then that should allow you much more time to
consider how you want to handle the extended range requirement, and all the changes that maybe required
in your systems....

however you may find that part of your systems fail because they aren't expecting negative ids...

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36485069
do you also need to step back and consider other design factors if the eventual database is going to be
 

10+ times bigger than originally designed for?

changing/choosing  the datatype is possibly a minor decision compared to the other scale factors
you now need to consider...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question