1

I have a table that saves user hits. It has these columns:

ID , UserID , HitDate , Userlocation , UserUniqueID

Today I'm getting this error "Arithmetic overflow error". After I checked it out I find my Last hit ID was "2147483647" and also my Column type is

Int type , Primary Key with AutoNumber enabled

So the only solution is to Alter the column to bigint type (I guess). I've tried this code :

Alter HitTable alter column id bigint

but it returns this error :

Msg 5074 level 6 stat 1 line 1

The Object 'PK_HitTable' is dependent on colun 'id'

Alter Table Alter Column id failed because one or more objects access this column

So how can I change this huge table without losing any data? Will droping the current constraint cause data loss? Is below code safe and will it give me the result (also with autonumber)?

ALTER TABLE HitTable
DROP CONSTRAINT PK_HitTable
GO
Alter HitTable alter column id bigint
GO
ALTER TABLE HitTable
ADD CONSTRAINT HitTable PRIMARY KEY CLUSTERED (ID);

Or should I use this?

ALTER TABLE HitTable NOCHECK CONSTRAINT ALL  
GO
Alter HitTable alter column id bigint
GO
ALTER TABLE HitTable NOCHECK CONSTRAINT ALL  

Also I should mention this table is used heavily.

  • Restore the database into a dev environment and try your code to see what happens. – Max Vernon Oct 11 '14 at 6:33
  • sadly its a 500gig db and no disk space to make additional dev environment :( – Ahad Porkar Oct 11 '14 at 6:41
  • Have you used the negative numbers? If not you can reseed your IDENTITY are -1 with a step of -1 and buy some time while you implement and test a long term solution. Assumes you have no processing implicit on this column, such as using it as a surrogate for time sequencing. – Michael Green Oct 11 '14 at 10:13
  • 1
    You've tagged your question as SQL Server 2008 but defined the erroring column as "AutoNumber enabled", which is not SQL Server syntax. Can you correct one or the other, please. – Michael Green Oct 11 '14 at 10:14
  • @MichaelGreen It's obviously SQL-Server. They have GO all over the place ;) – ypercubeᵀᴹ Oct 11 '14 at 10:43
1

If you need to change the data type of a column, you'll have to change every data type referencing that column, too.

At the database level, you'll have to change any column referencing this column. Since you need to change a PK column, this will affect all other tables where this column is used as a foreign key reference. In all of those tables you'll need to change the data type of the related columns.

Furthermore, you'll have to perform a code review to catch each and every place where all the columns identified in the step above are referenced. If there are code sections, where a value from such a column is assigned to a variable, you'll need to change the data type of that variable. If the column is used in joins to other tables or in the WHERE section of a query, you might need to change the referenced columns as well.

As a side effect after the change you might see a performance drop caused by implicit conversions, if you didn't change all references.

Once the database changes are identified, you'll have to check the application if the columns and variables that had to be changed will have any side effect to the appl. For instance, if there are columns returned as bigint that used to be integer, the app should be able to handle it.

After all changes are identified, they should be implemented during a maintenance window to avoid data inconsistency due to dropped references.

Long story short: changing a data type needs to be well-prepared and tested. I strongly vote against an "on-the-fly" change!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.