• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Wrong Data type

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
Matb396
Asked:
Matb396
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Lee SavidgeCommented:
0
 
Matb396Author Commented:
Am i right in thinking that ever SQL statement which contain this field has to be change?


 any other ideas?
0
 
Lee SavidgeCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Lee SavidgeCommented:
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
 
Lee SavidgeCommented:
Any ta bles that reference that primary key will also have to be changed to bigints.
0
 
Matb396Author Commented:
ok thanks giving it ago now however the change of each table is taking its time...
0
 
Anthony PerkinsCommented:
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
 
LowfatspreadCommented:
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
 
LowfatspreadCommented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now