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.